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?

[Aleksi]

Your Start formula seems to be little weird. Are you looking syntax like…
<<Start: ORDERBY(SELECT(Orders[Order No],AND([ETA]=TODAY()+1,[Order_Status]=“Ordered”)),[Supplier])>>

[Sarah_Keown]

Thanks for the response. I have tried different syntax’s’ but everything I seem to find to solve the errors does’nt work. The columns cant be found according to the error messages. Below are some of the errors coming back from the syntax you provided;

[Sarah_Keown]

Sorry, the image is not too clear. Think what is confusing me most is the fact I explicitly say the table name the columns are part of?

[Aleksi]

Would you please take a printscreen from your template again.

[Sarah_Keown]

[Aleksi]

Is the [Order No] a key column?

[Sarah_Keown]

Yes

[Aleksi]

Every column names are typed exactly the same?

[Sarah_Keown]

Yes I’m pretty sure, I have checked multiple times and the chances of ALL columns input having a typo are slim to none. Do you think this is some sort of bug?

[Aleksi]

From which table do you trigger this workflow?

[Sarah_Keown]

It’s triggered by a separate table called ‘Deliveries Notification’ this table stores the current date and time so once the action button is clicked the date will change to the current date + time, which triggers the email notification to send. I have done this kind of thing before and the templates weren’t much of an issue but I was only pulling information from the Order Details Tbl ,not two tables.

The reason I didnt use the Orders table directly is because there are adds/updates/changes to it too often, so I had concerns that the workflow rule would not trigger at the right time. Additionally, the app acquires the same functionality elsewhere so I though it bets to be consistent.

[Aleksi]

Would you please share that template to aleksi@appsheet.com so I can check it, thanks.

[Sarah_Keown]

The app I am building is part of my job role, sharing company data outside the organisation via is not necessarily allowed. Sorry! My manager is not with me today, getting permission at the minute is not possible I’m afraid.

[Aleksi]

Did that last printscreen contain all the data from your template?

[WillowMobileSys]

Sorry to jump into the middle of this. I notice that the first error is complaining of an unmatched START. This needs corrected first as it may be causing all of the other issues.

I see the first END statement, inside the table, has square brackets [ , ] around it. I think you need to remove those.

Also, I compared to similar templates I created. I placed the END statements outside of the table. In your case, if the above doesn’t resolve the error, then I would suggest adding a blank line just before the “Total Ordered” table row and try moving the END statement there - outside of the table.

For clarity, I will try changing my template to put the END inside the table and see if I start getting errors and report back here.

[WillowMobileSys]

For my template, I moved an END statement into the table and received the error “Expression ‘End’ is invalid due to: Expression refers to undefined field”

Therefore it looks like you CANNOT have the END statement that corresponds to the START, inside of the table. You must move it to outside the table. In your case, I am assuming that the “Total Ordered” table row should NOT be included with the second START, so the END statement must be placed before that. This means you have to insert a blank line BEFORE the “Total Ordered” table row, in other words you need to separate that table into TWO tables each with a single row and a blank line in between. Then move the END statement to that blank line.

I hope I have explained clearly enough. If not, let me know.

[Sarah_Keown]

Thank you very much, I will give that a go

[Sarah_Keown]

I tried this soltuion, it worked to a point. A lot of errors have been removed but I think the main issue is the syntax of the “ORDERBY(SELECT(Orders[Supplier],AND([ETA]=TODAY()+1,[Order_Status]=“Ordered”,[Mech/Elec]=”Mechanical”)),[Supplier])”

The errors keeps coming up that this should generate a list of ref values, which should come from the key column of the referenced table

[Steve]

Instead of ORDERBY(SELECT(Orders[Supplier], ...)), use ORDERBY(FILTER("Orders", ...)).

[Sarah_Keown]

The last print screen contains everything I have in the template as of right now, besides the End statement in the table containing [ , ] brackets.

[Aleksi]

@WillowMobileSystems Good catch. The End should be like << End >>. Though it’s in a correct place inside of the table.

[WillowMobileSys]

@Aleksi I tried moving End inside of the table in a known working template of mine and received errors because it was being treated like a field value. Is there certain syntax required to do it this way? I can see the benefit in not requiring break in the table(s).

[Aleksi]

If the [Order No] is a key column in Orders table, you should use that one, not the [Supplier] column.

[WillowMobileSys]

Just to expand for future knowledge because it took me a while to come to grips with SELECT() …

The reason the SELECT is not currently working for you is because you have Orders[Supplier] specified in the SELECT statement. This essentially returns a list of Supplier values. Since these are not Ref type values, the system cannot reference back into the row to get the other values your template is trying to use. (The way i like to think of it).

@Aleksi points out, what you really need here is SELECT(OrdersOrder No], …). Order No is the key value, it is a Ref value and CAN be used to query back into the row. This is why he was asking if Order No was the key.

As @Steve points out, FILTER is equivalent and will give you the same thing. Maybe a bit easier to understand.

[Steve]

WillowMobileSystems:

FILTER is equivalent and will give you the same thing. Maybe a bit easier to understand.

The great thing about FILTER() is I can recommend it without knowing what the key column is. Zero confusion.

[Sarah_Keown]

Sorry @WillowMobileSystems that quote isnt correct the select statement is actually

ORDERBY(SELECT(Orders[Order No],AND([ETA]=TODAY()+1,[Order_Status]=“Ordered”,[Mech/Elec]=”Mechanical”)),[Supplier])”

The [Supplier] column is a ref value in the Orders table and Order No is the key in the Orders Tbl

[Aleksi]

@WillowMobileSystems When you use the Start & End combination and you want to repeat records from a table, you should have it in the last column and 2nd row if the first row is a header row.

[Sarah_Keown]

I had that originally though, the start was outside and the End tag was in the last column of the second row?

[WillowMobileSys]

Did you try the End in that location without the square brackets?

Also, in my template I actually have another row below to show comments/notes. So maybe that’s the diff for me??

[Sarah_Keown]

Yes I did try that, does the ORDERBY/SELECT statement seem correct to you?

[WillowMobileSys]

@Aleksi is more the whiz with these more complicated issues. The syntax looks good to me. Could you post the new set of errors you are seeing?

Also, I might suggest trying @Steve’s suggestion of using FILTER. Simply replace SELECT(Orders[Order No] with FILTER(“Orders” and see if it makes a difference.

[Sarah_Keown]

I did try the FILTER syntax, still received errors but I think some of them were a cloud server error too.

I hope this is the reason things have not been working for me today, with the server being busy in work its maybe causing problems.

[Sarah_Keown]

These errors have been appearing throughout the day somehow, only when I disable the workflow rule does it stop

[Steve]

I suggest contacting support@appshet.com directly for the cloud errors. Those errors have nothing to do with your expressions or the template content.

[Aleksi]

Try to use just <<Start: SELECT(Orders[Order No],TRUE)>>

[Sarah_Keown]

Aleksi:

<<Start: SELECT(Orders[Order No],TRUE)>>

@Aleksi I tried this expression in the workflow and still managed to get the same errors regarding missing columns and generting a list of ref values from the key column etc.

[Steve]

This one isn’t the problem:

<<Start: SELECT(Orders[Order No],TRUE)>>

This one is:

<<Start: SELECT([Order Details])>>

That’s not a valid SELECT() expression.

[Aleksi]

What is your account ID and app name if I check it. That’s probably the easiest way to solve this issue.

[Sarah_Keown]

Account ID: 769246
App Name: GMV Ph6 Orders