Solved: Re: Recommendation to bulk update records.

STS1SS · ‎10-03-2023

In the process of moving an Access app to power apps.

I have COMPANY records and a company may have multiple locations, so related LOCATION records.

In original ACCESS app these are in separate tables. I decided to use the CDM tables as much as possible here, so opted to use the 'Account' table for both tables, combining into one table. Added a column "Type". It has two choices: "Company" or "Location". Imported the two tables. Now I have the data in, but no relation between the two. I need to update the standard 'Parent Account' field to reflect the relationship.

I will have other similar situations so I thought it would be a good idea to ask for recommendations on best practices to do this.

My basic thought is when importing the Company/Location records I added fields for the CustID (company) and LocID (location) and I can just act on those, just not sure if this makes sense, or if there is some better way. Or even if I should have done it completely different... maybe during import or before import.

Open to suggestions

Thanks

Amik · ‎10-04-2023

@STS1SS - yes this has happened to me on occasion without an obvious or consistent reason. I consider this a bug and use the legacy environment as this more robust.

Advanced settings:

1. Select the Funnel

2. Select your table via the "Look for" dropdown

3. Select Edit Columns

4. Select Add Columns

5. Select the Checkbox on the left of the “Display Name” header to select all columns, then click OK to exit this window and OK again to exit the Edit Columns window.

6. Click the Results button

7. Select the Export icon

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Imran-Ami Khan

View solution in original post

Amik · ‎10-03-2023

@STS1SS - you might find this blog of mine helpful on bulk updating relationships:

https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/Bulk-update-Dataverse-LookUp-column-us...

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Imran-Ami Khan

STS1SS · ‎10-04-2023

@Amik

ok... I believe your suggestion is essentially export to excel? And do it there?

STS1SS · ‎10-04-2023

@Amik

Let me say that again... you didn't say to EXPORT to Excel, but your suggestion is to IMPORT from excel. Do the work OUTSIDE dataverse and then import. I like it. But in the process I have an issue.

I first need to EXPORT the current table because this is the only place the AccountID exists. In order to get the relationship right, I need another column as well, the CustID. This is the original column in ACCESS.

So I export the Accounts table to a csv. This table is missing columns. One of them is the CustID I need. I can work around this (I think) using Excel, but the question remains: why aren't all columns exported? The system did not offer to allow me to make any choices at all.

Amik · ‎10-04-2023

@STS1SS - yes this has happened to me on occasion without an obvious or consistent reason. I consider this a bug and use the legacy environment as this more robust.

Advanced settings:

1. Select the Funnel

2. Select your table via the "Look for" dropdown

3. Select Edit Columns

4. Select Add Columns

5. Select the Checkbox on the left of the “Display Name” header to select all columns, then click OK to exit this window and OK again to exit the Edit Columns window.

6. Click the Results button

7. Select the Export icon

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

Imran-Ami Khan

STS1SS · ‎10-05-2023

@Amik

OK, so what I ended up doing is really a combination of things. Some based on what you indicated here, and some other info as well.

I ended up exporting the parent table to get that UID column and pulling it in based on a lookup in excel, then importing CSV using the data import wizard in environment power platform admin center.