Cleanup Pass — Delegation Fix, Formula Dedup, Real Data-Gated Loading Screen

This replaces the old "Loading Screen — Branded App-Start Splash" guide entirely. That guide's core premise — "there's nothing real to gate on, OnStart already finished, just do a cosmetic pause" — stopped being true the moment RefreshActiveCache() and colActiveItems/colActiveLinks were built. There's now something genuinely worth waiting for, so the splash is redesigned to actually wait for it instead of faking a delay.

Three items, done in priority order — a real correctness/performance bug first, then the feature this whole session has been building toward, then a pure tidy-up with zero behaviour change. Each is independent; do them in any order you like, but this is the order that matters most first.

Jump to: 1. Fix: colThursdayOptions — 104 non-delegable filters Do first 2. Loading Screen — actually gated on real data The feature 3. Dedup: shared GetFileExt formula Tidy-up

1. Fix colThursdayOptions — same delegation bug as NewsletterPack, worse Do first

In ViewItem.OnVisible, the date-picker's per-Thursday item count is built with the exact same non-delegable Year/Month/Day pattern that broke NewsletterPack, except run 104 times, once per upcoming Thursday for the next two years, every single time ViewItem loads. Below is the entire ViewItem.OnVisible property, old and new in full — not the block in isolation — so there's no hunting for where it sits among the resets and the deep-link handling around it.

Old — full current ViewItem.OnVisible:

Set(varLoadingLinks, varLinksSourceID <> varSelectedRelease.ID || IsBlank(varSelectedRelease.ID));
Set(varSaving, false);
Set(varShowLinkModal, false);
Set(varSelectedLink, Blank());
Set(varErrTitle, false);
Set(varErrOwner, false);
Set(varErrDocType, false);
Set(varErrPublishDate, false);
Set(
    varOwnerDefault,
    If(
        IsBlank(varSelectedRelease) || IsBlank(varSelectedRelease.Owner.Email),
        Table({DisplayName: User().FullName, Mail: User().Email}),
        Table({DisplayName: varSelectedRelease.Owner.DisplayName, Mail: varSelectedRelease.Owner.Email})
    )
);
Set(
    varPlannedPublishDate,
    If(
        IsBlank(varSelectedRelease) || IsBlank(varSelectedRelease.'Planned Publish Date'),
        DateAdd(Today(), Mod(5 - Weekday(Today()) + 7, 7), "Days"),
        varSelectedRelease.'Planned Publish Date'
    )
);
If(
    !varDeepLinkHandled && !IsBlank(Param("ID")),
    Set(varDeepLinkHandled, true);
    Set(varItemID, Param("ID"));
    Set(varLoading, true);
    Refresh('Policy Proof Tracker');
    Set(varDeepLinkItem, LookUp('Policy Proof Tracker', ID = Value(varItemID)));
    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, Table({DisplayName: varDeepLinkItem.Owner.DisplayName, Mail: varDeepLinkItem.Owner.Email}));
        Set(varPlannedPublishDate, varDeepLinkItem.'Planned Publish Date'),
        Notify("This item could not be found. It may have been deleted or the link may be incorrect.", NotificationType.Error);
        Set(varDeepLinkNotFound, true)
    );
    Set(varLoading, false)
);
Reset(txtTitle1);
Reset(drpDocType1);
Reset(dpkPublishDate1);
Reset(ckbHighPriority1);
Reset(txtReasonHighPri1);
Reset(ckbExtConsult1);
Reset(txtExtConsultDetails1);
Reset(ckbTopItem1);
Reset(txtNewsletterTitle1);
Reset(txtAudience1);
Reset(txtSummary1);
Reset(txtRemarks1);
Reset(ckbPillarLead1);
Reset(radApproval1);
Reset(ckbPublished1);
Reset(dpkPublishedDate1);
Reset(cmb_Owner1);
Set(varShowThursdayPicker, false);
ClearCollect(
    colThursdayOptions,
    ForAll(
        Sequence(104),
        With(
            {_thu: DateAdd(Today(), Mod(5 - Weekday(Today()) + 7, 7) + (Value - 1) * 7, "Days")},
            {
                ThursdayDate: _thu,
                ItemCount: CountRows(
                    Filter(
                        'Policy Proof Tracker',
                        Year('Planned Publish Date') = Year(_thu) &&
                        Month('Planned Publish Date') = Month(_thu) &&
                        Day('Planned Publish Date') = Day(_thu)
                    )
                )
            }
        )
    )
);
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)
);

New — full replacement ViewItem.OnVisible, everything identical except the colThursdayOptions section (now two ClearCollects instead of one):

Set(varLoadingLinks, varLinksSourceID <> varSelectedRelease.ID || IsBlank(varSelectedRelease.ID));
Set(varSaving, false);
Set(varShowLinkModal, false);
Set(varSelectedLink, Blank());
Set(varErrTitle, false);
Set(varErrOwner, false);
Set(varErrDocType, false);
Set(varErrPublishDate, false);
Set(
    varOwnerDefault,
    If(
        IsBlank(varSelectedRelease) || IsBlank(varSelectedRelease.Owner.Email),
        Table({DisplayName: User().FullName, Mail: User().Email}),
        Table({DisplayName: varSelectedRelease.Owner.DisplayName, Mail: varSelectedRelease.Owner.Email})
    )
);
Set(
    varPlannedPublishDate,
    If(
        IsBlank(varSelectedRelease) || IsBlank(varSelectedRelease.'Planned Publish Date'),
        DateAdd(Today(), Mod(5 - Weekday(Today()) + 7, 7), "Days"),
        varSelectedRelease.'Planned Publish Date'
    )
);
If(
    !varDeepLinkHandled && !IsBlank(Param("ID")),
    Set(varDeepLinkHandled, true);
    Set(varItemID, Param("ID"));
    Set(varLoading, true);
    Refresh('Policy Proof Tracker');
    Set(varDeepLinkItem, LookUp('Policy Proof Tracker', ID = Value(varItemID)));
    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, Table({DisplayName: varDeepLinkItem.Owner.DisplayName, Mail: varDeepLinkItem.Owner.Email}));
        Set(varPlannedPublishDate, varDeepLinkItem.'Planned Publish Date'),
        Notify("This item could not be found. It may have been deleted or the link may be incorrect.", NotificationType.Error);
        Set(varDeepLinkNotFound, true)
    );
    Set(varLoading, false)
);
Reset(txtTitle1);
Reset(drpDocType1);
Reset(dpkPublishDate1);
Reset(ckbHighPriority1);
Reset(txtReasonHighPri1);
Reset(ckbExtConsult1);
Reset(txtExtConsultDetails1);
Reset(ckbTopItem1);
Reset(txtNewsletterTitle1);
Reset(txtAudience1);
Reset(txtSummary1);
Reset(txtRemarks1);
Reset(ckbPillarLead1);
Reset(radApproval1);
Reset(ckbPublished1);
Reset(dpkPublishedDate1);
Reset(cmb_Owner1);
Set(varShowThursdayPicker, false);
ClearCollect(
    colThursdayItemCounts,
    AddColumns(
        GroupBy(
            Filter(
                'Policy Proof Tracker',
                'Planned Publish Date' >= Today() &&
                'Planned Publish Date' <= DateAdd(Today(), 104 * 7, "Days")
            ),
            'Planned Publish Date',
            GroupedItems
        ),
        DayItemCount,
        CountRows(GroupedItems)
    )
);
ClearCollect(
    colThursdayOptions,
    ForAll(
        Sequence(104),
        With(
            {_thu: DateAdd(Today(), Mod(5 - Weekday(Today()) + 7, 7) + (Value - 1) * 7, "Days")},
            {
                ThursdayDate: _thu,
                ItemCount: Coalesce(LookUp(colThursdayItemCounts, 'Planned Publish Date' = _thu, DayItemCount), 0)
            }
        )
    )
);
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)
);

colThursdayItemCounts is a new, throwaway local collection — one delegable Filter, one GroupBy, done. colThursdayOptions itself is structurally identical to before, just reading counts from that local collection (via LookUp, which isn't delegation-limited once the data's already local) instead of hitting the live table 104 times. Coalesce(..., 0) keeps the same "no items that week" behaviour as before, since LookUp returns blank when nothing matches. Everything else in the property — the resets, the deep-link handling, the final colLinks block — is byte-for-byte what you already have; paste the whole thing and it changes nothing else.

Test: open any item, open the release-date picker, scroll through several months. Counts should match what's actually on the calendar, and it should feel at least as fast as before (fewer, cheaper operations, not more).

2. Loading Screen — wait for the real thing, not a timer The feature

Correction from the previous version of this guide: it assumed a screen called StartScreen already existed. It doesn't — this app has never had one. Routing between ViewItem and Main currently happens entirely inside App.StartScreen (the App object's own property, not a screen), with this exact formula, confirmed live in your app:

If(
    !IsBlank(Param("ID")),
    ViewItem,
    Main
)

This resolves once, at launch, straight to a real screen — it can't itself show a splash or wait on anything, same as App.OnStart can't. So a genuine branded, data-gated splash needs an actual new screen created from scratch, with App.StartScreen changed to always land there first instead of choosing directly between ViewItem/Main. Steps below now include that missing setup, in the right order.

Component: cmpLoadingScreen from powerappsui.com, archived at yaml/components/LoadingScreen/.

The idea: the app now has one real, variable-length async step worth tracking — RefreshActiveCache() building colActiveItems/colActiveLinks — and it already sets varActiveCacheReady to true when done (that variable exists in Formulas.txt today but nothing reads it yet). The new splash screen genuinely waits on that flag instead of a fixed pause. Deep links straight to ViewItem skip the wait entirely — that screen already falls back to a live per-item fetch if the cache isn't ready, so there's nothing worth making a shared link wait on. ViewItem.OnVisible's own existing Param("ID") handling doesn't need to change at all — it's already guarded by varDeepLinkHandled, so it runs correctly and exactly once regardless of whether it's reached via the old direct routing or the new splash screen.

2a. Create the screen

Insert → New screen → Blank, name it StartScreen (that name is free — it was never taken).

2b. Verify the component pastes cleanly

Insert → Custom → New Component, paste the full contents of yaml/components/LoadingScreen/LoadingScreen.yaml, name it cmpLoadingScreen.

Test: component imports clean, previewing it directly shows the logo mark, wordmark, and progress dots without errors.

2c. StartScreen.OnVisible — kick off the cache build, don't navigate yet

This is a brand new screen, so this is the entire property from scratch, not a replacement of anything:

Set(
    varStartDestination,
    If(!IsBlank(Param("ID")), "ViewItem", "Main")
);
Set(varSplashMinTimeElapsed, false);
Set(varActiveCacheReady, false);
If(
    varStartDestination = "Main",
    Refresh('Policy Proof Tracker');
    RefreshActiveCache()
)

Deep-link case (varStartDestination = "ViewItem") deliberately skips the cache build — a shared link shouldn't wait on a 4-week preload it doesn't need. The Main case fires the same RefreshActiveCache() used everywhere else in the app, so this is the exact same function, just called one screen earlier than before.

2d. Place the component, wire OnReady

Drag one instance of cmpLoadingScreen onto the new StartScreen and set:

Height: =App.Height
Width: =App.Width
X: =0
Y: =0
Theme: ="Light"
AccentColor: =RGBA(11, 74, 54, 1)
LogoLetter: ="P"
AppName: ="Policy"
AppNameAccent: =" Tracker"
AppSubtitle: ="Cadet Forces Document Tracking"
CompanyName: ="Policy Tracker"
AppVersion: ="V2"
LoadingSteps: =Table(
    {msg: "Loading colour palette…"},
    {msg: "Loading document list…"},
    {msg: "Checking staff access…"}
)
CurrentStep: =If(varStartDestination = "ViewItem" || varActiveCacheReady, 3, 2)
MinDisplayTime: =400
ErrorMessage: =""
OnReady: =Set(varSplashMinTimeElapsed, true)

CurrentStep genuinely reflects reality — it sits at step 2 ("Loading document list…") for as long as the real fetch is still running, and only reaches step 3 once varActiveCacheReady actually flips (or immediately for the deep-link case, which never claimed to load that list). OnReady doesn't navigate directly — it only marks that the component's own minimum branding time has passed. Navigation itself happens in the timer below, which is the piece that actually enforces "wait for everything, then disappear."

2e. Add the gate — a small polling Timer on StartScreen

Power Fx has no native "await a variable" — the standard way to do this in canvas apps is a repeating Timer that checks the condition and navigates once it's true. Add this new control to StartScreen:

- tmrSplashGate:
    Control: Timer@2.1.0
    Properties:
      AutoStart: =true
      Duration: =150
      OnTimerEnd: |-
        =If(
            varSplashMinTimeElapsed && (varStartDestination = "ViewItem" || varActiveCacheReady),
            If(
                varStartDestination = "ViewItem",
                Navigate(ViewItem, ScreenTransition.None),
                Navigate(Main, ScreenTransition.None)
            )
        )
      Repeat: =true
      Visible: =false

Correction: the first draft of this had Navigate(varStartDestination, ScreenTransition.None) — that's wrong and won't run. Navigate() needs an actual screen reference as its first argument (Main, ViewItem), not a text variable holding the screen's name as a string — Power Fx has no way to resolve "Main" into the real Main screen object at runtime. The version above branches explicitly instead, calling Navigate() with the real screen name in each branch, and is the one to actually use.

Every 150ms it checks: has the splash's own minimum branding time passed, and is there actually somewhere safe to land (either the deep-link case, which never needed the cache, or the cache genuinely finished)? Only when both are true does it navigate.

2f. The step that actually makes any of this run — repoint App.StartScreen

Without this step, everything above sits on a screen the app never visits. This is the fix for exactly the gap you caught — nothing built in 2a–2e does anything until App.StartScreen is told to go there first.

Click App at the top of the Tree view (above all screens), find StartScreen in its property list, select all, replace it entirely:

Old (confirmed live in your app):

If(
    !IsBlank(Param("ID")),
    ViewItem,
    Main
)

New:

StartScreen

The If(!IsBlank(Param("ID")), ...) branching logic isn't lost — it just moves one step later, into the new StartScreen.OnVisible from 2c, which does the exact same check and then either waits (Main-bound) or goes straight through (ViewItem-bound).

Test 1 (cold start, normal): reload the app fresh (not just navigating within it), confirm the splash shows, step 2 briefly, step 3, then lands on Main with the "Due Next 4 Weeks" tab already populated — not a skeleton loader after the splash disappears, actually populated.
Test 2 (deep link): open the app with ?ID= in the URL and confirm it lands on ViewItem quickly, without waiting on the 4-week cache.
Test 3 (slow network, optional): if you can throttle your connection, confirm the splash now visibly stays on step 2 longer rather than flashing through regardless — that's the whole point of this change, proof it's a real gate and not cosmetic.

3. Dedup: shared GetFileExt formula Tidy-up, zero behaviour change

BuildLinksForSource and RefreshActiveCache (both in Formulas.txt) duplicate the entire "figure out the file extension from LinkUrl" block verbatim — only the Filter(...) feeding into it differs. Pulling it out means one place to touch if you ever add a file type, nothing else changes.

Add this new formula to Formulas.txt:

GetFileExt(LinkUrl: Text): Text = 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",
            ""
        )
    )
)

Then replace both UDFs with the shortened versions — full replacements, everything else in each is unchanged:

BuildLinksForSource(SourceID: Number): Boolean = {
    ClearCollect(
        colLinks,
        AddColumns(
            Filter(PolicyLinks, PolicyTrackerSource.Id = SourceID),
            FileExt,
            GetFileExt(LinkUrl)
        )
    );
    Set(varLinksSourceID, SourceID);
    true
};

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,
            GetFileExt(LinkUrl)
        )
    );
    Set(varActiveCacheReady, true);
    true
};

Test: open any item's link list (from cache and from a live fallback item), confirm file icons/extensions still show correctly — behaviour should be identical to before, this is pure refactor.