You’re offline. This is a read only version of the page.
Hi all,
I have a canevas app, where a collection is modified by controls in a screen. The items of the collection come from a Dataverse table.
It is named Etudes_Co and has a column to store if :
- the record has been newly created (not in the table yet),
- the record has modified (table is to update)
- the record is unchanged from the table
I would like to update the dataverse table which is named tbEtudes with values from the collection.
I don't have any problem to patch the records which are newly created.
The issue is when i try to update records which already exist. Then the modifications are applied to the last created record in the table, wether modified or not.
Does someone have an idea of where i have a fail in the code ?
Share
ReportForAll(Etudes_Co;
If(ThisRecord.modif="modif";
Notify("traitement " & ThisRecord.codeAffaire);;
Patch(tbEtudes;LookUp(tbEtudes;tbEtudes[@ythabo_codeaffaire]=ThisRecord[@ythabo_codeaffaire]);
{
ythabo_nom:ThisRecord[@ythabo_nom];
ythabo_client:ThisRecord[@client];
ythabo_statut:ThisRecord[@statut];
ythabo_budgetglobal:ThisRecord[@budgetGlobal];
}
));;
If(ThisRecord.modif="créa";
Notify("Enreg" & ThisRecord.codeAffaire);;
Patch(tbEtudes;Defaults(tbEtudes);
{ ythabo_codeaffaire:ThisRecord[@ythabo_codeaffaire];
ythabo_nom:ThisRecord[@ythabo_nom];
ythabo_client:ThisRecord[@ythabo_client];
ythabo_statut:ThisRecord[@ythabo_statut];
ythabo_budgetglobal:ThisRecord[@ythabo_budgetglobal];
}
));;
ThisRecord.modif="non"
);;
Yes, for DataVerse that is very often confused as they label the "Primary Name Column", but yet the primary key column is only identifiable by looking for the data type of unique identifier
If you are still working then with your original formula, you are fighting the Patch function and the ForAll which is going to lead to performance problems.
The problem you were having with the formula I provided is that you most likely were not working with the primary key. This is often confused with the "Primary Name Column". That is not the primary key! The primary key column is the column of your table that is a Unique Identifier. If you provide that for the formula I had, that would provide patch with what it needs.
The problem with using the Lookup that you are stating that works now, is that you are double-hitting your datasource (performance issues). You have already gathered the primary key once, so doing it again is just wasting the performance...you just need to provide it to patch and it will do all the work for you.
Think about this part in addition - your Lookup is going to return a record...the entire record. So, not only are you waiting on the datasource to do the lookup, but then also for the full record of data to be transmitted back to the app. AND, all for what...the only thing that Patch is going to do with the record is look at ONE column...the primary key.
So, bottom line, performing the ForAll backwards and then combining that with a Lookup is a performance killer.
It's like going to the store with a cart of items and putting them one at a time at the register, scanning it, bagging it, then paying for it, and THEN putting the next item up and repeating over and over. It would take a long time. Instead (just as the case with the Patch and a ForAll table), you would put ALL of your items at the register and scan them all and bag them all and then perform one payment...much faster!
Hi, it seems the solution works : i changed the formula in the Patch function
instead of
All responses (
Answers (
LookUp(tbEtudes;tbEtudes[@ythabo_codeaffaire]=ThisRecord[@ythabo_codeaffaire]i now have :
LookUp(tbEtudes;tbEtudes[@ythabo_codeaffaire] = Etudes_Co[@ythabo_codeaffaire]);
it seems to be working.
Have a nice day.
Owl85
Hi, RandyHayes, many thanks for your quick answer.
I tried your formula but with a modification in order just to consider the queries of modification.
Patch(tbEtudes;
ForAll(Filter(Etudes_Co; modif in "modif");
{ythabo_codeaffaire: ythabo_codeaffaire;
ythabo_nom: ythabo_nom;
ythabo_client: ythabo_client;
ythabo_statut: ythabo_statut;
ythabo_budgetglobal: ythabo_budgetglobal;
}
)
)
I get an error telling that codeAffaire already exists in the table and that it's not possible to have more times the same value in the primary key column.
it seems that it tries to create new records in the table instead of modifying the ones already existing.
To start, your formula has the ForAll backward. You are trying to use it like a ForLoop in some development language - which PowerApps is not. ForAll is a function that returns a table of records based on your iteration table and record schema.
It is more efficient to use the function as intended and will provide better performance, especially with a Patch!
One thing you should keep in mind about patch (and most all other "data modifying" functions in PowerApps) is that they only need the primary key of your record in order to determine what to do.
If you supply patch with the primary key of your record, it will know to modify it. If you provide a blank key, it will know to create it. So, there is no need to put two sets of logic into your app in order to specify which to do...just let patch do the work!
Your formula should be more like the following:
Patch(tbEtudes;
ForAll(Filter(Etudes_Co; modif in "modif|créa");
{ythabo_codeaffaire: ythabo_codeaffaire;
ythabo_nom: ythabo_nom;
ythabo_client: ythabo_client;
ythabo_statut: ythabo_statut;
ythabo_budgetglobal: ythabo_budgetglobal;
}
)
)
Now, some assumptions are made here...and one is that your ythabo_codeaffaire is your primary key column and that Etudes_Co is some sort of collection and that the ythabo_codeaffaire column would be blank if it was a new record and have a valid value if it exists.
Also assuming (since you mentioned "unchanged" in your post) that you are using the modif column in your collection to indicate which action to perform. If so, then the above formula will provide the results you are looking for.
IF you put this formula in place and patch provides an error stating it is looking for a record and not a table, then the names of your columns in your ForAll table record are not correct. Make sure that you are using the real schema names for the columns in the record definition and it will then all work just fine.
I hope this is helpful for you.
#1
WarrenBelz
146,645
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,997
Most Valuable Professional
