Related Items not showing in email or attachments, can you help?

[BriW307]

Hello, 

My workorders app, similar to the Order Captures App template isn't populating the Related Orders in the reports/emails. I am using the default template. I added a section to see if removing the data from the tables would help and it is the same. I've compared the structure with other templates and I can't identify where it's going wrong. Any suggestions?

Template:

https://docs.google.com/document/d/1GboMy8hyWfvhPICwXhaPBKdEJ72eMcrPwdtjnqVEXIE/edit?usp=sharinghttp...

[James_Burns]

If my understanding is correct, your start expression needs to refer to the Contracts table (presuming one exists). So something like <<Start:[Contracts]>><<[Project_ID].[Project_ID]>> or <<Start: SELECT(Contracts[Contract_ID],[_THISROW].[Project_ID]=[Project_ID])>><<[Project_ID]>> may work.

See here for more on Start Expressions: https://help.appsheet.com/en/articles/961746-template-start-expressions

[BriW307]

This resulted in errors: 

The Order Capture template is the same as the default template, here's the link

Run task "Send an email"

• Error 1 : 'Handle new Workorders record 2' task 'Send an email' Attachment template. Expression '[Contract]' is invalid due to: Unable to find column 'Contract', did you mean 'Customer'?.
• Error 2 : 'Handle new Workorders record 2' task 'Send an email' Attachment template. Start expression '[Contract]' should generate a List of Ref values. Please verify that it generates a List and that the contents of the List are Ref values. Ref values should come from the 'Key' column of the referenced table.
• Error 3 : 'Handle new Workorders record 2' task 'Send an email' Body template. Expression '[Contract]' is invalid due to: Unable to find column 'Contract', did you mean 'Customer'?

These are the columns in the Contract Table:

Workorder_ID

Contract_ID

Project_ID

Unit

Operator

Rate

Hours

Total

Description

[James_Burns]

What is the key column for your Contracts table?

[BriW307]

Contract_ID

[James_Burns]

Did you try: <<Start: SELECT(Contracts[Contract_ID],[_THISROW].[Project_ID]=[Project_ID])>><<[Project_ID]>> ?

Perhaps this: <<Start:[Related Contracts]>><<[Contract_ID].[Project_ID]>>

[BriW307]

The select statement pulls data but I think the _THISROW reference is off. 

The 2nd suggestion didn't pull anything

Here is a copy of my Sheet that I'm building off, yellow is key, blue referenced keys

of: https://docs.google.com/spreadsheets/d/1ZTi2zJoefBpSiyoTt_4Ef_r7h_COW192QpObhdXceYg/edit?usp=sharing

[BriW307]

I think there are a couple of ways to pull the data but I'm concerned that something is off if the default template isn't working with the Related Tables

[James_Burns]

Can you describe how the _THISROW reference is off?

Try: <<Start: ORDERBY(SELECT(Contract[Contract_ID],[_THISROW].[Workorder_ID]=[Workorder_ID]),[Contract_ID],FALSE)>>

<<[Project_ID]>>

[BriW307]

I replaced Project_ID with Workorder_ID from the initial suggestion , it was pulling everything from the project instead of the parent workorder.

none of it is pulling anything at the moment, see the screenshot (with all suggestions)

[James_Burns]

Also: 

<<Start:[Related Contracts]>>

<<[Workorder_ID].[Project_ID]>>

 <<End>>

[BriW307]

nothing... I don't know a lot about <<Start>> and <<End>>, is it ok to have multiple ones on the form?

[Hamlet]

Yep. You can even have them nested.

[Hamlet]

Quick question,

Do you have the [Related Contracts] virtual column showing up in your Workorders Columns section in the editor?

[BriW307]

I do, here is the screenshot of the column view for workorders showing the related contracts

[Hamlet]

That looks like it should. One way I test things is using Virtual columns to try the expressions. Try adding a Virtual Column with just [Related Contracts][Project_ID] and click on the TEST option and see if it brings the right information.

[BriW307]

Thank you for this, very helpful for testing. The result is multiple listings of the [Project_ID] in the test

[Hamlet]

That means that <<Start: [Related Contracts]>> should be working on the template. You can try removing the template file location in the TASK and then clicking "create" to have AppSheet auto make a new one and see if on that one the <<Start>> expression works.

[Hamlet]

The only other thing I can think of is that somehow the values in the column [Workorder_ID] of the CONTRACT table are not exactly the same as the values in the column [Workorder_ID] of the WORKORDERS table. So the <<Start: [Related Contracts]>> it's not bringing any data since it can't find the records that it's pointing to.

[BriW307]

In the app it does show the related workorders though.. 

[Hamlet]

I see. Well maybe it's just the record you are testing the template on? Try the template on different WORKORDERS records. Because from what I can see, it should be working with just <<Start: [Related Contracts]]>>

[BriW307]

I keep making new ones and saving them to double check... Trying different customers as well and different items. 

I agree, it Should be working.. It's making me crazy

[SkrOYC]

Are you using security filters or some kind of filter based on usersettings? @BriW307 

[BriW307]

None.. 

[SkrOYC]

I deleted the space between Start: and [Related...] from the template. Try now.

Before:

<<Start: [Related..]>>

Now:

<<Start:[Related..]>>

[BriW307]

No change

[James_Burns]

I set up a quick test app and used your report template and it's generating the following output... is this what you're expecting to see?

[BriW307]

yes! Any clue's on my end where the glitch is?

[James_Burns]

Can you confirm the following details:

Contract_ID is the key column for the Contract table.

Workorder_ID is the key column for the Workorders table.

The formula for Related Contacts is REF_ROWS("Contract", "Workorder_ID").

The task is set to work against the Workorders table.

Is the task sending an email or creating a file?

[BriW307]

I've verified the key columns, and formula, it is set against workorders 

and the formula

The task was to send an email 

[James_Burns]

Perhaps you can pare the template back to basics and then build back from there. Or as @Hamlet suggested, create a new template and start again from there.

Here's the template I used:
https://docs.google.com/document/d/13wuXM6yI1-WkOIA-vuLJ-9mlSr809DtjTxAX9XByw8Y/
and here's the output email I received:

[BriW307 · Accepted solution]

Well, you all are not going to believe this.... I took both of your leads @James_Burns & @Hamlet   and started fresh, linking one at a time. Contract worked, Time and Materials didn't and broke everything again . I removed it from my main app and boom, it all works. I know you're not supposed to use special characters in column headers and now I see why.  

So, [Date & Timestamp] has no issues operating but [Total T&M Time] throws it off in the Referenced rows even though it works in the output.

FIX: I added spaces to [Total T & M Time]  to separate the & symbol and it all works. I will be removing the & symbol from all headers to prevent any further unexpected results.

 I thank you both for so much of your time and help on this! Hopefully this helps someone in the future

[Hamlet]

Wow! I did not know that something like that would break it. Glad to know you figured it out!