Unanswered

ForAll Patch help

(0) Share

Report

Posted on by kriggo15

526

I need some help creating a ForAll Patch. In a gallery, I'm allowing users to edit previously entered records (columns details) and also add new records. Any changes or additions get written to a collection. I'm trying to add a ForAll function on the OnSuccess property of the top form but I can't figure out the logic. If the record in the collection exists in the source, then update it, otherwise it is a new record and add it as such in the source. ForAll ss.png

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (0)

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report
Collect(yourDataSource, ForAll(DropColumns(Filter(yourCollection, IsBlank(ID)), "ID") As _item, _item ) ); Patch(yourDataSource, Filter(yourCollection, !IsBlank(ID)) )

@kriggo15

This is going to be highly dependent on the Items property of the bottom gallery, but in general your formula on the OnSuccess of the upper form would be:

This assumes you have all the column names properly done in the collection to match the datasource.

I hope this is helpful for you.

Was this reply helpful? Yes No
kriggo15 526 on at

Like (0)

Report

Thanks @RandyHayes. The Items property of my gallery is my collection I'm storing the edits in. Also, my source is SQL and I have over 2K rows. Am I going to run into a delegation issue here?

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report

@kriggo15

No, you are creating and patching rows, delegation will not be an issue.

The key to everything is based on the column that is the primary key of your table in SQL. So, in the above I used ID to designate the primary key, but just change that to whatever your primary key is.

The only limitations you might incur will be with API connection rate. If you are going to "batch" these rows and create/patch on form submit, just make sure your API calls per minute rate is not exceeded.

If that becomes an issue, then consider getting rid of the concept of working from a collection and just have your gallery be directly connected to the datasource, your saves be an update of the current row and your "add new row" simply create a new record in your table. It is much less likely that a user working on the form/rows will exceed the limit while they work on row by row.

I mention this specifically because your screen shows a save icon on each row, so the assumption is that the person has to "save" the row item each time, and thus updating and creating in batch is not really relevant.

Was this reply helpful? Yes No
kriggo15 526 on at

Like (0)

Report
I'm getting an error with each statement:
The specified column 'Value' does not exist. The column with the most similar name is 'Name'. - I assume this is because my collection doesn't directly reflect the datasource. I'm not showing all of the columns and I added the Value column.
The Patch function has some invalid arguments. Invalid argument type (Table). Expecting a record value instead.

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report
@kriggo15

Column matching is highly important. Not only must you provide the proper names of the columns in your collection to match the datasource, but also you must make sure the values are as expected.

This is one of the complexities of working with collections instead of working direct with the datasource.

So you will need to either adjust your column names properly and also drop any columns from your collection that you added in order for it to work properly.

Ex:

Collect(yourDataSource, ForAll( DropColumns(Filter(yourCollection, IsBlank(ID)), "ID", "addedColumn1", "addedColumn2", etc... ) As _item, _item ) ); Patch(yourDataSource, DropColumns( Filter(yourCollection, !IsBlank(ID)), "addedColumn1", "addedColumn2", etc... ) )

If you have different names on columns than the datasource, then you need to rename them.

Ex.

Collect(yourDataSource, ForAll( RenameColumns( DropColumns(Filter(yourCollection, IsBlank(ID)), "ID", "addedColumn1", "addedColumn2", etc... ), "wrongNameColumn1", "ProperNameColumn1", "wrongNameColumn2", "ProperNameColumn2" ) As _item, _item ) ); Patch(yourDataSource, RenameColumns( DropColumns( Filter(yourCollection, !IsBlank(ID)), "addedColumn1", "addedColumn2", etc... ), "wrongNameColumn1", "ProperNameColumn1", "wrongNameColumn2", "ProperNameColumn2" ) )

If you don't go that route, then you will need to recreate your entire record schema in the ForAll

Ex:

Collect(yourDataSource, ForAll(Filter(yourCollection, IsBlank(ID)) As _item, {column1: _item.CollectionColumnName1, column2: _item.CollectionColumnName2, ..etc.. } ) ); Patch(yourDataSource, ForAll(Filter(yourCollection, !IsBlank(ID)), {primaryKeyColumn: _item.OrimaryKeyColumn, column1: _item.CollectionColumnName1, column2: _item.CollectionColumnName2, ..etc.. } ) )

Depending on the amount of columns, it's a lot of work at times.

This is why your collections should be made from the datasource schema directly all the time, or skip the collections and work direct with the datasource.

Was this reply helpful? Yes No