How to update a dataverse table with data coming from an external API ?

[PowerNooob]

Hi all, 

i have a shceduled cloud flow that triggers every 2 days, in the first action a get currencies exchange rates from a custom connector, in the dataverse side i have a table named currency_list (columns : name, code, rate), i would like to update every row of my column with the correct exchange rates, but the two only way i found are : 

1  - Doing a apply to each inside an appy to each (looping through thr rows and also looping through the data)

2 - "Doing update a row" one after another and harcoding the row ID (i need only 8 currencies...)

i also tried to "apply for each" on the table rows then accessing the json data dinamically but i found out that it is not possible.

Is there a nice and clean way to do the job without using an "Apply to each" inside another one ? 

Is there a data type that i can generate with a compose that can be accessed dynamically (using a variable) ? 

Thanks for your help !

[v-jefferni]

Hi @PowerNooob ,

First you can use Parse JSON action to convert the JSON string to an array. In the first Apply to each where looping on this array, Filter array on Dataverse table based on name or code to get the row, then in child Apply to each 2 on this row to Update a row, row id would be:

items('Applu_to_each_2')?['currency_list']

currency_list is the unique identifier column in the currency_list table.

Best regards,

[PowerNooob]

I would like to use only one apply for each if possible, and reference the currency code dynamically in the "update a row" like this Outputs('compose')[item()['code']].

[v-jefferni]

Hi @PowerNooob ,

Sure. You can use expression to reference GUID of the only row:

For other fields, you should be able to select from dynamic contents of Parse JSON.

Just in case that there is no item matching the name in Dataverse table, you will need to use a Condition to check the outputs of List rows:

length(outputs('List_rows')?['body/value']) is equal to 1

Best regards,

[PowerNooob]

What do you have inside of you Parse Json pease ? In other words what is the structure of your json / array ?

[PowerNooob]

Using your method, i have two additional "for each" that appers in my code ! (screenshot attached)...

[Chriddle]

If you have only a few rows to update (and you already know their Ids) your plan 2 is the way to go (IMHO).

But I would not add multiple update connectors but create an additional array of objects with row Id and exchange rate (here "DataverseRows") and loop over them.

This looks like this:

Of course, depending on the data structure of the API response, additional steps may be required.

[PowerNooob]

I like your idea ! And yes i have only a few rows to update and i know the id in advance, but imagine i have to update 100 rows...

When i try to "list rows" then "apply to each", i can update the current row with the corresponding rate inside a "update row" by referencing the result of my compose or parse json this way : Outputs('compose')[item()['theCurrencyCodeColumn'] (inside the update row rate filed), but i have an error inside the "update a row" saying that "File or directory not found", knoing that i got the ID from the current element of the "apply to each" (over "list rows"), i also tested that the table column names i'm trying to deal with are correct by trying to select and filter the data inside the list rows (i also have the same error when i try to "get a row by ID" using dynamic data !)