Unanswered

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

(0) Share

Report

Posted on by 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 !

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (0)

v-jefferni on at

Like (0)

Report
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,

Was this reply helpful? Yes No
PowerNooob 16 on at

Like (0)

Report

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']].

Was this reply helpful? Yes No
v-jefferni on at

Like (0)

Report

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,

Was this reply helpful? Yes No
PowerNooob 16 on at

Like (0)

Report

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

Was this reply helpful? Yes No
PowerNooob 16 on at

Like (0)

Report

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

test.png

Was this reply helpful? Yes No
Chriddle 8,436 Super User 2025 Season 2 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
PowerNooob 16 on at

Like (0)

Report

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 !)

Was this reply helpful? Yes No