Overcoming calculated column IF statement limitations

(1) Share

Report

Posted on by CU21022120-0

A SharePoint List is the data source.

I have 126 tasks in a choice column allowing for only 1 selection. The user won't know the L5 process (25 choices) and also know the L4 work group (5 choices) related to the task they selected. Tasks > L5 > L4 (not use the greater than but showing the flow of information)

I would like to use calculated columns from the task to assign the L5 process and another calculated column to assign the L4 work group based on the L5.

The problem is calculated columns have limited IF statements for the 126 tasks.

Can and how would I use power automate to choose from the 25 L5 processes based on 1 of the 126 task options? I would most likely just have this flow run manually.

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Michael E. Gernaey 53,433 Super User 2025 Season 2 on at

Like (1)

Report

Hi,

Is the back end Dataverse or SharePoint or other? What is the front end?

From a power automate perspective, you could easily pass in the selection from the 126 Tasks, then first have it do the IF to pick the L5 easy in a compose. Oh its going to be long... but possible.

Then after 5 is picked, have another to pick 4.

Then you could pass them back to a Canvas App if its that, or depending on how its called..

You could also have a Flow that is triggered when the user selects and saves the option from 126.

Then have it select the 5 and 4 and save them. It would depend on if you want them to see in real time what is 5 and 4.

So either of those works but again, you didn't say the UI or how its happening.

Was this reply helpful? Yes No
CU21022120-0 4 on at

Like (0)

Report

The data is coming from a sharepoint list.

I have updated the post to indicate the data source.

Best regards,

Jeremy

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

Like (0)

Report
If I understand correctly, you don't need to use if statements at all.
Just use the modulo function on the task selection index.

If you have a list like this:

[ { "No": 0, "Task": "Task 0 0 " }, { "No": 1, "Task": "Task 0 1 " }, { "No": 2, "Task": "Task 0 2 " }, { "No": 3, "Task": "Task 0 3 " }, { "No": 4, "Task": "Task 0 4 " }, { "No": 5, "Task": "Task 1 0 " }, { "No": 6, "Task": "Task 1 1 " }, { "No": 7, "Task": "Task 1 2 " }, ... etc ]

For a selected task number n, you can calculate L5 and L4 as follows:

L5 = n / 5

L4 = n mod 5

Was this reply helpful? Yes No
CU21022120-0 4 on at

Like (0)

Report

How would use the modulo function on the task selection index? You have a guide?

Was this reply helpful? Yes No