Appending Text to an excel row based on value comparison

[Sidhant_02]

Hi everyone,
I have a scheduled flow that gets generated Monday morning and the flow looks like this:

The loop wherein I am adding rows and also checking if the current week does it have any Holiday for which I have maintained a separate list

So now I had one more requirement which was some individuals report to office daily (no hybrid mode) that is their work mode status will be WFO. So for them I wanted to add 'WFO' for all the date columns only for those individuals. So I had created a variable (array type) wherein I mentioned those individuals emails like:

Now in the same loop (Add members to Excel) after checking for Holiday or not I wanted to perform a check if the current email (from the Teams action does it match with any of the email defined in the variable (WFO members) if it does then append 'WFO' else leave it blank.

So I have a expression in my mind which is like:
contains(variables('WFO_Members'), items('Add_Team_Member_to_Excel')?['email'])

But I am not sure whether it is correct and the second thing I am not sure where do I need to place it and How?.
And the other thing is if the particular column is already marked as Holiday from the previous check (that particular day like Christmas : 25th Dec)  in that case it should not add the WFO text for the set of employees defined in the variable.

Update:
So I did try to update my existing flow with the changes that I mentioned above like:
Added a initialize variable action : With 3 strings (emails)

Then in the Add Members to Excel loop (Apply to each):

Previous code:
(In Row action)
//Here I was only checking if the current week if there are any Holidays as per the Holiday List then append Holiday for that column else leave it blank
if(contains(join(body('Select'),', '),split(outputs('Header'),',')[3]),'Holiday','')

//Now in the else block I modified it slightly:
if(contains(join(body('Select'),', '),split(outputs('Header'),',')[3]),'Holiday',if(contains(variables('WFO_Members'),items('Add_Team_Member_to_Excel')?['email']),'WFO'),'')

Working:
In the outer if (first if):
Check: If the current weekday is there any Holiday:
if(contains(join(body('Select'),', '),split(outputs('Header'),',')[3]),'Holiday',

Then in its else part:
(Check if the current record/item email are they part of WFO_Members array:
if(contains(variables('WFO_Members'),items('Add_Team_Member_to_Excel')?['email']),'WFO'),
 
And if its not satisfied:
Then leave it blank:
''

The whole expression looks like:

{
  "SNo": "@{variables('varCounter')}",
  "Employee Name": "@{items('Add_Team_Member_to_Excel')?['displayName']}",
  "Employee Email": @{items('Add_Team_Member_to_Excel')?['email']},
  "@{split(outputs('Header'),',')[3]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[3]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO'),'')}",
  "@{split(outputs('Header'),',')[4]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[4]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO'),'')}",
  "@{split(outputs('Header'),',')[5]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[5]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO'),'')}",
  "@{split(outputs('Header'),',')[6]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[6]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO'),'')}",
  "@{split(outputs('Header'),',')[7]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[7]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO'),'')}"
}

So I saved my flow and tested (manually), got this issue (in the loop)

Is there anything that I missed?

So if you guys can help me, it will quite helpful.
If you need any more information do let me know.

Note:
Starting from tomorrow (16/07/2024 to 22/07/2024) probably we might not able to comment on the post as there is some transition that will be happening (from Microsoft) so in that case if comment is disabled, you can message me where we can continue the discussion.

Regards,
Sidhant.

[SudeepGhatakNZ]

@Sidhant_02 

Your syntax is correct.

contains(variables('ArrayString'),triggerBody()['text']).

I have tested it and it works.

[Sidhant_02]

Hi @SudeepGhatakNZ ,
Thanks for verifying, I have posted an updated in my post wherein I am facing one issue can you please have a look at it.

Regards,
Sidhant.

[SudeepGhatakNZ]

@Sidhant_02 try this

{
"SNo": "@{variables('varCounter')}",
"Employee Name": "@{items('Add_Team_Member_to_Excel')?['displayName']}",
"Employee Email": "@{items('Add_Team_Member_to_Excel')?['email']}",
"@{split(outputs('Header'),',')[3]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[3]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO',''))}",
"@{split(outputs('Header'),',')[4]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[4]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO',''))}",
"@{split(outputs('Header'),',')[5]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[5]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO',''))}",
"@{split(outputs('Header'),',')[6]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[6]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO',''))}",
"@{split(outputs('Header'),',')[7]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[7]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO',''))}"
}

[Sidhant_02]

Hi @SudeepGhatakNZ ,
Quick update have solved the issue that I was facing earlier related to arguments i.e. it expected 3 but got 4:

Earlier:
split(outputs('Header'),',')[3]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[3]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO'),'')

The 4th argument that was referring to was    'WFO') , ''  <- This was the 4th argument.
General syntax of If is: If (condition, True part, Else part) so earlier I was specifying the else outside the else block which is:

if(contains(join(body('Select'),', '),split(outputs('Header'),',')[3]),'Holiday',
//Else Part: (if-block)
if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),
'WFO'  #TRUE PART

            # ELSE PART (WAS MISSING)

)
,'')     #Specified the blank condition outside

Silly mistake 😅, so got to know about this after some time and then fixed this part:

Updated:
split(outputs('Header'),',')[3]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[3]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO',''))

And now when I executed the flow it was successful.

In the flow I could see the 'WFO' text was added:

But in the sheet for 16/07/2024 it was not added (for rest it was there)

Updated Expression:

{
  "SNo": "@{variables('varCounter')}",
  "Employee Name": "@{items('Add_Team_Member_to_Excel')?['displayName']}",
  "Employee Email": @{items('Add_Team_Member_to_Excel')?['email']},
  "@{split(outputs('Header'),',')[3]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[3]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO',''))}",
  "@{split(outputs('Header'),',')[4]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[4]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO',''))}",
  "@{split(outputs('Header'),',')[5]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[5]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO',''))}",
  "@{split(outputs('Header'),',')[6]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[6]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO',''))}",
  "@{split(outputs('Header'),',')[7]}": "@{if(contains(join(body('Select'),', '),split(outputs('Header'),',')[7]),'Holiday',if(contains(variables('WFOMembers'),items('Add_Team_Member_to_Excel')?['email']),'WFO',''))}"
}

Have got the solution working just need some small help regarding this issue .🙂

Regards,
Sidhant.