Hi, @HollyNewell , I guess my main question is:
Do you intend to use the "Status" column for anything else?
I ask this because ( purely based on the above ) I see a binary situation there. It's either that input is needed, or not.
Purely SharePoint Solution
Columns
I have made three columns:
Single Line of Text - Country
inputRequired - Calculated Yes/No - This uses a formula to define if it is Yes or No.
Status - Calculated Single Line of Text - This could use the same formula as inputRequired, but I decided to base it on that field for variety.
Formulas
For the 'Yes/No' calculated column 'inputRequired' I used this formula:
=OR(EXACT(Country,"-DE"),EXACT(Country,"-IT"),EXACT(Country,"-UK"),EXACT(Country,"-FR"))
I used this code for the formatting:
{"elmType":"div","style":{"box-sizing":"border-box","padding":"0 2px"},"attributes":{"class":{"operator":":","operands":[{"operator":"==","operands":["@currentField",true]},"sp-css-backgroundColor-blockingBackground50",{"operator":":","operands":[{"operator":"==","operands":["@currentField",false]},"sp-css-backgroundColor-successBackground50",""]}]}},"children":[{"elmType":"span","style":{"line-height":"16px","height":"14px"},"attributes":{"iconName":{"operator":":","operands":[{"operator":"==","operands":["@currentField",true]},"",{"operator":":","operands":[{"operator":"==","operands":["@currentField",false]},"",""]}]}}},{"elmType":"span","style":{"overflow":"hidden","text-overflow":"ellipsis","padding":"0 3px"},"txtContent":"@currentField.displayValue","attributes":{"class":{"operator":":","operands":[{"operator":"==","operands":["@currentField",true]},"",{"operator":":","operands":[{"operator":"==","operands":["@currentField",false]},"",""]}]}}}],"templateId":"BgColorBoolean"}
For , the Single Line of Text calculated column I used this formula:
=IF(EXACT(requiresInput,TRUE),"Input Required","n/a")
I used this code for the formatting:
{"elmType":"div","style":{"flex-wrap":"wrap","display":"flex"},"children":[{"elmType":"div","style":{"box-sizing":"border-box","padding":"4px 8px 5px 8px","display":"flex","border-radius":"16px","height":"24px","align-items":"center","white-space":"nowrap","overflow":"hidden","margin":"4px 4px 4px 4px"},"attributes":{"class":{"operator":":","operands":[{"operator":"==","operands":["@currentField","Input Required"]},"sp-css-backgroundColor-blockingBackground50",{"operator":":","operands":[{"operator":"==","operands":["@currentField","n/a"]},"sp-css-backgroundColor-successBackground50",{"operator":":","operands":[{"operator":"==","operands":["@currentField",""]},"","sp-field-borderAllRegular sp-field-borderAllSolid sp-css-borderColor-neutralSecondary"]}]}]}},"children":[{"elmType":"span","style":{"line-height":"16px","height":"14px"},"attributes":{"iconName":{"operator":":","operands":[{"operator":"==","operands":["@currentField","Input Required"]},"",{"operator":":","operands":[{"operator":"==","operands":["@currentField","n/a"]},"",{"operator":":","operands":[{"operator":"==","operands":["@currentField",""]},"",""]}]}]}}},{"elmType":"span","style":{"overflow":"hidden","text-overflow":"ellipsis","padding":"0 3px"},"txtContent":"@currentField","attributes":{"class":{"operator":":","operands":[{"operator":"==","operands":["@currentField","Input Required"]},"",{"operator":":","operands":[{"operator":"==","operands":["@currentField","n/a"]},"",{"operator":":","operands":[{"operator":"==","operands":["@currentField",""]},"",""]}]}]}}}]}],"templateId":"BgColorChoicePill"}
I didn't make those formatting codes myself, for the text field I created a separate choice field with the exact same values it calculates, then copied the automated formatting that the modern SharePoint site does for it. For the Yes/No, you can edit it by right clicking the column when looking at the list.
Power Automate Solution
If I'm reading this correctly, you already have a flow that is handling this, you can go one of two ways:
- Full Flow Logic - Create a country array variable, and a status variable, and set the status variable on that.
- Array variable - I'll call mine countryArrVAR - This contains a list of the texts to look out for.
- String variable - statusVAR - Is changed dependent on their presence in the 'Country' column.
- Condition action - A four pronged OR condition, where each prong will be to see if the Country column value equals the text above.
- Expression Logic - Use a one or two expressions to set a boolean then decide if the Status text will be "Input Required" or "N/A"
Full flow logic is the easiest to understand because you can see it all visually, but I'll flesh both of these out with a couple of examples in a bit, but hopefully I've plotted them out enough for you to work out the rest.
If you respond, I'll reply again, otherwise I'll edit more into this reply myself. 🙂
