** HELP ** Need to create an approval based on Date

[sbruk89]

Hi,

I want to have an automation so that will cross reference the below spreadsheet and if it is in planned status and it is "todays date" for demonstration purposes lets say it was the 25th it would reference the cells that are = to 25/08/2023 and check that they are in planned status, if so I would like it to send an approval to say whether these have been completed, if so once they approve it will update the relevant cell to "Complete" if it is denied will say "Failed"

@ManishSolanki - not sure if you can help?

[v-jefferni]

Hi @sbruk89 ,

Based on you need, I have created a flow for your reference:

Best regards,

[sbruk89]

hi @v-jefferni ,

Can you kindly share the expression in the filter array please 🙂

[v-jefferni]

Hi @sbruk89 ,

I wrote it as "'utcNow('dd/MM/yyyy')'", assumed it's a Text column.

Best regards,

[sbruk89]

Hi @v-jefferni,

Thanks for the expression, I ran the flow and it said it ran successfully but I didn't not receive an approval. Upon doing some investigating I found that the date is being pulled through as number format on the flow when the "Proposed Pilot Date" is in the Date Format on excel.

[v-jefferni]

Hi @sbruk89 ,

In the List rows in a table action, you need to use ISO 8601 format instead.

Best regards,

[sbruk89]

Hi @v-jefferni 

I have updated that but the date format still is a bit off

Im based in the UK so date format is "dd-MM-yyyy"

[sbruk89]

@ManishSolanki 

Any suggestions?

[ManishSolanki]

Hi @sbruk89 

Pls try using the below expression for both left side & right side operand:

Left side

formatDateTime(item()?['Proposed Pilot Date'],'yyyy-MM-dd')

Right Side:

formatDateTime(utcNow(),'yyyy-MM-dd')

 The format should be same on both side to compare the values.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[sbruk89]

hi @ManishSolanki ,

Im getting the following error:

[sbruk89]

Hi @ManishSolanki 

Any ideas to the above?

[ManishSolanki · Accepted solution]

Hi @sbruk89 

Pls check the empty string in the left-hand operand:

if(equals(item()?['Proposed Pilot Date'],''),'',formatDateTime(item()?['Proposed Pilot Date'],'yyyy-MM-dd'))

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[sbruk89]

thanks @ManishSolanki ,

we seem to be making some progress however, im being sent requests for rows that do not meet the eligibility of State = Planned and Proposed date of "today"

as we can see from this list MRBUX is already completed so i shouldn't be receiving this email, its going through the entire list and asking for approval on all of them.

the only ones i should be receiving are the 3 below

Below is my flow:

[ManishSolanki · Accepted solution]

Hi @sbruk89 

Pls modify the flow as per below instructions:

1. Pass the body of filter array instead of value array obtained from "List rows in a table" action.

2. In the condition, use the expression for the left side operand: item()?['State']

3. Remove "Apply to each 2" action and set the values in approval process or other action with in "Apply to each" using expression in the form: item()['<excel col name>']

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[sbruk89]

hi @ManishSolanki ,

Sorry for late response, again we're making progress it is now bringing me the correct exchanges, so thank you!

Only issue i have now is getting the column "State" to update correctly from "Planned" to "Complete" last couple of times when i have clicked approved. it has changed all the rows in Column "State" to Complete rather than the individual ones via the approval

[ManishSolanki · Accepted solution]

Hi @sbruk89 

You don't need "Apply to each" action for updating the records in excel. This is the reason why all records are updated with status as "Complete". In "Update a row" action, in 'Key Column' write the below expression:

item()?['Candidate Email']

 here Candidate Email is the column in excel, pls replace with the actual column in excel file.

If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[sbruk89]

Thanks @ManishSolanki,

That seemed to help, however it says on the flow that the "State" is "Complete" as below

However, it stills shows as planned on the list.

[sbruk89]

thanks as always @ManishSolanki  😁 I now have it working.