Make material summary table

Obrasel

Hello, i have an app to make quotes, the app has Materials, Items(which are made of different materials), BoM(which connects this last two as a middle table), Quotes and Quotes_details(which connects quotes and items), the issue i have is that i was trying to have some sort of table or list that show how much of each material is used in a quote(in essence multiplying the quantity values that BoM and Quote_Details have) . I tried using a middle table but i had issues automating it to get the proper material + item + quote, at most i could only get the correct info for two of those using one of the middle tables but no luck with the remaining one.

Any help would be appreciated, thanks in advance.

Marc_Dillon

Does the quote_detail table have a quantity column? You should be able to to generate an inline view of that table, from a quote record, that shows material and quantity columns.

Obrasel

Hello, sorry for the late response

quote_details does have a quantity column, however that is the quantity of the item asociated with the quote, bom(the other middle table) has also a quantity column, but this ones is the quantity of a material asociated with an item. The problem is trying to get something like a table that multiplies those two numbers to get how much of a material was used for a quote.

Hope this clears some stuff

Marc_Dillon

Oh... yah... I did not read that close enough, sorry.

Definitely with a new table. "quote_material" or whatever. I would tackle the creation of the records in that table using the API. You can get really complex and nested with your expressions in an API call body, much better, and more performant, than trying to do it with app actions.

Would there be cases where the same material is used in multiple items on the quote, and you'd want to sum those up into a single record?

TeeSee1

I believe the Quote Details only have items and not materials which need to be derived from BOM.

The best I could come up with to do that is as follows,, (I would be interested to know simpler ways if someone can chip in here..)

1) Create a table to store a Quote ID, call it CONTROL, this will be used within the action on BOM to retrieve necessary data.

2) Create a table to store materials related to quotes, say QUOTE_MATERIALS

id, ref.quote, ref.quote_detail, ref.material, qty.quote, qty.bom, qty.required

You do not need all of the above fields but is more informational if you do.

3) Create an action on Quotes to insert its id into CONTROL

4) Create an action on BOM to add a new row into QUOTE_MATERIALS

ref.quote: ANY(CONTROL[ref.quote])
ref.quote.detail: ANY(FILTER("QUOTE_DETAILS",AND([ref.quote]=ANY(CONTROL[ref.quote]),[ref.item]=[_THISROW].[ref.item])))
ref.material: [ref.material]
qty.bom: [qty]
qty.quote: ANY(SELECT(QUOTE_DETAILS[qty],AND([ref.quote]=ANY(CONTROL[ref.quote]),[ref.item]=[_THISROW].[ref.item])))

5) Create a bot on Control added trigger which is fired by the action on QUOTES and do Run action on rows of BOM. Referenced rows should be

FILTER("BOM",IN([ref.item],SELECT(QUOTE_DETAILS[ref.item],[ref.quote]=ANY(CONTROL[ref.quote]))))

You can add a step to delete the record on CONTROL for house keeping.

Now all this will produce duplicate material records in QUOTE_MATERIALS if particular materials are contained multiple times in a quote. This can be resolved in various ways.

Group the inline view of QUOTE_MATERIALS by ref.material and show SUM. A caveat here. The view is displayed differently depending on various factors such as the desktop view version, Detail view vs Dashboard view, etc, so you need to choose the best way to fit your needs
You could run another step to summarize QUOTE_MATERIALS into another table to eliminate duplicate MATERIAL rows.

This configuration assumes a single person operation. If your app needs to handle concurrent user usage, obviously additional control needs to be put in place.

I hope this gives you some ideas.