Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 29, 2024 08:18 PM - edited Sep 30, 2024 01:59 AM
Hi all
I want to send out weekly email notifications to all my customers which shows only the records (orders) which relates to them (i.e. I dont want to show one customer someone elses orders and ) and where status of the order is NOT complete and ETD (date or formula) is on or before the last day of the current month.
The records (orders) are found in my order line items table with the information (quantity dispatches, quantity remaining, ETD, etc)
I have a 'look up' customer email address field in this table so I would think this is how this could work? Note that my customers and suppliers both sit in the same table called 'Locations'
I also want this to be in one automation (i.e. I dont want to create an automation per customer) so is there a way to send out to all customers?
Any suggestions @TheTimeSavingCo @kuovonne ?
Thank you!
Sep 29, 2024 09:23 PM
This is a common scenario. In general ...
- Use a "at scheduled time" trigger to get the automation to run once a week.
- Have a "find records" action that looks for records in the [Customers] table. Use a conditional rollup field to determine if a customer has any open orders that meet your requirements. (See my post on your other thread about the dates.)
- Use a repeating action group for each customer found.
- Within the repeating action group, do another find records to find the orders to include. Then have the send email action.
Sep 29, 2024 09:30 PM
Thanks for the quick reply.
Could you please help with the repeating action part for each customer? I dont follow this sorry...
Sep 30, 2024 03:20 AM
Hello,
Here are some screen captures to illustrate Kuovonne's solution that works perfectly.
Table client
Add a field "Weekly orders":
This field is a formula to count the number of orders that match "reporting conditions":
Automation
Trigger in this example is not "at scheduled time", but it does not change the logic of the automation
We start with a "Find records" action on table client to get the list of clients for whom there is something to report:
We then add a repeating group action set on the input list from previous "find records" action:
We add a new "Find records" action to find "opened orders" for the current client:
Where the client_id is identified with the repeating group's current item:
Finally, we add the "Send an email" action:
"Mail To" comes from the current item:
Mail's content is a grid from the second "Find records" action:
Here is what the mails looks like for client A:
Regards,
Pascal