Workflow Rule: Email Notification pdf template

[Sarah_Keown]

Hi all, trying to put together some pdf templates for an email notification workflow rule.

I keep getting errors in my expression, as I am trying to mimick an example of this sort of scenario from the help section “Reports” so the template should look something like this;

Each Order is showing the related order details in the table and this repeats for each Order, the back end of this is the following;

I am attempting to recreate this for my app but with small changes such as the Supplier Name being the object for sorting the data i.e. each Order shows the related Order Details for that supplier. My expressions keep causing errors in my app sheet editor but I am not sure of the issue. My own template looks something like this;

Note: the ‘Total Ordered’ column value is missing because the way I created this value in the app is by concatenating the ‘Product’ and ‘Order No’ columns and grouping the inline data with that virtual column. The Total Ordered value is then tallied using the group aggregate sum function. I am now realising that there is no way to insert that into the report at present, is there anyway around this?

[Sarah_Keown]

This is the template as of right now;

[Phil]

Hi Sarah,

Let’s start by fixing the most obvious problem.

You need an << End>> tag right after << [Qty Ordered]>> at the end of the table row containing the << Start>> expression.

When using a table, the leftmost cell in the row should normally contain the << Start>> and the rightmost cell in the row should normally contain the << End>>. This signifies that you want to repeat the entire row (everything between the << Start>> and << End>>) for each record returned by the Select expression contained in the << Start>> expression.

Remove the extra << End>> expression at the bottom of the template. There should only be one << End>> expression at the bottom of the template. It is there to match the << Start>> expression in row five of your template.

Once that is sorted, you may have other errors, but you should be closer to having a working template.

[Aleksi]

I checked your app and Start expression should be like…
<<Start: SELECT([Related Order Details][Line Reference],TRUE)>> and then this <> right after the <<[Qty Ordered]>> as Phil suggested…

Your Total Ordered tag <<[Repeat Item Quantity]>> won’t work as it is now because that value is coming from the Order Details table, not from the Order table. I would make that calculation in the Orders table and it would work. You can sum the total quantity like SUM([Related Order Details][Qty Ordered]) in a virtual column. Or you can use the existing virtual column [Supplied Qty] if that’s the correct value.

[Sarah_Keown]

What about the expression with regards to the Orders table?

Thank you for helping out and taking a look at the app for me

[Sarah_Keown]

Hi @Aleksi just a note about this issue and to provide some context to what I am trying to do I made a mock template of what I hope the PDF will look like.

The ‘Total Ordered’ table section was meant to be a sum of the Qty Ordered column by Product; so where the concatenation of Order No and Product appear sum the Qty Ordered column. I am doing this within the app in the Order Details table, but obviously since I am pulling data from the Orders table in the template I cannot get access to that virtual column.

I created a VC to concatenate the Order No and Product, grouping the inline related Order Details by this column and using a group aggregate sum I can see the Total Ordered for each product (not the entire order). It looks something like this;

The 122 and 92 is the Total Sum

This is what I hoped the template would resemble, but cannot get working due to the two separate tables (Orders and Order Details);

I made another VC in the Order Details table called [Repeat Item Qty] in the hopes I could pull it into the template but that is no longer possible, the expression held in this column is as shown;

I dont have the required columns to put this expression into the Orders table, and the VC is not accessible through ‘Related Order Details’, I’m wondering if there are other ways around this issue?

Thanks

[Aleksi]

I would propose to send the original email from the Orders table. Then the first Start & End would not be needed at all. You can trigger the order with an action button with the same kind of action that you are triggering already. The only difference is that you would need to write a condition rule like AND(ISNOTBLANK([EmailCreated]),[_THISROW_BEFORE].[EmailCreated]<>[_THISROW_AFTER].[EmailCreated]). That will take care that it will send the email only, when you click that action button (it writes NOW() DateTime value). Then you can use UPDATES_ONLY for your Workflow.

[Aleksi]

You are looking for a grouped table structure and I’m afraid we don’t have that functionality yet. If the order would contain the same amount of groups, that could be doable, but because you don’t know them in advance and you could have lot of different groups, it would be very difficult job to do.

[Sarah_Keown]

Thank you for the response, is this type of functionality something AppSheet will be capable of in future?

[Phil]

Hi Sarah,

We have talked about adding a “Group By” feature for Workflow and Reports but there are no firm plans to do so. Admittedly it would be very useful.