Referencing 2 tables on a single start expression

[Rafael_ANEIC-PY]

Hello friends, the problem i have come upon is that i’m trying to load information from different tables into a single workflow doc template, i’d like to pull a list of unique ids from [TABLE A], and be able to use that list of ids using the context of [TABLE B] for the columns inside said start expression.

Is there a way to do this without having those tables directly referencing each other?

To give a better idea of my problem, imagine a basic shopping app, where a customer picks some products and amounts, a simple workflow doc template would display a list of the items and amounts the customer bought for that given order.

In my case i need to display all the items that the customer is able to buy, even those that he hasn’t purchased, that list of all products is in [TABLE A], and the individual item purchase records are in [TABLE B].

[MultiTech]

Looks like there’s two requests here:

Rafael_ANEIC-PY:

i’d like to pull a list of unique ids from [TABLE A], and be able to use that list of ids using the context of [TABLE B] for the columns inside said start expression.

For this, it sounds like what you need to do is extract from your orders the list of products selected - then you want to use that list inside a <<START: in a workflow so that the product details are displayed in the workflow instead of the order details.

For this, check out List dereferences:

help.appsheet.com

List Dereference

Dereference the items of a list in bulk to produce a list of the resulting values.

I’m assuming you’ve got an Order parent level, and then order details where people select the individual products they want (and quantity, etc.)…

• On the Order Parent, you’ve got a [Related OrderDetails] - a virtual column that’s a list of all the related OrderDetail records
  • From this, use a List Dereference formula to pull out the Product values.

[Related OrderDetails][ProductID]

This will product a list of all the values in the [ProductID] column for all the related records.

With this on the Order level, you can then use this column’s values inside your <<START: expression

<<START: SELECT(Products[ProductID], 
  IN([ProductID], [_ThisRow].[Products_From_OrderDetails]))>>

---

The second question

Rafael_ANEIC-PY:

In my case i need to display all the items that the customer is able to buy, even those that he hasn’t purchased, that list of all products is in [TABLE A], and the individual item purchase records are in [TABLE B].

For this one, take a look at List dereferences and list math (addition/subtration)

help.appsheet.com

List Subtraction

Subtract lists to produce another containing only the unique items of the first list that aren't also in the second.

You can easily create a list of the products selected (list deref from the OrderDetails), then take the master list of products and subtract from that the items the person has already selected.

• this would give you a list of products that haven’t been selected by the user.

Alternatively, if you added the Master List to the list of products selected by the user;

• you would end up with a list of all the products (because you took what the user selected… and added everything to it).

[MultiTech]

On an entirely different note:

It’s entirely possible to store a list of references inside an EnumList - with the base type text if you want.

You can also easily store a list as a string (inside a LongText/Text column) by wrapping the list formula in CONCATENATE().

• Then you can reconvert this string into a list using SPLIT().

Just fyi.

[Rafael_ANEIC-PY]

Hi Matt ! thank you for your detailed answer, i’ll study everything you mentioned and i’ll report again after i give it a go.

[Rafael_ANEIC-PY]

Hello again @MultiTech_Visions , after struggling a whole day i realized i was over complicating the problem.

In my case i need to display all the items that the customer is able to buy, even those that he hasn’t purchased, that list of all products is in [TABLE A], and the individual item purchase records are in [TABLE B].

It’s just comes down to displaying the whole list of items of [TABLE A] and using the reference with [TABLE B] to sum each item’s “related sales” in order to obtain the accumulated total of sales per item.

To do that, i need to be able to filter the related sales based on a column named [OrderNumber] that is within the table that triggers the workflow, and is also recorded in each item sale, i’m trying something like:

<<SUM(
	SELECT(
			[Related Items sold][Qty],
			[OrderNumber]<[_THISROW-1].[OrderNumber]
		  )
     )
>>

Any suggestions? i’m not very familiar with the use of [_THISROW] in template variables, and haven’t managed to grasp the concept after reading the documentation about it.

[Steve · Accepted solution]

Rafael_ANEIC-PY:

i’m not very familiar with the use of [_THISROW] in template variables, and haven’t managed to grasp the concept after reading the documentation about it.

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

Back-references within nested queries ([_THISROW-n]) Consider a complex App formula with “nested” SELECT() expressions (SELECT() expressions within SELECT() expressions): SELECT( table[column1], ... SELECT( table[column2], ... SELECT( table[column3], ... ) ... ) ... ) Let’s call the row for which this entire expression is evaluated as its App formula the Origin Row. Within this entire expression, we can refer to column values of the Origin Row by dere…

[Rafael_ANEIC-PY]

oooh sheeeeeet, it works at last ! thank you @Steve and @MultiTech_Visions, i learned a lot from your replies, at one point yesterday i was complicating the matter so much i was about to