cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jsteele303
New Member

compare year of date with string

In Dynamics 365 I have a "financial target" entity. It allows me to create a target for income against a person for a set year. i.e. against John Smith, create an income target of £10,000 for FY2020 and £12,000 for FY2021 (Financial years match calendar years). The fields are contact (lookup), financial year (option set), target (currency) and current total (currency).

 

I am then using a flow to populate the current total by looking for all income records associated with the same contact and dated in the relevant financial year. 

 

To do this I am looking for all financial target records. Then, in an apply to all stage I am looking for all income records where the donor is the contact from the financial target record and where the income date has the same year as the financial year from the target. That is where I am having an issue. The financial year is expressed as "FY2020" and income date is a completed datetime field. I seem to be struggling with it. I know I need to use an expression but I struggle with using an expression in an odata filter. I need to match on the contact from the current item (i.e. the financial target record we are looking at) AND on all income records where the year of the income date matches the year from FY2020. This would then allow me to create another apply to all step where I can then go through all these income records to Sum them before moving onto the next financial target record and repeating.

 

Is this the best approach to do this? How can I list records comparing the year from a datetime to a string?

1 ACCEPTED SOLUTION

Accepted Solutions
v-litu-msft
Community Support
Community Support

Hi @jsteele303,

 

You could use the Filter array and FormatDateTime() function to filter out the date which year equals to FY, I create an entity to explain it.

You could input the array into the Filter array action, on the left side is the string "FY2020", we need use replace() function to remove FY; on the right side is FormatDateTime() function to convert the Date string into "yyyy":

屏幕截图 2020-09-18 181853.png

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-litu-msft
Community Support
Community Support

Hi @jsteele303,

 

You could use the Filter array and FormatDateTime() function to filter out the date which year equals to FY, I create an entity to explain it.

You could input the array into the Filter array action, on the left side is the string "FY2020", we need use replace() function to remove FY; on the right side is FormatDateTime() function to convert the Date string into "yyyy":

屏幕截图 2020-09-18 181853.png

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Users online (82,846)