Automatically assigning a value based on another column value

Question

Hello All,&nbsp;I was wondering if there is a process in Sharepoint maybe using power automate that will help me fill a column automatically depending on another column value.&nbsp;&nbsp;As an example I have 26 Departments listed as possible choices in column AI want Column B to auto generate a DepartmentID based on the department selected in column A&nbsp;So every time HR (one of the 26 departments)&nbsp; is selected in column A it will generate a "1" in column B.Every TIme Operations is selected in column A it will auto populate a "2" in column B.&nbsp;And so on and so fourth, my apologies if there is a simple solution, I'm quite new to using SharePoint and power apps.

robelliott · Answer

NicosAcuna&nbsp;well you could do it with a Switch control in Power Automate as you would be just inside the 27 case limit, but it's more work than having a calculated column using multiple If expressions. So your department column would be a choice column then you'd add the following formula to your DeptID calculated column:=If(Department="HR",1,If(Department="Operations",2,If(Department="Warehouse",3,"")))etc etc.&nbsp;&nbsp;RobLos GallardosMicrosoft Power Automate Community Super User

syedfaizuddin · Answer

RobElliott&nbsp;Thank you for this solution! I was able to get this to work finally! I ran into another issue however. Lets say the choice column was multiselect, meaning you could select multiple options. How would I go about adding the points up on the second column?&nbsp;&nbsp;Below is my example and error I'm getting:&nbsp;(I have three selectable options and gave them all 2 points each for a total of 6 points. Lets say I were to select "Multiple Users" and "20 Users," that should equal up to 4 points. If I selected all three options from the drop down menu, it would be 6 points. So I have two columns related to this. First column is "Collaboration" and second column is "CollaborationCalculated" which is a Calculated Field. When I add this formula below in the "CollaborationCalculated" column it works until I make the "Collaboration" column to Allow multiple selections. I get the below error which says: "unsupportedFieldTypeError." Is there an IF statement with addition involved to make this work?)&nbsp;&nbsp;=IF([Collaboration]="Multiple Users",2,IF([Collaboration]="20 Users",2,IF([Collaboration]="10 Users",2,"")))&nbsp;&nbsp;&nbsp;

cuba_rj · Answer

Hi, RobElliott&nbsp;&nbsp;I have the same problem in this post, but if I have any change in the number of departments or even names I would have to return to this formula and adjust, am I right?Is there any other solution that would handle variables for Departments?

rob_elliott · Answer

Cuba_RJ&nbsp;yes you would have to update it manually, it wouldn't update dynamically.&nbsp;RobLos GallardosMicrosoft Power Automate Community Super User.Principal Consultant, SharePoint and Power Platform WSP UK (and classic 1967 Morris Traveller driver)

awesome390 · Answer

This doesn't work for me I get an error:
Sorry, something went wrong
One or more column references are not allowed, because the columns are defined as a data type that is not supported in formulas.

One column is choice, the other column is my calculated column which puts the text I want.

Forum Discussion

Automatically assigning a value based on another column value

Share

Resources