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:
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
This resulted in errors:
The Order Capture template is the same as the default template, here’s the link
Run task “Send an email”
These are the columns in the Contract Table:
| Workorder_ID | Contract_ID | Project_ID | Unit | Operator | Rate | Hours | Total | Description |
|---|
What is the key column for your Contracts table?
Contract_ID
Did you try: <<Start: SELECT(Contracts[Contract_ID],[_THISROW].[Project_ID]=[Project_ID])>><<[Project_ID]>> ?
Perhaps this: <<Start:[Related Contracts]>><<[Contract_ID].[Project_ID]>>
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
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
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]>>
Also:
<<Start:[Related Contracts]>>
<<[Workorder_ID].[Project_ID]>>
<>
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)
Quick question,
Do you have the [Related Contracts] virtual column showing up in your Workorders Columns section in the editor?
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.
Yep. You can even have them nested.
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.
In the app it does show the related workorders though..
Are you using security filters or some kind of filter based on usersettings? @BriW307
Thank you for this, very helpful for testing. The result is multiple listings of the [Project_ID] in the test
