Unanswered

Is there a good way to bulk update a database

(0) Share

Report

Posted on by Medoomi

523

I'm not too sure how to ask this question, but I'm wondering if there is a function in PowerApps that would enable me to write new values en masse to all the records in an entity.

Details:

Entity: "Member Agency Contact Lists" has two fields, a lookup field "Contact #" and a text field "Full Name."

Entity: "Member Contact" has three fields, the primary field "Autonumber," and two text fields "First Name," and "Last Name."

The lookup field "Contact #" in "Member Agency Contact Lists" is related to the Autonumber in "Member Contact"

Scenario:

Occasionally (like now), I need to update the "Full Name," field in "Member Agency Contact Lists," with data from "First Name" and "Last Name" from the related records in "Member Contact."

I guess I could use Flow to accomplish this, but I wondered if there were a native function in PowerApps that would do the same job, say on a button press.

Thanks so much!

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

v-bofeng-msft on at

Like (1)

Report
Hi @Medoomi :
Do you want to update the current entity with data from related entities?
The key is to use ForAll and Patch.
I've made a test for your reference:
1\Add a button and set it's OnSelect property to:

ForAll( RenameColumns( ShowColumns( AddColumns( 'Member Agency Contact Lists', "thefullname", 'Contact #'.'First Name' & 'Contact #'.'Last Name' ), /*Create a new field "thefullname" to store the value*/ "crec3_name", "thefullname" ),/*Only extract required fields,"crec3_name","thefullname"*/ "crec3_name", "thename" /*Renamed crec3_name" to "thename" to disambiguate*/ ), Patch( 'Member Agency Contact Lists', LookUp( 'Member Agency Contact Lists', Name = thename ), {'Full Name': thefullname} ) )

In addition,I suggest you use a simpler alternative.
You can directly obtain the results through the entity relationship instead of updating the entity to improve app performance and robustness.

AddColumns( 'Member Agency Contact Lists', "thefullname", 'Contact #'.'First Name' & 'Contact #'.'Last Name' )

The result obtained by the above code is the same as the value of the updated entity 'Member Agency Contact Lists'.
Best Regards,
Bof

Was this reply helpful? Yes No
Medoomi 523 on at

Like (0)

Report

Thank you so very much @v-bofeng-msft. I am very pleased with the results... except that the AddColumn function is subject to the non-delegation limit. My database has 16,000 entries, so only the first 500 are processed (by both formulas)
Is there any way to work around this?

Was this reply helpful? Yes No
v-bofeng-msft on at

Like (1)

Report
Hi @Medoomi:
There seems to be no way to avoid the delegation problem.(I use these functions to modify columns to eliminate ambiguity.If I don’t do this, the whole formula will not work )
Since there is already a relationship between entities, I think that using relationship directly is the easiest way.
My suggestion is to directly call

'Contact #'. 'First Name' & 'Contact #'. 'Last Name'

instead of updating the data source.
Best Regards,
Bof

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (1)

Report

Hi @Medoomi

One of the best practices of relational databases is to not save any calculated fields in the back end when the calculation can be done in the front end. In this case, if you save lastName and firstName you can always put them together as @v-bofeng-msft describes above in PowerApps.

Was this reply helpful? Yes No
Medoomi 523 on at

Like (0)

Report
Thank you so much @v-bofeng-msft & @Drrickryp
I really do appreciate the input & suggestion not to write these values to the database. I'll mark the formula as the solution, though with as many records as I have, I'm stuck behind the non-delegation limit.
I would avoid writing these values, except that I need to perform a gallery filter of "Member Agency Contact Lists" based on First & Last Name (fields natively found in "Member Contacts") on another screen & when I use the text filter:
ContactSearch.Text in 'Contact #'.'Last Name'
This brings a non-delegation warning (two blue lines under "in"), I'm assuming because "Last Name" is two levels of relationship away. I'm trying to avoid that non-delegation warning by writing a "Full Name" in the parent entity, "Member Agency Contact Lists."
For new records, I can easily patch update the fields, but for the 16,000 records that already exist in the database, I need to bulk write those values. I guess Flow would be the best way to do this, or is there another answer I'm overlooking?

Was this reply helpful? Yes No