IF expression to apply value on form field entry (excel connector)

[rsymmons]

Hi,

I'm trying to add Excel logic into an "add a row into table" excel function. I'd like to provide a value based on 2 values being met. 

E.g. IF volunteer type = Team Leader provide value 27001 in AccountCode. IF volunteer type = Volunteer provide value 35410 in AccountCode. 

The excel foruma for this would be: =IF(F2="Volunteer", "27100", IF(F2="Team Leader", "27750",)) however I'm unable to replicate this within the Flow. 

Thanks,

Rob

[ChristianAbata]

hi @rsymmons  you could use replace to do that. Compose has the value Team Leader

replace(outputs('Compose'),'Team Leader','27750')

[rsymmons]

Hey Christian, 

I would need to provide one value for TeamLeader and another for Volunteer in the same field. How would I go about doing that using the below? 

Thanks,

Rob

[Pstork1]

Unfortunately, the Excel Table you connect to with Power Apps can't contain any formulas.  So you can't add a formula to a Row that you save to the Table from Power Apps.  As @ChristianAbata said, you can add a specific value calculated by Power Apps to the table.  Just use Power Apps to duplicate the result of the formula when adding the row. But there is no way to add the formula itself.

[rsymmons]

Hi,

Sorry not sure if I follow - is it not possible to change the value in a single cell based on the value provided in another cell?

What we want to achieve is if the word "volunteer" is provided, put a number in cell C1, If the word "TeamLeaver" is provided, put a different number in cell C1. 

Thanks for the help 

[ChristianAbata]

hi @rsymmons  where are your values? I'm thinking in two posible escenarios.

1.- You are getting values from a form and you want to convert this values with the condition that you mention and then save it into excel. If is yes you can follow my explanation to transform your data with replace function.

2.- If you are triying to do the conversión by using formules in excel, then @Pstork1  gives you the answer, Is not posible to save values from power automate to an excel table that contains formules. The first step to add data into excel is create a table so if you add a formula in your excel table of file, you can't use PA.

If my answer is well for you, please consider mark as resolved to guide others.

[Pstork1]

Sorry, let me clarify.  You can't have EXCEL change that value for you, because you can only add values to cells in Excel when adding a row.  If you are adding the row, and have access to the the values of the other two cells, then you can have Flow calculate what the row value should be and add it to the cell.  But if someone opens the Excel spreadsheet and changes one of the two values the value in the cell won't change

[rsymmons]

We're receiving the values from a form. I basically want the "add a row into a table" function for the Excel connector to add a value if the form field provides a certain value.

E.g.

If form field "volunteer type" = "TeamLeader" use value "Value1"

If form field "volunteer type" = "Volunteer" use value "Value2"

This is easily achievable in Excel, I'm asking if I could place this formula in Flow instead to provide a value using the Excel connector (not to actually perform this function within Excel). 

Thanks,

Rob

[Pstork1]

Here's what I suggest.

1) create a variable to hold the value you want to insert in Excel

2) Add a condition that checks if  volunteer type = Team Leader.  Volunteer type is the field in the Form and Team Leader is the expected value.

3) In the Yes side of the condition set the variable to 27001 In the No side set it to 35410.  

4) insert the value of the variable into the row in excel.

For Step 2.  If you want to account for more than two options, use a switch instead of a condition.

[rsymmons]

Surely there is a simpler way of achieving this?

Is there no way of building a condition in here based on the form output field with the red arrow next to it?

[Pstork1]

Yes, you should be able to build an IF() statement using the expressions tab and dynamic content.  The format is 

IF('Volunteer Type' = 'Team Leader','27001','35410')

Or something similar.  It depends on the dynamic content names and the data types involved. 

[rsymmons]

The expression is invalid. Do I need to reference the exact field name of the "volunteer type" field and not just the word?

[Pstork1]

You can't just type the expression in.  You need to use the expression tab and the dynamic content tab to build it out.  Start in the expression tab and type IF(). then place the cursor inside the parentheses and switch to the dynamic content tab.  select the field you want to check.  Then go back to the expression tab and fill in the rest.

[rsymmons]

Hi,

Thanks this was helpful but I'm still running into issues getting the expression right. I am typing this out in the expression tab on the field however it won't accept by formula:

if(triggerBody()?['VolunteerType']="Volunteer","27001", if(triggerBody()?['VolunteerType']="TeamLeader","35001"))

I also tried:

if(triggerBody()?['VolunteerType']="Volunteer","27001") - no luck

[Pstork1]

Based on your previous screenshot I believe Volunteer type has a space, which isn't in your formula.  So I think what you want is this.

if(triggerBody()?['Volunteer Type']="Volunteer","27001", "35001")

But as I said before the actual formula is dependent on the field names and the data type of the field you are putting it in.  Is the 27001 supposed to be a number or text? Does Volunteer Type have a space in the name? etc. 

[rsymmons]

[Pstork1]

My typo.  Change all the double quotes to single quotes in the formula.  Then try that.

[rsymmons]

Still invalid:

if(triggerBody()?['Volunteer Type']='Volunteer','27001', '35001')

[rsymmons]

=if(triggerBody()?['VolunteerType'],'Volunteer','27001') did the trick. It didn't like the =

[rsymmons]

Now it's saying the expression is invalid when I try to save the flow. Still doesn't seem to like it!