Reminders based on 2 diff columns

(0) Share

Report

Posted on by TORFS_BE

I've tried to make some changes to a flow I have but I cant figure it out to make it work properly.

What it basically does is send out reminders for contracts when a specific date happens.

Now, we still want reminders but based on 2 columns.

So, it all works based on a sharepoint list;

Documents will be uploaded in this list and be

few important columns;

"Startdatum" -> Start date of contract

"Einde Triennaat" -> First reminder alert. Here they want a reminder 7 and 8 months before this date occurs

"Einde contract" -> Second reminder alert.Here they want a reminder 16 and 18 months before this date occurs

"ExtraEmail" -> Column they can insert an extra email address for the reminder

What I have now (before we made these column changes on top)

The current magic happens on the "Filter Array" (again, this is outdated based on the columns we had and want now)

In my Filter Array now;

@Or(
equals(addDays(item()?['Vervaldatum'], mul(int(string(coalesce(item()?['RemindMeXDaysBeforeExpire'], 9999))), -1), 'yyyy-MM-dd'), body('Convert_time_zone')),
equals(addDays(item()?['Vervaldatum'], -7, 'yyyy-MM-dd'), body('Convert_time_zone')),
equals(addDays(item()?['Vervaldatum'], -30, 'yyyy-MM-dd'), body('Convert_time_zone')),
equals(addDays(item()?['Vervaldatum'], -60, 'yyyy-MM-dd'), body('Convert_time_zone')),
equals(addDays(item()?['Vervaldatum'], -90, 'yyyy-MM-dd'), body('Convert_time_zone'))
)

Someone tried to help me before but I cant figure out what to exactly place where;

If this is useable, please guide me;

Replace the Filter Array by the following;

lessOrEquals(addDays(body('Convert_time_zone'), -210, 'yyyy-MM-dd'),'Einde Triennaat')

greaterOrEquals(addDays(body('Convert_time_zone'), -240, 'yyyy-MM-dd'),'Einde Triennaat')

lessOrEquals(addDays(body('Convert_time_zone'), -210, 'yyyy-MM-dd'),'Einde contract')

greaterOrEquals(addDays(body('Convert_time_zone'), -240, 'yyyy-MM-dd'),'Einde contract')

I'm not sure on what to place on the marked fields on top.

Also, the problem here was that it was spitting out reminder for documents that were not relevant and their date was much older.

I was getting spammed daily with multiple reminder about certain files on the list.

Thank you!!!

I have the same question (0)

All responses (6)

Answers (1)

CodrutFt 46 on at

Like (0)

Report
Re: Reminders based on 2 diff columns

Hei,

Hopefully, i understood your process usecase.
I would try something like this:
Split the flow in 2 flows, first reminder flow and second reminder flow. Argument here, it is better for operations and monitoring.
This is an example for the first flow :
set a daily run of the flows.
you add a parallel branch
For the left side Use Get Items and filter "Where Einde Triennaat -7 months = utcNow()" you can obtain that like this
"Einde_Triennaat' eq addToTime(utcNow(),+7,'Month','yyyy-MM-dd')
all items where the "Einde Triennaat" is 7 months in the future will be retrieved.
then, for each item you send a reminder.
For the right side use also Get items with the filter:
"Einde_Triennaat' eq addToTime(utcNow(),+8,'Month','yyyy-MM-dd')
all items where the "Einde Triennaat" date is 8 months in the future will be retrieved.
then, for each item you send a reminder.

Do this for the second flow with 16 months and 18 months.

It is possible that i didn't get your process right and it's more complicated than that. Let me know.

Was this reply helpful? Yes No
TORFS_BE on at

Like (0)

Report

Re: Reminders based on 2 diff columns

Hi @CodrutFt

First of all, thank you for replying and trying to find a solution here.
I like the idea to split them up as it seems to be complicated to merge both things into one flow.

However I tried looking into what you suggested but my first problem i'm facing early is that my items are stored in a folder and not in a list.
I'm not able to pick "Get Items" as I need to use "Get Files (properties only" (I think?)

I consider myself on a beginner level here so any detailed guide is welcome 🙂
Would your suggestion still work in a way with the library I have or does it only work with lists?

Thank you!
Best regards

Was this reply helpful? Yes No
CodrutFt 46 on at

Like (0)

Report

Re: Reminders based on 2 diff columns

Hi @TORFS_BE ,

Should work the same with Get files. Let me show you a screenshot of what i mean.
where what you see in purple is the following code :
addToTime(utcNow(),+7,'Month','yyyy-MM-dd')

This should be it. left side +7, right side +8 months. Also, my trigger is manually but you can set a scheduled one for daily run.
But remember. This flow is only for the column Einde Triennaat.
You need another flow for the Einde contract.

Was this reply helpful? Yes No
TORFS_BE on at

Like (0)

Report

Re: Reminders based on 2 diff columns

Hi @CodrutFt

Thanks for the detailed reply above here.
I tried to create the one you mentioned, however when testing I get an error message.

By the things you suggested does this mean in the query he will be searching for Einde Triennaat within my library?

I have to say my setup is like this;

1. I have one library <documents>
2. Within this library I have a few folders, one of them is named "Huur" (because we want other departments to be able to use this)
3. Inside my folder "Huur" I have the columns -> Einde Triennaat and Einde Contract (both date/time columns)

So, am I doing something wrong here or does your setup not work with how my library/folders/documents are set up like that?
Am I correc to say that this Flow basically checks every day if one item within the column Eind Triennaat has surpassed the date of 7 months (which is shown in the Query).
And if a date does not match, he won't act ?
But, I run this flow daily... will it spam me daily as long the date isn't changed? And if more than two files happen to have surpassed the date, I'll get two emails or one emails with a summary?

Thanks you
Best regards

Was this reply helpful? Yes No
Verified answer

CodrutFt 46 on at

Like (0)

Report
Re: Reminders based on 2 diff columns

Hi @TORFS_BE ,

First, the expression might be wrong because your column is not called Einde Treinnaat in the background. to find the exact name, go to library setting, more library setting, go to you column and check the URL of the browser. There you can see exactly the name.
On the same level regarding the error.
Please Use Get files first without filter.
Check the output of the Get Files
Check for the column with the same name you found in URL and check the returned format of the date. Maybe the format yyyy-MM-dd is not good for you in this case and cannot compare.

Second : your get files let's you pick up the folder. just click on the folder icon on the right and choose "Huur".

Third: Flow is checking every day if it should send a reminder. But it's sending the reminder only 7 months before that date. So if your date is 01.08.2025 the flow will send only one reminder for that document on 01.01.2025. If that day passed, there is no spamming. The flow will simply ignore the item, "tomorrow".
Also the flow will send an e-mail for every item, not making a summary.

This goes for 8 months , 16 and 18 months flows also.

Was this reply helpful? Yes No
TORFS_BE on at

Like (0)

Report

Re: Reminders based on 2 diff columns

Hi @CodrutFt

Perfect!!
I managed to get it working properly this time.
The column name was indeed not correct in the query.
You helped me big time here. The setup is more simple and has more posibilities for changes in the future.

About the date format... correct it would be better for us to have it "dd-mm-yyyy" instead of "yyyy-MM-dd" but i've played with that before and as soon as I change something it stops spitting mails.

Thank you!!
Best regards
Brian

Was this reply helpful? Yes No