Unanswered

Patch data form one database to another with ForAll and comparing IDs

(0) Share

Report

Posted on by lai_lai

I've a excel list with some different colums then Sharepoint list, but both of then have the same intention.

For exemplo: in SP list I have A, B and C coluns, and the excel I have D, F and G. I want to add the D, F and G coluns from the excel sheet to Sp list.

I will create this coluns in SP list, and want to upload the items from sp list with the excel list. Also, I have the same itens in both sheets, with a coluns that work as a ID.

So I can compare the items to insert the data of the new colums in the correct item. Maybe I should try something like:

ForAll Excel

Patch Sharepoint List

Lookup Sharepoint ID = Excel ID

Colunms from patch function

I'm trying and afraid of and ForAll Lookup didn't work with different databases, but not sure...

I've already tried with galleries, tables, and collection to store the data and compare

Can someone help me? (:

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (0)

WarrenBelz 153,079 Most Valuable Professional on at

Like (0)

Report

Hi @lai_lai ,

Firstly, you cannot add columns to a data source that do not already exist, so how exactly do you want to combine this data ?

Was this reply helpful? Yes No
lai_lai 41 on at

Like (0)

Report
Hey @WarrenBelz !

I'll create this columns to add the data and keep both Sharepoint and Excel with the same structure.
After that, I want to copy the data from one list to another, keeping the same columns in both lists.

Explaning a bit more:
The Sharepoint list contains a data controle to the application of one form, and we used to use another tool to apply the form. This tool provide a excel with the answers.

So I taked the excel file from the application form and want to copy the data to the Sharepoint list, and use only one list (and tool) to control the calendar of application and the application form.

So, what I want is to copy the questions of application form (excel file) to Sharepoint list, after creating the columns in Sharepoint that correspond to the questions of the excel file.

Now, I'm trying something like this above, because didn't work with gallery and table. I'm trying to insert trought a collection before:

ClearCollect(colExcel;TabelaTESTE);; ClearCollect(colSharepoint;SP_TESTE_PCTRAMA);; ForAll(colExcel; If(colSharepoint;Text(CI1CodigoIndividual) = colExcel;CI1CodigoIndividual; Patch(SP_TESTE_PCTRAMA;ThisRecord; {Tradicao:"Teste patch ForAll"} ); ) )

Was this reply helpful? Yes No
WarrenBelz 153,079 Most Valuable Professional on at

Like (0)

Report
Hi @lai_lai ,

Try this (you will not need the top two collections)

With( { _XL: TabelaTESTE; _SP: SP_TESTE_PCTRAMA }; ForAll( _XL As _Data; With( { _ID: Lookup( _SP; Text(CI1CodigoIndividual) = _Data.CI1CodigoIndividual ).ID }; If( !IsBlank(_ID); Patch( SP_TESTE_PCTRAMA; { ID: _ID; Tradicao: "Teste patch ForAll" } ) ) ) ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
lai_lai 41 on at

Like (0)

Report
Man, I'm trying but I confess I didn't undertand all.. but let me give you some more informations and change some exemples I gave:
The columm in TabelaTeste that has the ID it's called

CI1CodigoIndividual

And the columm in sharepoint that has the ID to compare it's called: Title

When you use the "_ID" do you mean the Id columns? So I will change the _ID for the

CI1CodigoIndividual

?

And I didn't undertood the _Data you created with the As operator. Are you changing the format of the sheet from excel to something more near a sharepoint list? So because of it you use _Data?

I'm writing something like this:

With( { listaExcel: TabelaTESTE; listaSharepoint: SP_TESTE_PCTRAMA }; ForAll( listaExcel As _Data; With( { CI1CodigoIndividual_Column1; Lookup( listaSharepoint; Text(CI1CodigoIndividual) = _Data.CI1CodigoIndividual ).ID }; If( !IsBlank(CI1CodigoIndividual); Patch( SP_TESTE_PCTRAMA; { CI1CodigoIndividual: _ID; Tradicao: "Teste patch ForAll" } ) ) ) )

Was this reply helpful? Yes No
WarrenBelz 153,079 Most Valuable Professional on at

Like (0)

Report

Hi @lai_lai ,

The As statement is a disambiguation operator the specify the source and destination separately. It is technically not needed but avoids a lot of potential errors.

Every SharePoint list and Library has an ID field which is the unique identifier of the record and this needs to be used in the patch.

Was this reply helpful? Yes No