Preload — Instant Due Next 4 Weeks Tab

The biggest, most invasive guide of this batch — touches Main.OnVisible, HeaderGallery.Items, ViewItem.OnVisible, and every write action that could change what's due in the next 4 weeks. Read it start to finish before pasting anything; the pieces depend on each other.

Scope, deliberately narrow: only the "Due Next 4 Weeks" tab (varSelectedState = "Current") gets preloaded — items and every one of their links, fetched once when Main loads. Future Releases, Issues, and Historic all keep working exactly as they do today, live-fetched on click, no change. If this works well, the same pattern can extend to Future/Issues later — not attempted here.

Jump to: 1. The cache-building function 2. Build the cache when Main loads 3. Read from the cache instead of the live table 4. Skip the network entirely when opening a preloaded item 5. Keep the cache honest after every write 6. The trade-off, stated plainly

1. The cache-building function

One user-defined function, same pattern as BuildLinksForSource and ShowToast — add to Formulas.txt (on App's Formulas property):

RefreshActiveCache(): Boolean = {
    ClearCollect(
        colActiveItems,
        Filter(
            'Policy Proof Tracker',
            'Planned Publish Date' >= Today() &&
            'Planned Publish Date' <= DateAdd(Today(), 28, "Days")
        )
    );
    ClearCollect(
        colActiveLinks,
        AddColumns(
            Filter(PolicyLinks, PolicyTrackerSource.Id in colActiveItems.ID),
            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",
                        ""
                    )
                )
            )
        )
    );
    Set(varActiveCacheReady, true);
    true
};

colActiveItems is every item due in the next 4 weeks, unfiltered by owner/type — those toggles apply on top of this locally (step 3), so switching them doesn't need a fresh network call either. colActiveLinks is every link belonging to any of those items, fetched in one combined query via PolicyTrackerSource.Id in colActiveItems.ID rather than one fetch per item. The FileExt column logic is copied verbatim from BuildLinksForSource, so colActiveLinks has exactly the same shape as the per-item colLinks your link cards already expect — nothing downstream needs to know whether its data came from the cache or a live fetch.

2. Build the cache when Main loads

Add one call to Main.OnVisible, after the existing Refresh('Policy Proof Tracker') and before Set(varMainLoading, false) — order matters, the cache needs the freshly-refreshed table, not whatever was cached from before:

=Set(varMainLoading, true);
Refresh('Policy Proof Tracker');
RefreshActiveCache();
Set(varMainLoading, false)

3. Read from the cache instead of the live table

In HeaderGallery.Items, only the "Current" branch changes — the other three (Future, UnapprovedHistoric, Historic) stay exactly as they are, still hitting 'Policy Proof Tracker' directly. Find the "Current" branch and replace just its inner Filter(...) call:

Old:

Filter(
    'Policy Proof Tracker',
    'Planned Publish Date' >= Today() &&
    'Planned Publish Date' <= DateAdd(Today(), 28, "Days") &&
    (varFilterOwner = false || Owner.Email = User().Email) &&
    (IsBlank(varTypeFilter) || varTypeFilter = "" || 'Document Type'.Value = varTypeFilter)
)

New:

Filter(
    colActiveItems,
    (varFilterOwner = false || Owner.Email = User().Email) &&
    (IsBlank(varTypeFilter) || varTypeFilter = "" || 'Document Type'.Value = varTypeFilter)
)

The date-range condition drops out entirely — it's already baked into what's in colActiveItems. Owner and type filters stay, now running against the local cache instead of the live table, so toggling either is instant too.

Then simplify the "Current" tab button itself. Find Button2's OnSelect (the "Due Next 4 Weeks" tab). Depending on whether item 4 from the fix-list is already applied, it may or may not currently wrap things in varMainLoading — check what's actually there before editing rather than assuming. Either way, two changes: remove the Refresh('Policy Proof Tracker'); line if present (switching to this tab no longer needs to fetch anything, the cache is already sitting there from when Main loaded), and swap the colTypeCounts filter's source the same way as step 3 above:

Set(
    colTypeCounts,
    Filter(
        colActiveItems,
        (varFilterOwner = false || Owner.Email = User().Email)
    )
);

Everything else in that formula (Set(varSelectedState, "Current"), Reset(HeaderGallery), and the varMainLoading wrapper if it's there) stays as-is — only the Refresh line and the colTypeCounts filter's source change. Leave Button2_1/Button2_2/Button2_3 (Future/Issues/Historic) completely untouched — they still need their own live refresh, this scope stops at "Current" only.

4. Skip the network entirely when opening a preloaded item

This is the piece that makes clicking into an item from this tab actually instant, not just the list rendering. Builds directly on item 10's varLinksSourceID preload check — find that same final block in ViewItem.OnVisible and replace it:

Old (from item 10):

If(
    !IsBlank(varSelectedRelease.ID),
    If(
        varLinksSourceID <> varSelectedRelease.ID,
        BuildLinksForSource(varSelectedRelease.ID)
    );
    Set(varLoadingLinks, false),
    Clear(colLinks);
    Set(varLoadingLinks, false)
);

New:

If(
    !IsBlank(varSelectedRelease.ID),
    If(
        !IsBlank(LookUp(colActiveItems, ID = varSelectedRelease.ID)),
        ClearCollect(colLinks, Filter(colActiveLinks, PolicyTrackerSource.Id = varSelectedRelease.ID));
        Set(varLinksSourceID, varSelectedRelease.ID),
        If(
            varLinksSourceID <> varSelectedRelease.ID,
            BuildLinksForSource(varSelectedRelease.ID)
        )
    );
    Set(varLoadingLinks, false),
    Clear(colLinks);
    Set(varLoadingLinks, false)
);

If the item being opened is one of the ones already in colActiveItems, its links come straight out of colActiveLinks — a local filter, genuinely zero network calls. Anything not in the cache (an item from Future/Issues/Historic) falls back to exactly the same behaviour as before, untouched.

Test after steps 1–4: open Main fresh, click into 3–4 different items on the Due Next 4 Weeks tab in a row. Fields and links should both appear with zero visible loading gap, not just fields. Then click into a Future or Historic item and confirm it still behaves exactly as before (brief real load, unaffected by any of this).

5. Keep the cache honest after every write

This turned out simpler than it first looked. Main.OnVisible already rebuilds the cache unconditionally, every single time Main is visited (step 2) — and every write path that matters here (Save & Close, plain Close, Delete) ends in Navigate(Main, ...), which re-runs Main.OnVisible every time. So editing an item, adding a link, and hitting Save & Close is already covered for free: the moment you land back on Main, the whole cache rebuilds from scratch, no exceptions, nothing extra to add. Same for plain Save followed by Close — whenever you actually leave ViewItem, the cache catches up.

The only genuine gap is the two places that change data without navigating away and back — they never trigger Main.OnVisible again, so nothing else rebuilds the cache for them. Both buttons are on Main. In each, the only change is one new line, RefreshActiveCache();, added right after Refresh('Policy Proof Tracker'); and before the closing Notify(...) — everything else in both formulas is unchanged from what's already live.

BossPublishBtn.OnSelect (Main, quick-publish button) — full replacement:

=Set(
    varQuickPublishDate,
    Coalesce(ThisItem.'Planned Publish Date', Today())
);
Patch(
    'Policy Proof Tracker',
    LookUp('Policy Proof Tracker', ID = ThisItem.ID),
    {
        Published: true,
        'Published Date': varQuickPublishDate
    }
);
Refresh('Policy Proof Tracker');
RefreshActiveCache();
Notify(
    "Marked as published - " & Text(varQuickPublishDate, "d mmm yyyy"),
    NotificationType.Success
)

PublishAllBtn.OnSelect (Main, bulk-publish button) — full replacement:

=// PublishAllBtn.OnSelect
Set(varPublishGroupDate, ThisItem.PlannedDate);
Set(varPublishGroupCount, CountRows(Filter(ThisItem.GroupedItems, 'External consultation required' = false)));
ForAll(
    Filter(ThisItem.GroupedItems, 'External consultation required' = false) As PubItem,
    Patch(
        'Policy Proof Tracker',
        LookUp('Policy Proof Tracker', ID = PubItem.ID),
        {
            Published: true,
            'Published Date': varPublishGroupDate
        }
    )
);
Refresh('Policy Proof Tracker');
RefreshActiveCache();
Notify(
    "Published all " & varPublishGroupCount & " items for " & Text(varPublishGroupDate, "d mmm yyyy"),
    NotificationType.Success
)

If your current live version of either button differs from what's shown above (e.g. you've made other edits since), don't blind-paste — just add the single RefreshActiveCache(); line in the same spot (right after Refresh('Policy Proof Tracker');) in whatever you actually have.

Test the scenario that actually matters: open an item from the Due Next 4 Weeks tab (instant), edit some text, add a link, hit Save & Close. Back on Main, then click the same item again — it should still open instantly, and both the text change and the new link should be there, not the old version. Separately, test the quick-publish button on Main directly and confirm the card updates without needing to leave and return.

6. The trade-off, stated plainly

This does not make Main aware of changes made by someone else, in a different session, while you're sitting on the Due Next 4 Weeks tab — the cache only refreshes when Main loads or when you make a change through one of the write paths above. If a colleague publishes something while you're looking at your own screen, you won't see it until you switch tabs and back, or reopen the app. Same category of trade-off as removing the redundant Refresh(PolicyLinks) earlier — instant every time, at the cost of a rare cross-session staleness window. If that's not acceptable for how this app actually gets used day to day, say so before rolling this out — it's a real, conscious trade, not an oversight.