Coupling selected values with numbers

(0) Share

Report

Posted on by Daniël

147

Hi, I want to build a flow that does the following (Automate in bold):

Step 1: A form gets submitted through Power Apps.

Step 2: Flow picks up this new submission to Sharepoint 1.

[ - ]

Step 3: Adjusts item in Sharepoint 2 based on the information in the Sharepoint 1 item.

Between Step 2 and 3, the flow would be much clearer if I could match certain values to numbers, so it knows which item to update in Sharepoint 1.

For example, if the submitted form has a selected fiscal year of 2024. I would like a step that translates the year into an ID number:

Fiscal Year	Sharepoint 2 ID
2023	1
2024	2
2025	3
2026	4

Or even more complex, matching multiple values to a certain ID:

Fiscal Year	Department	Sharepoint 2 ID
2023	Sales	1
2023	Procurement	2
2023	Internal	3
2024	Sales	4
2024	Procurement	5
2024	Internal	6

So based on the last item, if I were to submit a form to Sharepoint 1 with the following details:

Name	Department	Fiscal Year	Spending	Income
Smith, John	Internal	2023	55.000	0

It would be able to deduce that the Sharepoint 2 ID would be 3, so I can use 3 in the 'Get Item' operation.

Does anyone know how to approach this within Power Automate?

Categories:

Building flows

All responses (4)

Answers (2)

Sort by

Verified answer

v-jefferni Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Daniël ,

Since SharePoint list ID would be generated automatically and will be lost when an item is deleted, the correspondence would collapse. If items in list2 won't be deleted, then you can use below logic to get the ID:

1. get the difference of 'Fiscal Year' and 2023

sub('Fiscal Year',2023)

2. get a number from 1-3 based on the Department:

if(equals(Department, 'Sales'),1,if(equals(Department, 'Procurement'), 2, 3)

3. multiply the first number with 3 and add the second number to get the ID:

add(mul(number1, 3), number2)

You can get there numbers in Compose actions using expressions.

Best regards,
Daniël 147 on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @v-jefferni , thank you for your reply!

Could you show me how and where you implement these functions through a screenshot? I wouldn't know which operations to use for example.
v-jefferni Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Daniël ,

As I told you, you can use Compose actions with expressions to get these numbers:

Best regards,
Verified answer

Daniël 147 on at

Like
a
(1)

Report
Copy link

Link copied!
Thank you @v-jefferni ,

This is the first time for me using if-statements so I struggled a little bit with the if statements, especially considering the dynamic content. For others, my Compose expression for linking the Department to a specific number/ID looked something like this:
if(equals(outputs('Get_item')?['body/Department/Value'], 'Sales'), 1, if(equals(outputs('Get_item')?['body/Department/Value'], 'Procurement'), 2, 3))
'Get_item' refers to the Get item action and the Department is the column header. I was able to select these items dynamically.