The old Patch from Gallery with ForAll failures...

[TickboxPhil]

Hi PowerAppers, I've been through all the posts (I think) to try and simply update a SQL table from an already filtered Gallery - a pretty basic app requirement. Timesheets, in this example - the Gallery has some filtered Timesheets and "Notes" is a text field.

The not particularly intuitive, somewhat overwrought (low code!), yet generally accepted solution is to alias the gallery ID and Patch a Lookup:

ForAll(RenameColumns(galTime.AllItems, "TimeID", "GalTimeID"),
    Patch('[dbo].[tbl_Time]',
      LookUp('[dbo].[tbl_Time]', TimeID = GalTimeID),
      {Notes: "hah!"}
    )
);

Apparently, LookUp is delegable, Understand delegation in a canvas app - Power Apps | Microsoft Docs, so shouldn't cause a problem even over the maximum 2000 non-delegable limit. However, nothing is updated!

I've used {Notes: "hah!"} for the ChangeRecordTable so there's no red herrings referring to ThisRecord, gallery Text etc - it's a dirty hard-coded text update - we just need to get it to update anything from the IDs at this stage.

I tried it without the RenameColumns and even without the LookUp, but no joy. Similarly, with UpdateIf:

ForAll(RenameColumns(galTime.AllItems, "TimeID", "GalTimeID"),
    UpdateIf(
        '[dbo].[tbl_Time]',
        TimeID = GalTimeID,
        {Notes: "hah!"}
    )
);

Even tried a version of the official current documented way Patch function - Power Apps | Microsoft Docs using a groomed Collection:

ClearCollect(_colTime, RenameColumns(ShowColumns(galTime.AllItems,"TimeID","Notes"), "TimeID", "GalColTimeID"));

ForAll(_colTime,
  Patch('[dbo].[tbl_Time]',
    LookUp('[dbo].[tbl_Time]' As X, X.TimeID = _colTime[@GalColTimeID]),
    {Notes: "hah!"}
  )
);

Still no updates (or errors).

Maybe the team are updating the formula model behind the scenes and things have changed. Or maybe I'm just missing something?

[v-xiaochen-msft · Accepted solution]

Hi @TickboxPhil ,

Could you tell me:

What's Items property of 'galTime' gallery control? Is it '[dbo].[tbl_Time]'?

If so ,I don't think there is any problem with the grammar of your formula.

This looks like patching a "haha!" value for the 'Notes' column of each record.

Unfortunately, I did a test and did not encounter similar problem and  It worked.

So, perhaps this is a caching problem.

You could try to clear the cache, rebuild the connector and connection, refresh the data source and change the browser.

Besides, how many records do you have?

You could try the following formula:

ClearCollect(_colTime, RenameColumns(ShowColumns(galTime.AllItems,"TimeID","Notes"), "TimeID", "GalColTimeID"));

ForAll(_colTime,
LookUp('[dbo].[tbl_Time]' , TimeID = GalColTimeID));

Please check if it has returned records or is an empty table.

This will be used to troubleshoot whether it has not found records or cannot add data.

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

[TickboxPhil]

Thanks Wearsky @v-xiaochen-msft - am glad I'm on the right track then, really appreciate your confirmation and test. There are thousands of records, that's why I mentioned the Lookup being delegable (on paid Premium connectors like SQL Server) - it shouldn't be a problem (but def a concern, as you note).

And you've hit the nail on the head - this screen is using a SQL View (to bring in lookup field text), so '[dbo].[vw_Time_Plus]' on '[dbo].[tbl_Time]', so only indirectly on the actual Table. Have modified the code for another app with thousands of records but filtered gallery using direct Table in Gallery Items - and it works nicely, phew!

Can now appreciate exactly how PA is using data lineage here for its updates. It should really throw an error to stop all this head scratching, not just silently fail to update. Especially as the Patch syntax *is* on the direct Table, not the View, as I do understand you can't update a SQL View by nature. Actually, that's why I thought all this "Patch a Lookup" business was required in the first place. Here, the Gallery/SQL View is only used to lookup the IDs selected for updates - I can't see any reason the Gallery ID's aren't just treated as a list to the Patch with the values drawn out accordingly, making the fact of it being from a View or direct Table irrelevant. SQL Views are crucial in displaying connected data, especially in Galleries - we can't always have everything in a single table just for Power Apps!

So, is there are way to Patch a Table using IDs from Gallery Items fed from a SQL View? Is the data lineage so entangled in the Patch key matching (or any match in an UpdateIf) that we can't circumnavigate and create a filtered Collection and use that instead of the Gallery? Bit fiddly and not ideal (others have expressed frustration at not simply patching direct from gallery ID list without all this Renamed Lookup malarkey) but will try, any further thoughts extremely welcome before I hit the solution button!

[TickboxPhil]

Have tested this - trying to use a Collection for the IDs instead of Gallery based on SQL View. And it (almost) works! The "hah!" text is updated only on the filtered Gallery records in source.

So the Collection is disconnected data lineage wise (even though source is still the same Gallery and therefore Items SQL View), which makes sense. However, I remember why I discounted this idea at the time - the fly in the ointment is when I make the update field value a real one from the Gallery, or in this case the disconnected Collection, rather than the simple fixed text {Notes: "hah!"} as above.

ClearCollect(_colTime, RenameColumns(ShowColumns(galTime.AllItems,"TimeID","TestUpdate"), "TimeID", "GalColTimeID", "TestUpdate", "GalColTestUpdate"));
ForAll(_colTime,
  Patch('[dbo].[tbl_Time]',
    LookUp('[dbo].[tbl_Time]' As T, T.TimeID = _colTime[@GalColTimeID]),
    {TestUpdate: _colTime.GalColTestUpdate}
  )
);

I've just used a different field {TestUpdate: <solve this>} instead of {Notes: <solve this>} to save me having to restore real field each time. Problem is this:

Basically, the Collection Lookup works because a table is ok, but not in the actual update. How do we restrict the Collection ChangeRecordTable to be each single record iterated, not just the whole table?

[TickboxPhil]

Having got similar code working on another app using Gallery Items direct SQL Table instead of SQL View, coming back to this app (slightly different as using SQL View on Gallery Items) I tried without the collection again:

ForAll(RenameColumns(galTime.AllItems, "TimeID", "GalTimeID"),
    Patch('[dbo].[tbl_Time]',
      LookUp('[dbo].[tbl_Time]', TimeID = GalTimeID),
      {TestUpdate: txtTextUpdate.Text}
    )
);

Now it works! I noticed that final {TestUpdate: <solve this>} only resolved properly when I switched back to {TestUpdate: txtTestUpdate.Text} instead of {TestUpdate: [@txtTestUpdate].Text} which the IntelliSense had offered. Perhaps it was that or a caching issue, there's certainly some of that with Power Apps, even with the Delayed Load and Explicit Column Selection settings switched off. Whatever, this code does work, even though the Gallery Items are a SQL View. Hope it helps someone else!