Deceased Notepad Dates- Query API

Carol Grant

Hello all- I'm trying to automate a deceased list. My records department came up with an idea to create a notepad that has the Date Mark Deceased in it. People may tell us someone passed 2 years ago but we just found out about it so we want to alert people that they have passed.

Notepad dates are fuzzy dates which are not easy to query from. I was hoping to see a Equal To Last Month but I don't get that option. I am changing these dates manually each month. It has last month (every year). I guess I could use that then limit it in Power Automate?

Does anyone know of a way I can query on this notepad date to make it automatic? I haven't looked into creating a custom API filter yet. Unfortunately, this is the only way to limit the “recently” marked deceased.

Thanks,

Carol

Alex Wong

@Carol Grant
Any reason why using a notepad for this? If all you are recording is a date of when your org knows about the deceased person, then an attribute/custom field can do that, and it is not fuzzy date.

However, though it is fuzzy date, I'm going to assume your org isn't putting fuzzy date, meaning you are actually inputting the full date of when your org knows about the deceased. Then you can treat the fuzzy date like a real date b/c you have all component of a date: month, day, year.

The next “issue” is getting the info you want, since there is no way to get all note of a certain note type nor filter on the fuzzy date field, you will need to get all note of all constituent and then filter array in flow (in order to deal with all dates situation: Jan/Dec of previous year, see below). If you have a lot of note in your RE, this is not a good path as it will take too long. however, if you use attribute/custom field, you can get constituent attribute/custom field by category, which is going to be a lot less records, then use filter array action.

However, if you MUST use constituent note, one other way that may limit the records to process is using Query API. I don't know if there is limitation on notepad info using Query API, so you will have to test that, but assuming there is no limitation, you can build a query that first filter on the note type, download as JSON, then filter array on the date. (I don't think you can filter date in querying directly, but again, you can try).

if you are using fuzzy date for filter array, you can use the parseDateTime expression:

parseDateTime(concat(item()?['date/m'], '/', item()?['date/d'], '/', item()?['date/y']), 'en-US', 'd')

the concat will create a M/d/yyyy to parse as a date string, then use subtractFromTime on utcNow(), convertFromUtc (to get the utc to your own timezone) and do the compare of the date in filter array.

Carol Grant

@Alex Wong- thanks Alex. They do put an actual date in the notepad date but when filtering on them in Query it says it's a “fuzzy date”. I like your idea of pulling in all of the notepads then parsing the date to see if it has been the last 30 days. That may work!

Alex Wong

@Carol Grant
if you have more than 5000 notes in all constituents, rather than do/until loop to get all notes, you can consider using Query API to get note that is already filtered by type.

OR, maybe you can use can do the all constituent note API with “date created” parameter first, it really depends on your processes.

Carol Grant

@Alex Wong- the plan was to use the Query API and have that get the specific notepad then filter by date in the Flow. I don't think we have more than 5,000 of those notepads.