Help with nested/matrix sum/product expression?

[Aurelien]

Hi AppSheet Community,

I’m struggling with an expression, I don’t know how to make it.
I hope you will find out !

Situation:
I have basically an inventory app.
The particularity is that some items can be made of others.
For example :

• window panes
• shower kits, each made of 3 window panes and 2 taps

→ How to calculate the virtual total stock of window panes ?

Structure:

• Products Table
• LineItem_Pdt Table
  
• AssemblyPart Table
  

Visual example:

My first thought was to try a matrix product, but I didn’t find a way to do so with AppSheet. I assume I should use some additional virtual column, but I don’t see how.

Thank you in advance for your help, you expressions-masters !

[Steve]

I would think the Products table would have:

• Related LineItem_Pdts

• Related Assembly_Parts

So the inventory for a given Products row’s Related LineItem_Pdts would be:

SUM([Related LineItem_Pdts][Quantity])

And for Related Assembly_Parts:

SUM([Related Assembly_Parts][Quantity])

[Aurelien]

Hi @Steve

Thank you for your help.
You are right about the “Related xxx”, I forgot to mention these, thanks for highlighting it.

I thought about this solution, my issue is about multiplying the products that are part of an assembly.
Let’s illustrate with my previous example of the shower kit which have 3 window panes.

• I have 20 panes initially
• I stock out 2 panes and 4 shower kits
• then I will get 20-2-4*3=6 panes left.

So, the expression for current stock would be something like:

[initial stock]
+SUM([Related LineItem_Pdts][Quantity])
+ {Sum of assemblies products, which contains my product key, 
     multiplied per their own quantity in this assembly}

(…not sure of explaining clearly my thought )

[Steve]

Wouldn’t this:

Steve:

SUM([Related Assembly_Parts][Quantity])

Give you this?

Aurelien:

{Sum of assemblies products, which contains my product key, 
     multiplied per their own quantity in this assembly}

[Aurelien]

Hi @Steve

Sorry for being that long to answer, I turned the problem in my head and could not solve it.

This:

Steve:

SUM([Related Assembly_Parts][Quantity])

Won’t be enough in my opinion, as I need then to multiply the result by the line item quantity.

I made a picture to illustrate my current app structure, I hope I did not forget any relevant information.

Feel free to suggest any other structure that would be adequate, I’m not 100% sure of having chosen the perfect structure for my need.

And again: thank you for your time and help on this case !

[Steve]

I’m confused. You now introduced component parts. How do those relate to your previous comments?

[Aurelien]

Hi @Steve

Sorry I wasn’t clear, I probably did not give enough context at first.
Here is a more explained context.

My app is somewhat a basic inventory app.
Its particularity is that some of the products can be made of various other products, hence the example with the shower kit, with its 3 panes and its 2 taps.

In order to proceed to stock in and stock out, I must be able to say “ok, here are 15 window panes, 5 shower kits, and 36 taps”.
Now, in order to achieve the calculation of my inventory stock, I must be able to say “for single elements (i.e. no kits), there are currently 20 panes and 30 taps”, no matter how many shower kits I physically have.

To do so, I used a table, which is here Assembly_Parts, and there are 2 columns with type Ref, all relating to the Products Table, + 1 column quantity which says how many “components” product are part of the “part of” product. The vocabulary may be incorrect for understanding purpose ?

In order to make the my own understanding clearer, I just renamed the “product” from the first picture into “component_product”, but nothing else changed.

My Assembly_Part table may be not the good idea or the good structure for it, that what came to my mind in order to solve this “assembly/composition” request. This is why I’m open to suggestions as well.

I hope it sounds clearer now, let me know if you need further informations

[WillowMobileSys]

Are Kits tracked as a product in the Products Table as well?

Aurelien:

I just renamed the “product” from the first picture into “component_product”,

I recommend in your Lineitem_Pdt table that you change “product” to something like “ordered_product”. Because I think the complexity of the expression will run into a ambiguity issue if you do not.

The expression will be something like:

SUM of Quantities 
             where Products Product in LineItem Products 
             AND LineItem Product is NOT a kit

+ 

SUM of (LineItem Quantity * Kit Quantity) 
             where Products Product in Assembly_Parts Product 
             AND Assmebly_Parts Kit in LineItem Products

These are just psuedo-expression meant to clarify the computation needed. They are not meant to be logically accurate. The expression for the second SUM will be a doozy!

---

One thing I am curious about. You mention this is an Inventory app. The expression you are after is a count of Products Ordered - over time. I’m unclear how you are using that in an Inventory app? Unless it’s just informational?

Also, should you ever decide you need to purge old Orders from the system, the count will no longer have any meaning.

[Aurelien]

Hi @WillowMobileSystems

WillowMobileSystems:

One thing I am curious about. You mention this is an Inventory app. The expression you are after is a count of Products Ordered - over time. I’m unclear how you are using that in an Inventory app? Unless it’s just informational?

Correct, and this is more than informational.
As English is not my first language, I may have used an incorrect vocabulary for Inventory app, but I think that’s what it is. Maybe I should call it a “Tracking stock app” ?
If that induced people in misunderstanding, I apologize.

My client have all of these products, both assembled and single, and he will bill its client depending on it. Furthermore, he also needs to know the cost price for each work he is about.
So, there are various purposes:

• know what he used
• know what he potentially has, or can have, in its warehouse

BTW, your suggestion to use “ordered_Product” instead of “product” is a good idea, I will use it.

WillowMobileSystems:

The expression for the second SUM will be a doozy

That’s exactly what’s turning me crazy
And this is why I’m first trying, one step after another, to find out how to deal with the structure to get what you summarized elegantly as: SUM of (LineItem Quantity * Kit Quantity)

At reading the posts on this thread and summing the time spent on it, I’m starting to think that I should try another way to manage all of this.

If you have any other suggestion to offer I will take it with great pleasure and relief !
In the meantime I will try to work differently on the structure. I will keep you updated.

[1minManager]

Im not sure if I’ve understood this all correctly… But I think you might have to look at a Bot and Actions to get all this to work.

So as I understand:
Product = Shower Kit
LineItem_Pdt = 2 x Taps and 3 x Window Panes

So for 3 x Shower Kits we need to see 6 x Taps and 9 x Window Panes

I think you need another column in LineItem_Pdt that tracks [TOTAL Quantity]. So this will have to look to the parent table so that you see for taps:
[Quantity]=2
[TOTAL Quantity]=6

So your SUM() is just looking at the LineItem_Pdt table. Which is easy.

For testing try just setup [TOTAL Quantity] as a virtual column. But if it works, but slows the App down too much you might have to setup some Bots & Actions so that changes in Assembly_Parts can be written to the relevant rows in LineItem_Pdt

Hope this helps

Simon@1minManager.com

[WillowMobileSys]

1minManager:

I think you need another column in LineItem_Pdt that tracks [TOTAL Quantity]. So this will have to look to the parent table so that you see for taps:
[Quantity]=2
[TOTAL Quantity]=6

I think you are on the right track.

I believe the problem has been that the LineItem table ONLY reflected the Kit ordered and none of the items that make up the kit.

So, based on the idea sparked by @1minManager, I recommend adding to the LineItem table a Kit column, Kit Quantity column and then a Total Quantity column

When a Kit is ordered you would add rows for each kit item, fill in the Kit ID, Kit Quantity, Product, Quantity(as part of kit), and then as @1minManager suggested - compute Total Quantity.

When the item Ordered is NOT a kit - then Total Quantity becomes the Ordered Quantity for that product

Now, all of the Ordered Quantity values are in the LineItems table to be SUMmed. You’ll need to grab the Kit Quantity for kits and the Total QUantity for all other product items.

---

The changes above leaves 1 question…how does a user order a Kit and then all of the kit items get added to the LineItem table?

I have implemented something similar and this is what I did.

When a Kit was Ordered, I made visible an inline table to reflect the “ordered items” but it was just a list of the kit items - not really part of the Order yet. When the Order is saved, I then used automation to CREATE the physical line item rows populating the item values from the kit items table and computing the necessary Quantities.

I hope this gives you an idea of how to solve your difficult problem!

[Aurelien]

Hi @WillowMobileSystems and @1minManager , without forgetting @Steve of course

You are awesome and very kind you guys !

I will try your suggestions and let you know how I deal with it.
Thank you again !