Unanswered

PowerAutomate to send payment reminders from dates and customer details host on a SP list or Excel file

(0) Share

Report

Posted on by KeithQ

Hi, I have been trying to build a PowerAutomate flow that will send personalized email reminders 14 days before a deposit payment is due to about 300 customers. Each customer has multiple payments different due dates that stretch for the next 4 years or so.

I have an Excel spreadsheet, (I can also use a SharePoint) list with rows for for customer name, email, deposit amount due, and the applicable deposit dates in columns. (See below example).

The challenge I have is with a schedule flow, I only seem able to send a reminder based on a specific date, meaning I would require a "reminder date"column for each payment date on the list. As the dates are all over the place, I would need add a lot of extra columns.

Does anyone have a solution where I send and email to each customer prior to their specific due date, inserting their name, (and hopefully due date and amount due), in the body of the email?

Thansk

KeithQ

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (2)

uwenagel 382 on at

Like (1)

Report

The problem lies in the structure of your data. Why do you use one column per date? You could/should condense each of the date columns into one column that then could be later used for a query that triggers the email sending.

So the table should have the following columns:

Lot
PurchaserName
Email
DueDate
Amount

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item.
If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
KeithQ 9 on at

Like (0)

Report

HI @uwenagel Thank you for suggesting. I should have mentioned that the data is based on a report that is updated from an ERP system to track the upcoming payments in a calendar based report. If I am able to export the data in the format as you suggested, I assume that would mean that there would multiple rows for each purchaser depending on the number of deposits with varying due dates. So what would the query/formula be on that dynamic date column?
thanks
KeithQ

Was this reply helpful? Yes No
Verified answer

uwenagel 382 on at

Like (1)

Report

Hi @KeithQ
You could get all the payments that are due within the next 14 days by filtering the table the following way:

ClearCollect(colPaymentsDue,Filter(Table1,DueDate<Now() +14))

You could then use this collection colPaymentsDue to send the emails as wished.

Hope this helps.

Best regards
Uwe

Was this reply helpful? Yes No
Verified answer

uwenagel 382 on at

Like (1)

Report

If you use PowerAutomate you would first have to list all the elements from the table (in SharePoint) and then add a condition statement: DueDate < addDays(utcNow(),14). In the true branch you then will have a 'Send Email' action that sends the mails as wished.

Was this reply helpful? Yes No