Using flow to calculate availability based on dates - struggling with what happens when dates expire
Apologies for the long post.
We have a custom object called Token, associated with accounts. Tokens have a type, plus a start date and an end date. When a record is created, both dates could be set to be in the future . Tokens are associated with another custom object, called Project, and there is a Roll Up Summary field on Token to count the number of Projects it is associated with (it should only be 1:1).
I need to automate a field called Availability, which should be populated with the following options:
- Pending (if the start date is in the future)
- Available (if the start date is in the past and the end date is in the future and the roll up summary for projects is 0)
- Used (if the roll up summary for projects is greater than 0)
- Expired (if the roll up summary for projects is 0 and the end date is in the past)
I also need to count the number of available tokens on the Account, based on the Type and the Availability.
We are currently managing this (excluding the start date function) with a complicated series of formula fields, (which can't be used in roll up summary fields) and workflow rules, but it doesn't work perfectly. If a token is available, it needs to automatically become Expired when the end date passes, but this doesn't currently happen, as nothing is triggering the WFR to update. Plus I need to transfer the WFRs to flow anyway.
What’s the best way to handle this? I’m sure I can create a formula field to automate the Availability, based on the Product count and the date, but how do I get this to be used in a Roll Up Summary field on the account? Could Flow populate a field that can be used in a RUS, and update it even if the date changes with no other input? Or is this an Apex only thing?
Does anyone have any ideas of non-coding ways of doing this please?
Forum Ambassador
Zack Terry (Fast Slow Motion)Hi @Sara Monksfield,
There's a lot going on here, so I would suggest working with a consultant or an architect to flesh out the full solution outside of this forum to ensure all considerations have been accounted for.
That said, you could consider using Flow to populate the rollup data instead of using standard rollup summaries, or using a tool like DLRS to calculate the rollup data. These strategies would allow you to overcome the limitation imposed by standard rollup summary fields preventing access to cross-object formula fields. As with anything, there are pros and cons and tradeoffs to consider (For example: would real-time triggers using DLRS cause other automations to fail? Can the updates be scheduled on a regular basis vs. triggered in real-time, or is immediate updating required? Do you have the resources to build out a more complex set of Flow-based rollup automations? Etc.).
To automatically update a record to 'Expired' when the end date passes, you could use a schedule-triggered Flow to identify records which have an end date in the past - a checkbox formula could be used to return True if the current date is after the expiration date value, and the Flow could be set to run daily to update matching records to "Expired". If other logic needs to be accounted for in that Flow, such as updating related records, it could be added (this approach is outlined here).
