Hiding formula with two different values

[Kuno66]

i have a hiding formula on the new entry form--=if([$StatusAnsuchen]=='Pending','false', 'true') that works fine so far. but now i´d like to add another statement based on other columns value (choice column)--=if([$Ansuchen]=='Andere','true', 'false') how can i add them together? i tried ...=if([$StatusAnsuchen]=='Pending','false', 'true' && [$Ansuchen]=='Andere','true', 'true')) but its not working...

thanks for any help

kurt

[Expiscornovus]

Hi @Kuno66,

You are talking about a conditional formula for a field in a SharePoint view, correct? This is not directly related to Power Automate but I will try and help you out anyway 😁

Can you try and use this formula instead?

=if([$StatusAnsuchen] == 'Pending' && [$Ansuchen] == 'Andere', 'false', 'true')

More examples can be found over here:

https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/list-form-conditional-show...

[Kuno66]

hello...

its me again...

it is possible with a formula to write value based on a choice field?ex. 

=if([$Ansuchen]=='Weiterbildung', write value to field 'true', 'false')

[Expiscornovus]

Hi @Kuno66,

That depends. Does your choice column allow the selection of multiple values?

That type is not supported:

https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/list-form-conditional-show...

A choice with a single value should work. In that same document they also have an example for that:

https://docs.microsoft.com/en-us/sharepoint/dev/declarative-customization/list-form-conditional-show...

[Kuno66]

no selection multiple values.

but how do i compose the formula?

=if([$Ansuchen]=='Weiterbildung', write value to field 'true', 'false')...

the formula reference only if choice field and column have a certain value. ok that works.

but i need if choice is weiterbildung than add email address to field.

thank you

[Expiscornovus]

Hi @Kuno66,

These example are only to show or hide fields, formulas for form configuation. I think you are now talking about different formulas, it sounds like you are now talking about calculated columns?

[Kuno66]

yes if i can reach my goal.

but i can´t find the right formula.

i have that text column called "Weiter". and a choice column mit choices.

can i add a calculated value in the column Weiter that looks at the choice column? ex.

if choice column is sharepoint than add value blablabla....

[Kuno66]

maybe i can achive this in flow?

but what  is the formula there.? if ansuchen value= weiter than add test@test.com else use value.

or better in the grant access to an item section because i need it there

[Expiscornovus]

Hi @Kuno66,

You could use an expression like below in the update item.

You might need to update the expression a bit. I am using a Get Item action.

if(equals(outputs('Get_item')?['body/Ansuchen/Value'], 'Weiterbilding'), 'test@test.com', 'Do nothing')

[Kuno66]

saving the flow gives me an error:

Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The action(s) 'Get_item' referenced by 'inputs' in action 'Update_item_2' are not defined in the template.'.'.

[Expiscornovus]

Hi @Kuno66,

What is the name of the action you are using to collect the value of Ansuchen? In my case it is called Get Item. That might not be the case in your flow. 

[Kuno66]

fafdssa

if(equals(outputs('Update_item_2')?['body/Ansuchen/Value'], is equal to 'Weiterbildung'), 'test@test.com', 'Do nothing')    it gives the error "invalid expression...... i can´t save the formula.

[Expiscornovus]

Hi @Kuno66,

There should be another action where you are getting the dynamic content fields like ID, ErstellDat from.

I am assuming this is either the trigger action like when item is created or some other action like Get Items, etc.

Can you share a screenshot of your whole flow setup. This will make it easier for me to come up with the correct expression 🙂

[Kuno66]

i added a get item action and used the expression. now it workes but the flow fails with applying the grant access items. when i use the choice weiterbildung everything works fine, when i user some of the other coices flow fails and grant access rights are not apllied.

The 'inputs.parameters' of workflow operation 'Grant_access_to_an_item_or_a_folder' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'parameter/recipients' is required to be of type 'String/email'. The runtime value '"kuno66@test.com;admincloud@test.onmicrosoft.com;Sharepoint-Genehmiger@test.com;Sharepoint-Finanzdienst@test.com;Sharepoint-Personalamt@test.com;;Do nothing"' to be converted doesn't have the expected format 'string/email'.

[Expiscornovus]

Hi @Kuno66,

That is correct, the sample only works with the Weiterbildung choice. If you want it to work with other choices as well you need to amend the expression to meet that requirement. 

Which other options do you have? And do they need to be mapped to the same e-mail address or to a different e-mail per choice option?

[Kuno66]

i have 4 choices "Aussendienst" - Weiterbildung - Abrechnung Aussendienst - Andere

the other 3 choices can mapped to the same email adress.

[Expiscornovus]

Hi @Kuno66,

In that case you could just use the false part of the if function for those other three choices. Below is an example.

if(equals(outputs('Get_item')?['body/Ansuchen/Value'], 'Weiterbilding'), 'test@test.com', '3choicesemail@test.com')

[Kuno66]

now it works.:-<))

what if the 3 other choices map 3 differnet email address?

[Expiscornovus]

@Kuno66, you can amend the expression to use nested if functions. But the expression becomes a bit longer:

if(equals(outputs('Get_item')?['body/Ansuchen/Value'], 'Weiterbilding'), 'test@test.com', if(equals(outputs('Get_item')?['body/Ansuchen/Value'], 'Aussendienst'), 'aussendienst@test.com', if(equals(outputs('Get_item')?['body/Ansuchen/Value'], 'Abrechnung Aussendienst'), 'abrechnung@test.com', 'andere@test.com')))

Or another approach would be to use a mapping array and filter that array based on that Ansuchen Value. I would suggest that approach when the amount of choices is a bit more.

[kuno6661 · Accepted solution]

thank you very much for your help.

regards

kuno