Hi all,  I'm very new to all of this, so your expertise is required.

I have two lists, one is my main list which will include a local currency amount column, with a column for what the local currency is.  In another list I have a currency conversion for each of the these currency - we don't want this to be a live link.  

My question is.  What process or power automate do I use to make a column return the EUR amount (from the currency list) based on the amount and the currency in the main list?

Hi all,  I'm very new to all of this, so your expertise is required.

I have two lists, one is my main list which will include a local currency amount column, with a column for what the local currency is.  In another list I have a currency conversion for each of the these currency - we don't want this to be a live link.  

My question is.  What process or power automate do I use to make a column return the EUR amount (from the currency list) based on the amount and the currency in the main list?

Hi Sam Sharepoint_Sam-Novice, yes you can do this quite easily with a flow in Power Automate and a button on your main list to trigger it. So for this example we've got our main list and a lookup list as shown below. The LocalCurrencyAmount column is a number column and the currency column is a single line of text column. The column where the button is is a single line of text column and I'll show the formatting of that further down. We've then got a currency column for the EUR amount:

The second list is the currency lookup list with one item for each currency:

Before you do the button formatting you need to build the flow. The trigger is a SharePoint For a selected item. 

The first action is a SharePoint Get item to get the details of the item that was selected. In the ID field you select ID from the dynamic box on the right.

Next we want to look up the item in the lookup list that corresponds to the currency in the main list. So you do that with a Get items action and in the advanced options there is a filter query field. So you type Title eq '' and in between the apostrophes select Currency from the Get item from the Main List section of the dynamic content box:

Next, even though we are only bringing back a single item from lookup list, nevertheless add an Apply to each control and select value from the Get items from the Lookup List section of the dynamic content box.

Next add a couple of Compose actions I've named the first one Compose Local Currency Amount and in the Inputs field I've selected LocalCurrencyAmount from the Get items from the Main List section of the dynamic content box. The second Compose action I've named Compose Exchange Rate and in the Inputs field I've selected ExchangeRatetoEuro from the Get items from the Lookup List section of the dynamic content.

Next we get the flow to multiply the Compose Local Currency Amount by the Comppose Exchange Rate. We do this with another Compose action and in the dynamic content box click the Expression tab at the top and type mul(and then click the dynamic content box and select the Outputs of the 2 Compose actions. Your expression will look like this with underscores erplacing the spaces (they're put in automatically):

Finally we want to update the item in the main list with the calculated Euro amount. So select a SharePoint Update item action, enter the site and the main list and it will find the columns. For the ID select ID from the Get item from the Main List section of the dynamic content. For the EUR column select the output from the Compose EUR section of the dynamic content.

Save and name your flow and go to the main screen for the flow and copy the ID of it from the address bar at the top of the screen:

Next format the column in your main list which will have the button (in the advanced mode part of the format this column) and paste in the following JSON changing the flow ID to yours and the txtContent to whatever you want the text to be. There are other parts of the JSON you can also change:

{\n  \"$schema\": \"https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json\",\n  \"elmType\": \"button\",\n  \"txtContent\": \"Calculate Euro Amount\",\n  \"customRowAction\": {\n    \"action\": \"executeFlow\",\n    \"actionParams\": \"{\\\"id\\\": \\\"252e9ec9-eff4-412d-ba6e-06de5ac401fb\\\",\\\"headerText\\\":\\\"Calculate Euro\\\",\\\"runFlowButtonText\\\":\\\"Calculate Now\\\"}\"\n  },\n  \"style\": {\n    \"background-color\": \"#468259\",\n    \"color\": \"white\"\n  }\n}

So when a user clicks the button in the list it will trigger the flow and put the Euro amount in the column. The attached video shows this in action based on these lists and the flow above. I've compressed the time after the button has been clicked as it can take about 10 seconds for the Euro amount to be added into the list item.

I hope that helps but come back with any questions if something isn't clear.

Rob
Los Gallardos
Microsoft Power Automate Community Super User

RobElliott Thank you so much.  I'll give this a try, but wanted to thank you for such an in depth response which is easy to understand.