Looping with Actions reference issue

[Rafael_ANEIC-PY]

Hello friends, I'm attempting to implement the Looping with Actions method shown in the linked video by the great @MultiTech, but there's a twist to it, it's not just a Parent and a child table.....

It's the whole family.

I'm making an app for a store, and this store makes a lot of "Deals" which are bundles of products from a "Product List" that come with discount prices, I call these products "Deal items".

When an user is processing a "Transaction", I want them to pick the "Deals" and the quantity of these that the client will buy, and once the form for each is saved to the "Deals Sold" table the app SHOULD grab all the "Deal items" that belong to each "Deals" chosen and add them to the "Products sold" table.

For clarity, here's the relevant ER diagram (Related Whatever columns not included)

Show More

My problem right now is that I need to pull the [productREF], [quantity] and [dealPrice] values from the "Deal items" table, and I'm running my actions from the "Deals sold" table, these two tables are connected by the "Deals" table, so they are brother tables I guess.

How can I get that done? I've tried some stuff but to no avail.

[Marc_Dillon]

If I understand completely, I wouldn't use looping, I'd use a reference action.

1. Create an action on Deal_Item table, to create a new record in the Product_Sold table

2. Run a reference action from Deal , referencing the above action, and using a simple FILTER( Deal_Item , [deal] = [_THISROW] ) for the referenced rows. (alternatively, just [Related Deal_Items] )

Also, I think this connection on your ERD is wrong, a Deal is made up of multiple Deal_Items, right? (disclaimer, I'm far from an expert on ERDs and their various symbols)

[Rafael_ANEIC-PY]

Hello @Marc_Dillon, that sounds genius, but if I'm creating the row from within the "Deal Items" table, how would I obtain the orderID to link the new rows to the transaction?

PD: Connection fixed, thank you !

[Marc_Dillon]

MAXROW( transaction , datetime , [user] = USEREMAIL() )

??

[Rafael_ANEIC-PY]

Well, I kept going, and I solved the problem using the forbidden fruit..... lots of SELECT()'s

[productREF]

Show More

INDEX(
SELECT(
Deal items[productREF],
[dealREF]=[_THISROW].[dealREF]
)
-
SELECT(
Products sold[productREF],
[orderREF]=[_THISROW].[transactionREF]
),
1)

[quantity]

Show More

[quantity]*
INDEX(
SELECT(
Deal items[quantity],
AND(
[dealREF]=[_THISROW].[dealREF],
[productREF]=
(INDEX(
SELECT(
Deal items[productREF],
[dealREF]=[_THISROW].[dealREF]
)
-
SELECT(
Products sold[productREF],
[orderREF]=[_THISROW].[transactionREF]
),
1)

)
)
),1)

[dealPrice]

Show More

INDEX(
SELECT(
Deal items[dealPrice],
AND(
[dealREF]=[_THISROW].[dealREF],
[productREF]=
(INDEX(
SELECT(
Deal items[productREF],
[dealREF]=[_THISROW].[dealREF]
)
-
SELECT(
Products sold[productREF],
[orderREF]=[_THISROW].[transactionREF]
),
1))
)
),1)

There MUST be a better way to do this, this feels like setting up a time bomb lol

[MultiTech]

When you're doing stuff like this...

• [orderREF]=[_THISROW].[transactionREF]

...most of the time you're essentially re-creating a list that already exists - if you've connected your tables together with references.

-----------------------------------------------------------------------------

Flow with me:

• Let's say I've got 2 tables:
  • Orders
  • Order_Items
• If I've connected Order_Items to the Orders table using a reference, we get a reverse reference of all the corresponding Order_Items on the Orders table
  • So if I were to look at Order #123 in the app, I would see an inline view of all the corresponding Order_Items for #123

The formula AppSheet uses to accomplish this is REF_ROWS().

-----------------------------------------------------------------------------

The following formulas produce the same output:

• SELECT(Order_Items[OrderItemID], [OrderREF] = [_ThisRow].[OrderID])
• REF_ROWS("Orders", "OrderREF")

Select, however, will kill your system with brute force (once you get tons of data), whereas Ref_Rows will process more efficiently.

-----------------------------------------------------------------------------

@Rafael_ANEIC-PY might you be able to replace a few of your SELECT() statements with use of reverse references?

 - Don't forget about data-subsets too! (If you need to see, say, only the "open" orders for a client or something) You can use slices inside your REF_ROWS() formulas, thus retaining the efficiency of the system, but also allowing the flexibility of using data-subsets for Ref_Rows.

[Rafael_ANEIC-PY]

Good morning friends, @Marc_Dillon I could use MAXROW() but it is not guaranteed that the transactions will be closed before a new one is open, thank you for your help, I appreciate it a lot.

Oh, you can use REF_ROWS() like THAT? that's amazing, and it SEEMS like I can dereference any column using this:

This opens a lot of new possibilities ! Sadly I don't think I can replace the first SELECT() in this picture, since both of these tables are referenced to another table, but they are not directly connected.

That REF_ROWS() alone shaves off half the uglyness of this solution, but I wonder, the calculations made in the other half repeat a lot, and the products that belong to each "Deal" don't vary at all, so I can swap that first SELECT() with a physical enumlist base type ref column that allows me to just dereference anything in that table?

This almost looks efficient ! now I can change all my other ugly expressions, and after that, testing time ! thank you @MultiTech.

[SkrOYC]

I'm gonna test that dereference with Ref_Rows(), sounds new to me.

Let me add my 2 cents.

If you are selling the Deals with it's items, I think you just need to add a new column to the database on the Deals Sold table of EnumList column type where it's initial value would be:

FILTER(
  "DEAL ITEMS",
  [dealREF]=[_THISROW].[dealREF]
)

Since this is an initial value, it won't hurt.
You will end up with a list of the values from Deal items that are child to the Deals table.
That would solve the need for one of the Select() expressions.

Problem is I don't fully understand what you are trying to do or the way you are doing it.

I'm interested to help you on your whole tables schema, although on a best effort basis as always

[Rafael_ANEIC-PY]

Hello again @SkrOYC, sorry I just read your reply today, I was coming back to this post to let the guys know the solution I decided to stick with.

The dereference with Ref_Rows() didn't work, but the app editor let it pass, weird.

I believe I ended up doing something very similar to what you're suggesting, in order to get rid of the selects I made 2 new columns:

1-An enumlist base type ref that lets me store all the Deal Items ID's on their respective Deals.
2-A reference for each "Product Sold" to his "Deal sold" 

With those two I got this working:

So I think that's the end of it, I'm very happy about the results and grateful for all the help @Marc_Dillon @MultiTech 

Basically the whole problem was that I'm making a basic "shopping" app with a twist, that the user can not only add each item one by one from a list of products, he can also add a "Deal" to his order, which consists of a group of items bundled together at reduced prices.

The best way I could think for solving that without messing the inventory management flow that's also on the way is by adding all the products listed in each deal when the customer wishes to purchase a certain amount of any given deal, but that was easier said than done, but now it is done hehehe