I have a CDS entity with the fields Person and LoginDate. The entity keeps a history of all logins, so there are multiple Emmas with different dates, and multiple Johns with different dates, and so on. I need a weekly email with a list of people, no duplicates of course, who haven't logged in in the last 6 months.
It sounds easy and straightforward, but it's starting to seem like Flow doesn't support this by itself and I would need to employ a workaround, like creating another entity.
Ideally I want to be able to use groupby and Max(Date) per group, but Max(Date) doesn't exist in Flow and I can't use groupby without aggregate. So my 2nd option Flow I'm trying to achieve is:
- Filter records for Date within the last 6 months
- From the filtered records, union Person into array "varUniqueArray1" with no duplicates (unique persons who have logged in in 6 mths)
- List records again, filter for Person not found in varUniqueArray1
- From the filtered records, union Person into array "varUniqueArray2" with no duplicates (unique persons who haven't logged in in 6 mths)
Unfortunately the flow fails at #3. Is the kind of filter I'm hoping to do even possible?
My third option is to tackle the task at the point of record creation (of the CDS entity). But first, I'll create a new entity with the fields Person and LatestLoginDate. Whenever the original entity (the history) adds a new record (record creation), I'll lookup the Person in my second entity, if it exists there I'll overwrite the LatestLoginDate, if it doesn't exist I'll add a new record. Then I'll use flow to filter that second entity for records with LatestLoginDate older than 6 months, and then send that email.
Can someone confirm please if Flow is unable to accommodate both my 1st option (use groupby and max(date)) and 2nd option (use process of elimination)?
Edited to add:
I simplified my scenario for ease of story telling. My Person field is actually a calculated field that concatenates 3 different fields in the entity because it takes 3 fields to make up a unique identifier. The LoginDate field is also not a login but a different date context.
