Final Scrub & Polish — Tier 4 Performance

Tiers 1–3 from the previous version of this guide are all applied — date-format fix, owner-avatar fallback colour, duplicate-modal variable naming, and Main.OnVisible confirmed intentional and removed. This version replaces the whole guide with just what's left: the three Tier 4 performance/maintenance items, each with full step-by-step instructions and complete replacement formulas, written against the new centralized colour system (colChoiceColors) so nothing here fights it.

Applied and off this guide: Planned Publish Date format regression, owner-initials fallback colour, varShowDuplicateModal/varShowDuplicateModal1 naming drift, Main.OnVisible (deleted — confirmed nothing else in the app writes varSelectedState/varTypeFilter/varFilterOwner except Main's own tab/chip/toggle controls, so there was nothing for it to reset).

Jump to: 1. Link-loading formula, one definition instead of six copies 2. Type-filter chip counts, computed once per tab/owner change instead of ~10× per render 3. Duplicate-entry Thursday picker, grouped once instead of 52 re-filters

1. Link-loading formula: one definition instead of six copies MAINTENANCE

The ~35-line "parse the file extension out of the SharePoint link URL, rebuild colLinks" formula is currently pasted verbatim in six places: two click handlers on Main (Selector.OnSelect, btnResultSelect.OnSelect) and four spots on ViewItem (both branches of OnVisible, frmLink_2.OnSuccess, and the delete-link confirm button btnModalAction1_1.OnSelect). Any future change — a new file type, a different URL shape — has to be made in all six by hand or they drift.

The fix: Power Fx User Defined Functions (entered in the Formulas property, same place as App.OnStart) now support parameters and behaviour functions like ClearCollect, confirmed current as of this pass. One function, six one-line call sites.

Test the function on its own first. Paste it into Formulas, save, and confirm Studio doesn't flag a syntax error before touching any of the six call sites. If it doesn't parse for any reason, none of your existing six copies are touched yet — you've lost nothing by checking first.

In the Tree view, select App. In the property dropdown (top-left of the formula bar), select Formulas.
Paste this in (adds to whatever's already there, doesn't replace it — if this is the first named formula/UDF in the app, it'll be the only thing in the box):

BuildLinksForSource(SourceID: Number): Boolean = {
    ClearCollect(
        colLinks,
        AddColumns(
            Filter(PolicyLinks, PolicyTrackerSource.Id = SourceID),
            FileExt,
            With(
                {FileParamStart: Find("file=", LinkUrl, 1)},
                With(
                    {
                        FileNamePart: If(
                            FileParamStart > 0,
                            With(
                                {AmpPos: Find("&", LinkUrl, FileParamStart + 5)},
                                Mid(LinkUrl, FileParamStart + 5, If(AmpPos > 0, AmpPos - (FileParamStart + 5), Len(LinkUrl) - (FileParamStart + 5) + 1))
                            ),
                            If(Find("?", LinkUrl, 1) > 0, Left(LinkUrl, Find("?", LinkUrl, 1) - 1), LinkUrl)
                        )
                    },
                    Switch(
                        true,
                        Right(FileNamePart, 5) = ".docx", "docx",
                        Right(FileNamePart, 4) = ".doc", "doc",
                        Right(FileNamePart, 5) = ".xlsx", "xlsx",
                        Right(FileNamePart, 4) = ".xls", "xls",
                        Right(FileNamePart, 5) = ".pptx", "pptx",
                        Right(FileNamePart, 4) = ".ppt", "ppt",
                        Right(FileNamePart, 4) = ".pdf", "pdf",
                        ""
                    )
                )
            )
        )
    );
    true
};

Behavior UDFs (any UDF whose body calls a side-effecting function like ClearCollect) must wrap their whole body in { } right after the : return type — a plain semicolon-chain without the braces gets rejected with "Please wrap the user-defined function body with curly braces to declare a behavior UDF." Non-behavior (pure calculation) UDFs don't need the braces, only ones with side effects.

Once that's saved with no error, replace each of the six call sites. Every one of them keeps everything else in its handler exactly as-is — only the ClearCollect(colLinks, AddColumns(...)) block is being swapped for one line.

Site 1 of 6 — `Main` → release row → `Selector.OnSelect`

Full replacement (only the ClearCollect block changes):

=Select(Parent);
Set(varSelectPolicy, "Filled");
Set(varParentSaved, true);
Set(varSelectedItem, ThisItem);
Set(varSourceScreen, "Main");
Set(varSelectedPerson, ThisItem.Owner);

// Load links immediately
Set(varSelectedRelease, ThisItem);
Set(varLoadingLinks, true);
BuildLinksForSource(ThisItem.ID);
Set(varLoadingLinks, false);

// Navigate to ViewItem
Navigate(ViewItem, ScreenTransition.Fade);

Site 2 of 6 — `Main` → search result row → `btnResultSelect.OnSelect`

=Set(varSelectedRelease, ThisItem);
Set(varSelectPolicy, "Filled");
Set(varParentSaved, true);
Set(varSelectedItem, ThisItem);
Set(varSourceScreen, "Main");
Set(varSelectedPerson, ThisItem.Owner);
Set(varLoadingLinks, true);
BuildLinksForSource(ThisItem.ID);
Set(varLoadingLinks, false);
Navigate(ViewItem, ScreenTransition.Fade);

If btnResultSelect.OnSelect has anything after the old ClearCollect block that isn't shown here (e.g. closing the search overlay), keep that tail exactly as it is — only the six-copy formula is being replaced app-wide, nothing else about this handler.

Site 3 of 6 — `ViewItem.OnVisible`, deep-link branch

This is inside the If(!varDeepLinkHandled && !IsBlank(Param("ID")), ...) block. Full replacement for that inner If(!IsBlank(varDeepLinkItem), ...) branch:

If(
    !IsBlank(varDeepLinkItem),
    Set(varSelectedRelease, varDeepLinkItem);
    Set(varSelectPolicy, "Filled");
    Set(varParentSaved, true);
    Set(varSelectedItem, varDeepLinkItem);
    Set(varSourceScreen, "Main");
    Set(varSelectedPerson, varDeepLinkItem.Owner);
    Refresh(PolicyLinks);
    BuildLinksForSource(varDeepLinkItem.ID);
    Set(varOwnerDefault, Office365Users.SearchUserV2({searchTerm: varDeepLinkItem.Owner.Email, isSearchTermRequired: true, top: 1}).value)
);

Site 4 of 6 — `ViewItem.OnVisible`, normal-selection branch

The second top-level If in OnVisible, right after the deep-link one closes:

If(
    !IsBlank(varSelectedRelease.ID),
    Refresh(PolicyLinks);
    BuildLinksForSource(varSelectedRelease.ID);
    Set(varLoadingLinks, false),
    Clear(colLinks);
    Set(varLoadingLinks, false)
);

Site 5 of 6 — `ViewItem` → `frmLink_2.OnSuccess`

=// Refresh PolicyLinks after successful save
Refresh(PolicyLinks);

// Reload colLinks so the gallery updates immediately
Set(varLoadingLinks, true);
BuildLinksForSource(varSelectedRelease.ID);
Set(varLoadingLinks, false);

// Close modal and reset variables
Set(varSelectedItem, varSelectedRelease);
Set(varShowLinkModal, false);
Set(varSelectedLink, Blank());

// Notify user
Notify("Link saved successfully!", NotificationType.Success);

Site 6 of 6 — `ViewItem` → delete-link confirm → `btnModalAction1_1.OnSelect`

=IfError(
    Remove(PolicyLinks, varSelectedLink),
    UpdateContext({locDisplayAttDeletePopUp: false});
    Notify("Delete failed. Please check your connection and try again.", NotificationType.Error),
    Set(varLoadingLinks, true);
    BuildLinksForSource(varSelectedRelease.ID);
    Set(varLoadingLinks, false);
    UpdateContext({locDisplayAttDeletePopUp: false});
    Set(varSelectedLink, Blank());
    Notify("Link deleted.", NotificationType.Success)
)

Once all six are pasted, do one real test per site type: click into an item from a Main card, click into one from search results, open a deep link (?ID= URL), add a new link, and delete a link. Confirm colLinks populates and file-type icons still show correctly in each case — the function is byte-for-byte the same logic that was in all six copies, so behaviour shouldn't change, only where the logic lives.

2. Type-filter chip counts: computed once per tab/owner change PERFORMANCE

Main's TypeFilterGallery (the ~10 document-type chips under the tab bar) currently has each chip's ChipLabel.Text independently run CountRows(Filter('Policy Proof Tracker', <state/owner condition> && 'Document Type'.Value = ThisItem.ChoiceValue)) — a fresh scan of the whole table, per chip, every time it re-renders. The state/owner part of that condition is identical across all ~10 chips; only the document-type equality changes per row.

The fix: build one collection, colTypeCounts, scoped to the current tab + owner-toggle state (rebuilt only when either of those changes — i.e. only where they're already Set(): the four tab buttons, the owner toggle, and once in OnStart for the initial "Current" tab). Each chip's count then becomes a cheap Filter over that already-small collection instead of the full table, and the gallery carries the count as a column instead of each label computing its own.

Step 1 — seed `colTypeCounts` in `App.OnStart`

Add this anywhere after colChoiceColors is built (order doesn't matter relative to it, just needs to run once at startup):

Set(
    colTypeCounts,
    Filter(
        'Policy Proof Tracker',
        'Planned Publish Date' >= Today() &&
        'Planned Publish Date' <= DateAdd(Today(), 28, "Days") &&
        (varFilterOwner = false || Owner.Email = User().Email)
    )
);

Step 2 — add the same rebuild to each tab button and the owner toggle

Four tab buttons (Button2 = Current, Button2_1 = Future, Button2_2 = UnapprovedHistoric, Button2_3 = History) plus MyItems.OnChange (the "My items only" toggle). Full replacement for each:

Control	Property	Full replacement

Button2.OnSelect (Current tab):

=Set(varSelectedState, "Current");
Set(
    colTypeCounts,
    Filter(
        'Policy Proof Tracker',
        'Planned Publish Date' >= Today() &&
        'Planned Publish Date' <= DateAdd(Today(), 28, "Days") &&
        (varFilterOwner = false || Owner.Email = User().Email)
    )
);
Reset(HeaderGallery)

Button2_1.OnSelect (Future tab):

=Set(varSelectedState, "Future");
Set(
    colTypeCounts,
    Filter(
        'Policy Proof Tracker',
        'Planned Publish Date' > DateAdd(Today(), 28, "Days") &&
        (varFilterOwner = false || Owner.Email = User().Email)
    )
);
Reset(HeaderGallery)

Button2_2.OnSelect (UnapprovedHistoric tab):

=Set(varSelectedState, "UnapprovedHistoric");
Set(
    colTypeCounts,
    Filter(
        'Policy Proof Tracker',
        'Planned Publish Date' < Today() &&
        Published = false &&
        (varFilterOwner = false || Owner.Email = User().Email)
    )
);
Reset(HeaderGallery)

Button2_3.OnSelect (History tab):

=Set(varSelectedState, "Historic");
Set(
    colTypeCounts,
    Filter(
        'Policy Proof Tracker',
        'Planned Publish Date' >= DateAdd(Today(), -varHistoricLimit, "Days") &&
        'Planned Publish Date' < Today() &&
        Published = true &&
        (varFilterOwner = false || Owner.Email = User().Email)
    )
);
Reset(HeaderGallery);

MyItems.OnChange (owner toggle) — uses Self.Value rather than varFilterOwner since this runs before the Set that updates it:

=Set(varFilterOwner, Self.Value);
Set(
    colTypeCounts,
    Filter(
        'Policy Proof Tracker',
        (
            (varSelectedState = "Current" && 'Planned Publish Date' >= Today() && 'Planned Publish Date' <= DateAdd(Today(), 28, "Days")) ||
            (varSelectedState = "Future" && 'Planned Publish Date' > DateAdd(Today(), 28, "Days")) ||
            (varSelectedState = "UnapprovedHistoric" && 'Planned Publish Date' < Today() && Published = false) ||
            (varSelectedState = "Historic" && 'Planned Publish Date' >= DateAdd(Today(), -varHistoricLimit, "Days") && 'Planned Publish Date' < Today() && Published = true)
        ) &&
        (Self.Value = false || Owner.Email = User().Email)
    )
)

Step 3 — carry the count on the gallery instead of per-label

TypeFilterGallery.Items, full replacement:

=AddColumns(
    colChoiceColors,
    TypeCount,
    CountRows(Filter(colTypeCounts, 'Document Type'.Value = ChoiceValue))
)

Step 4 — read the precomputed count instead of filtering live

ChipLabel.Text, full replacement (identical label logic, only the count source changes — the whole CountRows(Filter('Policy Proof Tracker', ...)) tail is gone):

=With(
    {
        FullLabel: Mid(ThisItem.ChoiceValue, Find(" ", ThisItem.ChoiceValue) + 1)
    },
    Switch(
        FullLabel,
        "Newsletter Item", "Newsletter",
        "Internal Document", "Internal",
        FullLabel
    )
) & " (" & ThisItem.TypeCount & ")"

Test: switch tabs, toggle "My items only", and check the chip counts still match what you'd get by manually counting the visible cards in each state. AllTypesBtn (the standalone "All types" pill, separate from this gallery) isn't touched — it only ever sets varTypeFilter, which was never part of this count in the first place.

3. Duplicate-entry Thursday picker: grouped once instead of 52 re-filters PERFORMANCE

ViewItem → btnDuplicate1.OnSelect builds the list of upcoming Thursdays by looping ForAll(Sequence(52), ...) and, for each one, running a fresh CountRows(Filter(colUpcomingEntries, Year(...) = Year(thurDate) && Month(...) = Month(thurDate) && Day(...) = Day(thurDate))) — 52 full scans of colUpcomingEntries (up to 365 days of data) to build one picker.

The fix: group colUpcomingEntries by date once with GroupBy, then each of the 52 iterations does one cheap LookUp against the small grouped collection instead of a full Filter+CountRows against the whole thing. This only fires when the duplicate flow is opened, so it's not urgent, but it's real waste every time it runs. Full replacement for btnDuplicate1.OnSelect:

=ClearCollect(
    colUpcomingEntries,
    AddColumns(
        Filter(
            'Policy Proof Tracker',
            'Planned Publish Date' >= Today() &&
            'Planned Publish Date' <= DateAdd(Today(), 365, "Days")
        ),
        EntryDateKey,
        Date(Year('Planned Publish Date'), Month('Planned Publish Date'), Day('Planned Publish Date'))
    )
);
Set(
    varDaysToNextThursday,
    If(Mod(5 - Weekday(Today()) + 7, 7) = 0, 7, Mod(5 - Weekday(Today()) + 7, 7))
);
ClearCollect(
    colUpcomingCounts,
    GroupBy(colUpcomingEntries, EntryDateKey, GroupedEntries)
);
Clear(colThursdays);
ForAll(
    Sequence(52),
    If(
        varSelectedRelease.'High Priority' ||
        varDaysToNextThursday + (Value - 1) * 7 >= 10,
        With(
            {thurDate: DateAdd(Today(), varDaysToNextThursday + (Value - 1) * 7, "Days")},
            With(
                {matchedGroup: LookUp(colUpcomingCounts, EntryDateKey = thurDate)},
                With(
                    {cnt: If(IsBlank(matchedGroup), 0, CountRows(matchedGroup.GroupedEntries))},
                    Collect(
                        colThursdays,
                        {
                            ThursdayDate: thurDate,
                            ThursdayLabel: Text(thurDate, "dddd d mmmm yyyy") &
                                If(
                                    cnt = 0, " - clear",
                                    cnt = 1, " - 1 entry already scheduled",
                                    " - " & Text(cnt, "0") & " entries already scheduled"
                                )
                        }
                    )
                )
            )
        )
    )
);
Set(varShowDuplicateModal1, true)

Test: open the duplicate flow on a real item and confirm the Thursday list still shows the right "already scheduled" counts against dates you know have existing entries, and "clear" against ones you know don't.

Final Scrub & Polish — Tier 4 Performance

1. Link-loading formula: one definition instead of six copies MAINTENANCE

Site 1 of 6 — Main → release row → Selector.OnSelect

Site 2 of 6 — Main → search result row → btnResultSelect.OnSelect

Site 3 of 6 — ViewItem.OnVisible, deep-link branch

Site 4 of 6 — ViewItem.OnVisible, normal-selection branch

Site 5 of 6 — ViewItem → frmLink_2.OnSuccess

Site 6 of 6 — ViewItem → delete-link confirm → btnModalAction1_1.OnSelect