Yes, you can definitely achieve this using Dynamics 365 (Power Apps) functionality. While calculated fields have limitations with dynamic "today's date" comparisons that update automatically every day, you have a couple of good options:
1. Using a Calculated Field (with a Clever Workaround for "Today"):
While a standard calculated field can't directly use a function that updates every day, you can leverage the NOW() function and some date manipulation to achieve a result that effectively changes based on the current date each time the record is loaded or its fields are updated.
Here's how you could structure the calculated field:
- New Field: Create a new field with the following properties:
- Display Name: e.g., "Is Before Today FY25"
- Name: e.g.,
new_isbeforetodayfy25
- Data Type: Two Options (Yes/No)
- Field Requirement: Optional
- Calculated: Yes
- Define the Calculation: Click the Edit button for the calculated field and use a formula like this (adjusting the date logic for your specific financial year):
Explanation:
[your_date_field]: Replace this with the logical name of the date field you want to compare.
DATEVALUE(): This function extracts the date portion from a date/time value, making the comparison date-only.
NOW(): This function returns the current date and time. DATEVALUE(NOW()) gives you today's date.
IF(condition, value_if_true, value_if_false): This is a standard conditional statement.
How it Updates:
- The calculated field will be evaluated and updated whenever the record is loaded or when any of its source fields (in this case,
[your_date_field]) are changed and the record is saved.
- While it doesn't update automatically every single day in the background for all records, it will reflect the correct "Yes/No" status based on the current date when a user views or interacts with the record.
Limitation of Calculated Fields for True Daily Updates:
Calculated fields are computed on-demand when a record is retrieved or modified. They don't have a built-in mechanism to trigger a recalculation for all records at a specific time each day.
2. Using a Scheduled Power Automate Flow (for True Daily Updates):
If you need the field to actually update for all relevant records every day without user interaction, then a scheduled Power Automate flow is the way to go. This is what you were likely thinking of with "business flows."
Here's a high-level outline of how you could set up a flow:
- Trigger: Set up a Recurrence trigger to run the flow once per day (e.g., at midnight).
- Action: List Records: Use the "List rows" action from the "Dataverse" connector to retrieve all the records of your entity that have the date field you're interested in. You might want to add a filter here if you only need to update a subset of records.
- Action: Update a Row (within a "For each" loop):
- Save and Test: Save your flow and test it to ensure it updates the records correctly.
Which Approach Should You Choose?
- Calculated Field: Simpler to implement and works well if you only need the "Yes/No" status to be up-to-date when a user views or interacts with a record. This is often sufficient for reporting and form logic.
- Power Automate Flow: Necessary if you absolutely need the field to be updated daily in the background for all relevant records, regardless of user interaction. This is more complex to set up but provides true daily automation.
Given your description, the calculated field approach might be sufficient as the "Yes/No" status will be accurate whenever a user looks at a record. You don't necessarily need to update every single record in the database at midnight if the logic is primarily for display or form behavior.
Let me know if you'd like detailed steps on how to create either the calculated field or the Power Automate flow!
