Email Automation

[kredfearn05]

Hello,

I'm currently facing challenges with setting up an email automation that revolves around the Tracker Table and its Linked Shipments (Shipments Table). The automation is designed to trigger when the Status is "Part-Shipped," and in the Linked Shipments, both the Tracking# and Shipped Date are not blank. It functions as intended when I change the status from any other state to "Part-Shipped." However, I'm encountering difficulties in triggering the email when the status remains as "Part-Shipped." I've attempted to create an action by changing the status to "Sync" and then back to "Part-Shipped," but I'm struggling to incorporate it into a formula. Any assistance in resolving this matter would be greatly appreciated.

This is the formula that I'm working with, just need help with forcing a sync when the status stays as Part-Shipped and any new shipments are added with the specified criteria:

AND(
[Status] = "Part-Shipped",
NOT(ISBLANK([Linked Shipments])),
COUNT(
SELECT(
Shipments[ID],
AND(
NOT(ISBLANK([Tracking #])),
NOT(ISBLANK([Virtual Shipped Date]))
)
)
) > 0
)

[Suvrutt_Gurjar]

 Could you confirm if you are using that expression in a bot condition and that it is a data change bot?

If so, data change bots run on a "data change" event as the bot type name applies.  There seem to be 4 columns involved in the expression. So for the bot to execute, either of those 4 columns need to undergo a change in status.

So you may want to evaluate and elaborate what data change takes place in the expression when the status remains as "Part-Shipped."

[kredfearn05]

Hi,

Thank you for the response. I'm using that expression in a data change set, data change type as 'Adds and updates' 

If I understand your post, all 4 columns need to change in order for the email to work if the status stays as "Part-Shipped"? Do you think it would be best to set another automation that would try to trigger the email to send when the status is set to "Part-Shipped"? I've been trying to figure this out for a few days now, so can't think quite clearly right now.

[rbucsis]

The Update event only happens for the table the updated row is a part of.

So in this instance, when you change the status to "Part-Shipped" on the Tracker table, the automation does its check to see if there are any shipments that have a tracking number and shipped date. If yes, it should send the email. The issue is if you set it to part shipped, and have no Related Shipments, then the automation will not run. Adding Shipments after changing the status to "Part-Shipped" does not trigger an Update event for the Tracker table, it triggers an Add event on the Shipments table, so your automation isn't going to run.

One solution would be to switch your Tracker Table status to something else, then back to "Part-Shipped", as you described with your "Sync" option. If a Tracker record will only ever have one shipment, then you can create an automation on the Shipment table instead to trigger on adds that will send the email.

You could also create an "Updated" column on the Tracker table. Have an On Save action on the Shipment form that updates the Tracker row's Updated column, and that should in turn trigger the automation.

[kredfearn05]

Hi rbucsis,

I've been contemplating the idea of setting up another automation for the Shipment table. However, there are certain limitations to what I can do since I'm not the app owner. The owner has specific criteria for the emails, emphasizing the need for summarized shipment information. For instance, if I part-ship an order on January 31st and another on February 1st, he wants a summary encompassing both shipments. Although I haven't tested this yet, as the owner has only created an email template for the Tracker Table, I might begin creating a template for the Shipments table. This way, I can evaluate how the emails look as I add shipments, especially since ChatGPT provided me with a formula to facilitate information summarization.

I wanted to follow the "Updated" column suggestion but I'm out of my depth. I don't understand how to implement the action into the automation, and I've tried on suggestions by ChatGPT. 

[kredfearn05 · Accepted solution]

Add 3 actions to the Tracker:

Update On-Data: set the value of some columns in this row

Status="Sync"

Update Off-Data: set the value of some columns in this row

Status="Part-Shipped"

Update-Grouped: execute a sequence of actions

Update On and Update Off

Added 1 action to the Shipments:

Test-For a record of this table-Shipments-Data: execute an action on a set of rows

Referenced Table: Tracker

Referenced Rows: FILTER(
"Tracker",
AND(
[Status] = "Part-Shipped",
[Sales Order] = [_THISROW].[Sales Order]
)
)

Referenced Action: Update

Behavior: AND(
ISNOTBLANK([Tracking #]),
ISNOTBLANK([Shipped Date]),
[Status] = "Part-Shipped"
)

Then went to the views and added the action to the Shipment Form--Behavior-Event Actions