Related Table View, Summarized

[Jason808]

Is there a way to display a related table but have the records grouped by a columns value and totals summed? Something like this for a specific item (Items.ID)?:
SELECT SUM(Quantity), Location
FROM InventoryLog
GROUP BY Location
ORDER BY Location

[Aurelien · Accepted solution]

Hi @Jason808 

For further question, please post in Q&A board, not Tips & Tricks 🙂

1) Let's assume you have in your related table, a column named [parentColumnRef] with type Ref, with its source table being your current table ( assumption: PRODUCT or something like that?)

2) Create a slice of the related table - let's name it "myChildrenSlice" (assumption: "InventoryLogSlice")

3) in your parent table, create a virtual column - let's name it [_related_children_slice] with this expression:

REF_ROWS("myChildrenSlice", "parentColumnRef")

assumption:

REF_ROWS("inventoryLogSlice", "Product")

4) create a view based on "myChildrenSlice", with:

- view type Table

- groupby: Location, Ascending (or Descending)

- group aggregate: SUM:Quantity

5) Eventually, you can hide the previously existing "Related Inventory Logs" that you may have, at disabling its SHOW property.

[Aurelien · Accepted solution]

Hi @Jason808 

For further question, please post in Q&A board, not Tips & Tricks 🙂

1) Let's assume you have in your related table, a column named [parentColumnRef] with type Ref, with its source table being your current table ( assumption: PRODUCT or something like that?)

2) Create a slice of the related table - let's name it "myChildrenSlice" (assumption: "InventoryLogSlice")

3) in your parent table, create a virtual column - let's name it [_related_children_slice] with this expression:

REF_ROWS("myChildrenSlice", "parentColumnRef")

assumption:

REF_ROWS("inventoryLogSlice", "Product")

4) create a view based on "myChildrenSlice", with:

- view type Table

- groupby: Location, Ascending (or Descending)

- group aggregate: SUM:Quantity

5) Eventually, you can hide the previously existing "Related Inventory Logs" that you may have, at disabling its SHOW property.

[Jason808]

Thank you for your reply and I will try to be more careful with where I post, need to learn this forum interface besides appsheet 😃

I believe I was able to get this to work kind of natively, just selecting the group by and aggregate option but the problem is it still shows all the detail records. I'm sorry I didn't mention this, was hoping the SQL query would speak for itself. I'd like the related view to show for each item:
Location Name Quantity
Location1 10
Location2 0
Location3 2
Location4 19

Without showing the eventual hundreds or thousands of records that make up the result as it would require a ton of scrolling to get to the next location's totals.

I ran through the instructions above and it showed the same result with the detail records. I'm trying to suppress all the detail records from showing but haven't figured out a way to do so. I did see a post about a pdf and using <<START but not sure how to work it into a related view. Thank you.

[Steve · Accepted solution]

Control grouping in a view 

[Jason808]

Thanks that did wonders for the View. I still don't know how to get it to show as an inline view as a related item on the main Items view. With the system generated InventoryLog_Inline I was able to adjust the grouping and sorting to work EXCEPT for the changes _RowNumber is supposed to modify.

Maybe it's not possible to show a related summary without details, without clicking the "View" button?

[Steve]

Please post screenshots of the inline view as shown in the app and of the view itself you're configuring.

[Jason808]

I've tried posting the two pictures twice and keep getting a forum error

[Jason808]

It took that one, so maybe one at a time?

Inline View on Item

[Jason808]

After clicking 'View' at the bottom of the Inline View (this is how i want it to show inline)

[Steve]

I've never tried this before and this behavior comes as a surprise. Looks like it's not going to allow it.

[Jason808]

I was unable to attach the image of my view setup after several tries. I am grouping by location id and _row number, group aggregate being SUM::Amount. Like I mentioned, the view looks great. Current workaround is to teach the users to click the VIEW button. I'm going to use the default generated inline view to simplify it. I love that grouping by _RowNumber almost solves it. (thought it might make more sense for them to have an option in the group by to show or suppress detail records). OK to mark_RowNumber as the solution as it is the closest I am probably going to get?

[Steve]

I'd say so, yep.