SUM quantity RELATED PRODUCTS

[bolognesiedalla]

Hi everyone
I would like to understand the best method to deal with this scenario:

I have these tables:

FERTILIZATION PLANS (parent)

INTERVENTIONS (child)

INTERVENTIONS LINES (grandchild)

PRODUCTS

each row of INTERVENTIONS LINES includes a product from the PRODUCTS table with the relative quantities.

I would therefore like to display in the details view of the FERTILIZATION PLANS the sum of the quantities of each product used in the individual plan.

On the FERTILIZATION PLANS table I already have the list of unique products used UNIQUE([Related INTERVENTIONS LINES][PRODUCTS]).

[Suvrutt_Gurjar]

---

@bolognesiedalla wrote:

On the FERTILIZATION PLANS table I already have the list of unique products used UNIQUE([Related INTERVENTIONS LINES][PRODUCTS]).

---

Could you update if you have [Related INTERVENTIONS LINES]  column in the parent table FERTILIZATION PLANS? If so, have you manually created it? This is so because since "INTERVENTIONS LINES" is grandchild, I believe the system generated reverse reference column will be created in the child and not in the parent?

---

@bolognesiedalla wrote:

I would therefore like to display in the details view of the FERTILIZATION PLANS the sum of the quantities of each product used in the individual plan.

---

Could you elaborate? Does " individual plan" mean "INTERVENTIONS LINES" ? 

[bolognesiedalla]

---

---

@Suvrutt_Gurjar wrote:

Could you update if you have [Related INTERVENTIONS LINES]  column in the parent table FERTILIZATION PLANS? If so, have you manually created it? This is so because since "INTERVENTIONS LINES" is grandchild, I believe the system generated reverse reference column will be created in the child and not in the parent?

---

---

I also have a Ref column in the grandchild table that autofills with the parent's name

---

@Suvrutt_Gurjar wrote:

Could you elaborate? Does " individual plan" mean "INTERVENTIONS LINES" ? 

---

I meant this (sorry if I was unclear): since I will have different plans in the data, each with their own ref rows, I want an inline view to appear in the detail view of each single plan with:

1. the list of products used in that plan

2. the sum of the [QUANTITY] column of the INTERVENTION ROWS table where my plan is the parent.

Did I explain myself better?

---

[Suvrutt_Gurjar]

Thank you. However, what you mean by plan is not yet clear. Could you elaborate? From which table it is? 

[bolognesiedalla]

Excuse me. I mean that I would like see that data in the detail view of each FERTILIZATION PLANS[ID_PLAN] (my key column of FERTILIZATION PLANS table)

[Suvrutt_Gurjar]

Thank you. Could you share the screenshots of relevant columns with some associated description so that your requirement is clear? Something like below. Below is just example). I think that will make the requirement clearer. 

Or else maybe someone else who has understood the requirement based on description so far, could help you.

Customer Name column in the table below references Customer table

Order Details table below references Orders table ( through Order ID) and Products table through Product ID columns

Now I would like the sum of all the [Quantity] from the Order details table related to all orders of a Customer to be summed in the Customer table's detail view for each Customer.

[bolognesiedalla]

So...this is the FERTILIZATION PLANS table....

This is the INTERVENTIONS table, which refers to the FERTILIZATION PLANS table (via the column) [IDPLAN] )

This is the INTERVENTIONS ROWS table which refers to the NUTRITION PLANS table (via the column) [IDPLAN]), but also refers to the PRODUCTS table (via the [PRODUCTS] column).

My goal is to obtain a summary of the products used with the relative quantities in the PIANI_detail view (like this one)

Do you think it's possible?

[Suvrutt_Gurjar]

Thank you. Is there a [Quantity] column in the INTERVENTIONS ROWS  table? If so , are the products and quantities not simply getting displayed in the related table view in the parent  FERTILIZATION PLANS table ?

[bolognesiedalla]

[DOSAGGIO] column is the quantity column. ([DOSAGE])

In the PLANS _detail view I see the list of  INTERVENTION LINES[ID_ROW]. This implies that I will have duplicate products, because the same products can be repeated in multiple INTERVENTION LINES.

Instead, I would like the list of unique products with the sum of the total [DOSAGE].

[Suvrutt_Gurjar]

Thank you. I think your summation of quantities for each product and then displaying them uniquely in the parent table will be resource intensive and a bit complex. One may not easily escape creating summing expressions using SELECT() etc. to add quantities for each product. Thereafter you will need each product only once to display in parent table that will add another layer of expressions to select only one row for each product. You are currently using UNIQUE([Related INTERVENTIONS LINES][PRODUCTS]), but I believe that expression will not help to display product and summed quantities together. You will need  a slice on the INTERVENTIONS ROWS  table to uniquely select each product row just once. 

I would be able to suggest these expressions, but I am afraid those will not be efficient ones for sync time.

Another possible approach will be complex one to compute summation of each product,  through reference actions whenever a row for a product is added or deleted.

Maybe someone else can suggest a better approach or else I will post my suggestion tomorrow by thinking of any easier option. 

[Suvrutt_Gurjar · Accepted solution]

Please try below

1. Please add a column called say [Product_Sum], number type in the INTERVENTION LINES table.

2. Please create an action called say "Add Products"  of type  "Data: set the values of some columns in this row" on the INTERVENTION LINES table to set the  [Product_Sum] column with an expression something like 

SUM(SELECT(INTERVENTION LINES[DOSAGGIO] , AND([IDPIANO]=[_THISROW].[IDPIANO], [PRODOTO]=[_THISROW].[PRODOTTO])))

The prominence of this action should be "Do not display"

3. Please create an automation bot of data change type with the following details

Condition for bot

[_THISROW_BEFORE].[DOSAGGIO] <>[_THISROW_AFTER].[DOSAGGIO] 

4. The bot step should look like below. Please pay attention to highlighted settings

The referenced rows expression is something like 

SELECT(INTERVENTION LINES[IDRIGA] , AND([IDPIANO]=[_THISROW].[IDPIANO], [PRODOTO]=[_THISROW].[PRODOTTO])))

5. Create a slice called say "Unique_Products" on the table INTERVENTION LINES  with an expression something like 

[IDRIGA]=MAXROW("INTERVENTION LINES","_RowNumber", AND([IDPIANO]=[_THISROW].[IDPIANO], [PRODOTO]=[_THISROW].[PRODOTTO]))

6. Add a VC in the FERTILIZATION PLANS table with an expression something like 

REF_ROWS("Unique_Products", "IDPIANO")

VC type list, element type -ref , referenced table name -"Unique_products (Slice)"

This VC should reflect the summed counts for each product only once in the parent table as an inline view. The column [Product_Sum] will capture the added quantities.

Note: There have been table, column names sometimes in your native language and sometimes in English in the post.  Sometimes names are in screenshots. So I could not properly copy. There might be mistakes in table name and column name spellings and names understanding. Hopefully you will get the essence of solution.  I request you to correct those names spellings as required.

[bolognesiedalla]

I understood the essence of your steps....I had created something much more complex at the time, so I'll immediately try to implement and test what you recommended.
Thank you very much for your detailed explanation and the time you dedicated to me. I update you!!

[Suvrutt_Gurjar]

You are welcome. Yes, please update us how it works.

[bolognesiedalla]

I get what I wanted!! great solution!!

I had created a separate "PRODUCT SUMMARY" table but it was difficult to maintain alignment, as well as making the app heavier and making actions such as "COPY PLAN" take a lot longer.

With your instructions I have practically halved the time, and practically eliminated errors.

The only thing I changed the activation condition of the BOT was also adding

OR([_THISROW_BEFORE].[DOSAGGIO] <>[_THISROW_AFTER].[DOSAGGIO],[_THISROW_BEFORE].[PRODUCT]<>[_THISROW_AFTER].[PRODUCT])

to recalculate if the chosen product changes. (and added a step that also recalculates the product that is removed)

Thanks again!! I am grateful to you

[Suvrutt_Gurjar]

You are welcome. Thank you for the update.

Good to know the change you made 

---

@bolognesiedalla wrote:

The only thing I changed the activation condition of the BOT was also adding

OR([_THISROW_BEFORE].[DOSAGGIO] <>[_THISROW_AFTER].[DOSAGGIO],[_THISROW_BEFORE].[PRODUCT]<>[_THISROW_AFTER].[PRODUCT])

---

Yes, the product can also change when bot will need a run.

Good to know it works the way you want.

All the best.