Help building a flow using Today's Date and other Date field

[Shap1]

Hello- Can anyone figure out how to build a part of my Recurrence flow?

The need is if a Completed Date field is entered on the candidate's program record and the Actual Start Date field on an assignment record has been 1 year from Actual Start Date field then it will update the Status Reason field on a different entity to Earned.

So if the today's date is equal to or greater than 1 year from the Actual Start Date, if this condition is true, then update Status Reason to Earned.

I have tried on my own but it has not been working. Is there another easier way than what I'm doing?

[abm]

Hi @Shap1 

Use the dateDifference() expression to get the number of days.

format both using yyyy-MM-dd

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

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

Pass the above values in dateDifference() expression. Check if its more than 365 or not?

Thanks

[Shap1]

Thanks for this @abm. 

I am trying what you said but don't think I have it set up correctly:

[abm]

Hi @Shap1 

Hi

You don't need the initialise variable

Inside the Loop before the IF condition add two compose action steps.

Then use the formatDateTime() expression which I explained earlier.

Then add another compose for using the dateDifference() expression passing both compose outputs (todays date and startdate) above.

Finally the third compose output you can check IF it is greater than 365 or not?

Thanks

[Shap1]

Now I may have two issues.

First being the Compose Start Date. Saying the value provided for date time string 'hc_startdate' was not valid:

The other issues is that something must not be right with my List rows of Assignments filter rows:

'_hc_candidate_value eq @{items('Apply_to_each')?['_hc_candidateid_value']} and statecode eq 0'

because the outputs don't appear to any data. Therefore not able to get the start date.

I'm just trying to get the Start Date for each candidate. I need this because it'll give me the correct Start Date:

[abm]

Hi @Shap1 

This is not quite right.

hc_startdate you need to map as dynamic.  Here you typed as plain text. Once you typed the formatDatetime() place the cursort inside then see the above menu Dynamic content. See below. From here you can select the startdate.

Another issue is you shown me a compose action called Compose start date but in run history  it is showing Compose 2 Start Date. I was expecting this should be the same name as above. Are you sure you posted the right screenshots?

[grantjenkins]

Hopefully this is what you're looking for. Note that you'll need to change the field names to match what you have in your table.

For this example, I'm using the following Dataverse Table called Assignments. Note that Albert doesn't have a Completion Date filled in, and Jane's Start Date isn't one year ago, so the only record that should be updated in Grant.

See full flow below. I'll go into each of the actions.

Recurrence is set to run daily.

List rows retrieves the rows where the Completion Date is filled in, and the Start Date is equal to the Date 365 days ago. Because this will only return the rows we need to update, we don't need a condition within our Apply to each.

//You'll need to change the field names to match what you have

cra85_completiondate ne null and cra85_startdate ge '@{startOfDay(addDays(utcNow(), -365))}' and cra85_startdate lt '@{startOfDay(addDays(utcNow(), -364))}'

Apply to each iterates over each of the rows returned from List rows and uses Update a row to update the Status Reason to Earned.

After running the flow, we should see the following:

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[Shap1]

Thank you for this!

However, getting some errors:

Also want to point out the Completed Date is on the Program entity. The Start Date is on the Assignment entity. So it's important each record update matches to the correct Candidate with their Completed Date and Start Date.

Then we have a third entity (Bonus Payments) that is the actual record that needs to be updated and connected to the right candidate:

[abm · Accepted solution]

Hi @Shap1 

Thanks for the screenshare. So what we did is find the difference between start date and todays date. Additional business rule for number of years and other filters. Then update the status of bonus payment against that candidate. Ideally you missing a relationship between Bonus Payment and Assignment. Otherwise you can only have one bonus payment. Hope it make sense.

Thanks

[Shap1]

Thank you @abm! This is now working correctly.

Here's what I ended up with:

Then based of what I get above, I can condition off of it to see if the days are greater than 2 years for example:

You are always so clutch @abm, always coming through giving me the perfect solution