Reverse Reference grouping or totals

[Thales_Taranto]

Hello everyone,

I am developing an inventory application that, in addition to recording the inventory log, also includes their storage location. The idea is to allow products to be stored in different locations. When opening product detail view, I need to list the locations where this specific Product is and it quantity is but i don't have a clue how to do it. Can any one help me?

The structure of my application is as follows:

Lockers Locations -> Boxes -> Inventory Log

Product -> Inventory Log

Lockers Locations Table

ID_Locker

Location In Building

Locker XY Map Image

Boxes Table

ID_Boxes	ID_Locker	Locker XY Coordinates	Related Inventory Log	Products
		[ID_Locker].[Locker XY Map Image] as background image	VC - REF_ROWS from Inventory Log Table	UNIQUE([Related Inventory Log][ID_Product) - Reverse dereference VC i created

Inventory Log Table

ID_Inventory Log

ID_Boxes

ID_Product

Date

Quantity

Product Table

ID_Product	Name	Related Inventory Log	Total in Stock	Boxes
			SUM([Related Inventory Log][Quantity])	UNIQUE([Related Inventory Log][Products])

[Suvrutt_Gurjar]

if understanding of your requirement is correct, please add a VC column called say [Box_Location] in the "Inventory_Log" table with an expression something like 

[ID_Boxes].[ID_Locker].[Location in Building]

I believe this [Box_Location] column will give you the location of each product? 

[Thales_Taranto]

I'm sorry, I believe I haven't adequately explained what I need. When I access the Product Detail View, I would like to have some visualization (maybe an inline view of Boxes) showing: ID_Boxes, ID_Locker (if I label Location in Building, it will already be resolved), Locker XY Coordinates, and a VC with the total quantity of this product in this box. I would like to know how could i include this visualization, primarily how I would create this VC with the total quantity of a specific product in the Boxes table (for the example of the inline view I presented above).

[Suvrutt_Gurjar]

Like this?

Expanded view of Inventory log

Are you not able to view it in the related inventory log (inline view) the related details?

[Thales_Taranto]

Yeah i can use this (image bellow).

But i would like to have this information also on the Boxes inline view. As you can see on the image bellow my app is in Portuguese and you can translate "Embalagem" to the Boxes Table i said before, also you can see that i just tried to do what i needed but it did not work as expected. At the "8642fa0d" the value at new virtual column should be 1. As the detail invetory log bellow shows

I created a VC called Total-Product-Box on the Inventory Log Table with the formula:

SUM(
SELECT(InventoryLog[Quantity],
AND(
[ID_Product]=[_THISROW].[ID_Product],
[ID_Boxes]=[_THISROW].[ID_Boxes])
)
)

Then I created another VC in the Boxes Table that retrieves the last row in the Related Inventory Log list based on the date. As bellow:

LOOKUP(INDEX(ORDERBY([Related Inventory Log],Date]),1),"Inventory Log","ID_Inventory Log","Total-Product-Box")

[Suvrutt_Gurjar]

I will revert about the first VC.

For the second VC 

---

@Thales_Taranto wrote:

Then I created another VC in the Boxes Table that retrieves the last row in the Related Inventory Log list based on the date. As bellow:

LOOKUP(INDEX(ORDERBY([Related Inventory Log],Date]),1),"Inventory Log","ID_Inventory Log","Total-Product-Box")

---

you could instead try with an expression

ANY(SELECT([Related Inventory Logs][Total-Product-Box], [ID_Boxes]=[_THISROW].[ID_BOX]))

This expression uses system generated columns , so I believe it will be less sync time expensive than the LOOKUP()  expression you have created.

[Thales_Taranto]

Hey @Suvrutt_Gurjar ,

---

@Suvrutt_Gurjar wrote:

you could instead try with an expression

ANY(SELECT([Related Inventory Logs][Total-Product-Box], [ID_Boxes]=[_THISROW].[ID_BOX]))

This expression uses system generated columns , so it will be less sync time expensive than the expression below

---

This approach of yours keeps returning the wrong result, and I believe I have already found the beginning of the solution path:

Firstly, we must establish the correct relationship between Box and Products, that is, we should only list the products in the Box Table that are still actually in the box. This implies selecting only the products whose sum of quantity in the Inventory Log for that box is greater than 0, and vice versa.

I have already made these connections:

Boxes Table:

Product (List ref of Product Tables) - Virtual Column

SELECT([Related Inventory Log][ID_Product], [Total-Boxes-Product]>0,true) - must ommit duplicates

Product Table:

Boxes (List ref of Boxes Tables) - Virtual Column

SELECT([Related Inventory Log][ID_Boxes], [Total-Boxes-Product]>0,true) - must ommit duplicates

But now I need to show in the Product Detail View how many units of a specific product I have in each box. How can I do that?

[Suvrutt_Gurjar]

Maybe I am unable to understand your requirement. I believe you have all the elements in place.

---

@Thales_Taranto wrote:

But i would like to have this information also on the Boxes inline view.

---

I believe this is also visible in the boxes inline view

You may want to pictorially show what you are looking for in which view ( you could use simple tabular format) so that we could look into feasibility of achieving it.

[Thales_Taranto]

Yeah i've made this path you achieved, it just remaining getting the number value that returns the actual quantity of this related product on the Box. Let me try to explain better by the images bellow, the first on is a specific Product Detail View and the second one is an tabular view exemple

Looking at the tabular exemple i think that what i want is simply not feasible as i don't see a way to return a number related to a specific product when you have a list of Products in the a box row value. How can i show in the Rice Detail View for exemple that i want only the rice's total on the Box inline view? 

[Suvrutt_Gurjar]

---

@Thales_Taranto wrote:

Looking at the tabular exemple i think that what i want is simply not feasible as i don't see a way to return a number related to a specific product when you have a list of Products in the a box row value.

---

Thank you for all the details.  I think your above assessment is correct. The Box table has an assortment of products in a single box. So in the Box related view inside the Product view it will be difficult to segregate the quantities for each product.

[Thales_Taranto]

Hey @Suvrutt_Gurjar,

Diving into the community i found a post from @Marc_Dillon that helped me to find a alternative solution, and it is creating more than one inline view from Related Inventory Logs as the post bellow:

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Sample-App-Child-table-with-multiple-parents-Sho... 

---

So i made it here but as expected when i group a column such as Product (to show it at a Box Detail view) the related inventory log show all the rows related to it's table as the image i pasted (Product -> Inventory). Is there a way to hide those rows?

Also, by default the nested row display is set to 5 and it counts the related reference list, in this case it is the invetory log. Is there a way to change what it counts? This is a important thing because i'd like to show all the related grouped columns and if the number of rows is bigger than the set number it'd hide some grouped column.

Only after clicking on "View" that i can see the hidden grouped column.

[Suvrutt_Gurjar]

For displaying more rows in the inline view , simply increase the nested row display limit to more than 5 to a practically suitable limit as per your app needs , something like 20 or 30.

Regarding collapsing grouping of inline rows, I will revert if there is any way to do the needful.

[Thales_Taranto]

@Suvrutt_Gurjar, firstly I would like to thank you for all your help. I think this aproach doesn't fit for purpose since i can't limit the number of logs into the inventory logs. Let's assume that a company could move in and out hundreds of the same product i can't increase the limit since i don't know what it's. 

So i figure out a better way to do it. Still using this slicing method that @Marc_Dillon shows as the goal is to show at the related inventory log view inside product only the total of each product in a specific Box the app could show only one record for each Box. To do so, i need to create a VC on the Inventory Log that stores the Unique Relationship list between Products and Boxes and then use a filter condition on the slice property to show only related Inventory Logs that are in this list. (Look at the similar tabular example i showed before)

Now the goal is to create a formula that return this list, do you have an idea how to do it?

[Thales_Taranto]

---

@Thales_Taranto wrote:

Only after clicking on "View" that i can see the hidden grouped column.

---

To complement the explanation, I believe this approach would solve the issue of the previous visualization. Using the example as a reference, the visualization would look like this:

[Thales_Taranto · Accepted solution]

@Suvrutt_GurjarI did it! And the solution is simpler than it seems. First i need to filter the Inventory Log that matches the criteria of same row product and box. After that as it doesn't matter what row it is unless it returns a single row value i can just use the INDEX(filter,1). So the formula should be: 

INDEX(FILTER("Invetory Log",AND(
[ID_Product]=[_THISROW].[ID_Product],
[ID_Box]=[_THISROW].[ID_Box]
)),1)

And the result is as expected:

Maybe i'll create a sample app to show all the settings and post at Tips&Tricks section. 

[Suvrutt_Gurjar]

Excellent. Nice to know you got the solution.

Sorry for not responding to latest 2-3 posts of yours in this thread as it was late night at my place and I planned to revert in my morning which is now. 

I will study your solution and revert with any observations.

Well done.

Edit: Could you update how are you creating the sum for each product for each box? Are you using a VC in the Inventory Log table?

[Thales_Taranto]

---

@Suvrutt_Gurjar wrote:

Sorry for not responding to latest 2-3 posts of yours in this thread as it was late night at my place and I planned to revert in my morning which is now. 

---

Hey @Suvrutt_Gurjar, don't worry you've helped me a lot to get on what we're expecting.

---

@Suvrutt_Gurjar wrote:

Edit: Could you update how are you creating the sum for each product for each box? Are you using a VC in the Inventory Log table?

---

There are two options in this case. You could use a normal column on the table if you need the balance of the transactions at the Inventory Log. Or you could use the VC if you don't need to save the balance, in this app i used the VC method. 

But the formula should be the same i guess, and it is:

SUM(
SELECT(Inventory Log[Quantity],
AND(
[ID_Product]=[_THISROW].[ID_Product],
[ID_Box]=[_THISROW].[ID_Box]
)
)
)

[Suvrutt_Gurjar]

Thank you for the update @Thales_Taranto .

I overall appreciate your willingness to find the appropriate solution by trying various options and also providing very methodical information in the post thread. Keep it up.

Yes, I was wondering if real column with may be an action to run on the records with same [ID_Product] and {ID_Box] will be a better option in terms of sync time optimization. The real column option could be a better option if there are a handful of records with same [ID_Product] and {ID_Box] combination but there are a high number of records in the "Inventory Log" table.