related table records not printing the info in bot

AppSheet AppSheet Q&A
1

I am testing a bot which prints records from a table and a related child table to a pdf document.

The main table data prints correctly, however the child table records do not.

The loop prints the amount of related child records but not the info within these records.

At the UX for the parent table the child records are listed properly through the use of a “SELECT” formula which references the common key.

like: SELECT(child products[child SKU], ([parent sku] = [_THISROW].[SKU]))

The template formula which loops through the child records is:

<<Start: [Related child products]>>

Sku: <<[child SKU]>> Cantidad: <<[qty]>>

<>

The result looks like:

Sku: Cantidad:

Sku: Cantidad:

Sku: Cantidad:

Sku: Cantidad:

Sku: Cantidad:

Sku: Cantidad:

The right number of child records but missing the actual info.

Any help will be greatly appreciated.

Jairo

2

Just wondering..

Your reverse ref col is not using the usual REF_ROW formula but SELECT which I assume you wrote yourself.

Is [child SKU] the key col of the child products table?

1 Like
3

Child products table has a unique ID for each entry, the relationship to the “mother table” (codigos) is through “parent sku”, the codigos table key is a concatenation of an order number and product number (not sku)

4

A START expression must evaluate to a table’s key.

That is why you are not getting the data from the child table would be my guess.

2 Likes
5

I’m writing this from my phone, but

  • are you able to send us a screenshot of the template?
  • This looks like there is something missing from the Start expression but I may be wrong there.
6

Great question! This may also be because the refenced column is not showing any data that is directly related to the child records. May need more information about the data structure to better diagnosis the issue.

7

This is the template

Screen Shot 2022-04-29 at 18.55.13.png
Screen Shot 2022-04-29 at 18.55.13.png1302×916 29.2 KB

8

The two tables. Invoice.

The invoice table references the child products table by sku (parent sku at the child products), the keys are not related in appsheet as there would be duplicate records.

Invoice table and child products tabl

Screen Shot 2022-04-29 at 18.58.46.png
Screen Shot 2022-04-29 at 18.58.46.png2584×1086 353 KB

Screen Shot 2022-04-29 at 18.56.55.png
Screen Shot 2022-04-29 at 18.56.55.png2102×780 329 KB

e

9

Looks OK to me except I repeat my question.

Does [Related child products] return key values of the child products table?

10

Would like to see Line Items table also..

11

Invoice table is Line items table (misnamed it previously)

12

And no, [Related child products] is set to return list based on SKU which is not key at Line items since it can repeat.

13

This is the reason I was using the SELECT(child products[child SKU], ([parent sku] = [_THISROW].[SKU]))

14

START expression has to return key values.

3 Likes
15

That explains it.

Is there any way to reference non-key values with a Start expression?

16

You have to get key values on the start expression.

I am a bit confused about your tables structures because your template shows three tables - invoice, line items, child products - but you also say line items is invoice but what you do is to use a select statemen with a filtering condition just like you do with regular expressions. You just make sure you you return key values in case of within a START expression.

<<START: SELECT(child products[child products key], CONDITION here like [field]=[]>>
<<field in child producs>>
<<END>>
2 Likes
17

I ended up creating a concatenated key for “line items” table and a “reverse key” with the same concatenated common fields at “child products” table. I referenced this one back to the “line items” and now it works like a charm.

The solution turns easy with the right info.

Thank you

1 Like