Creating a summary report from related tables

[davidAtayde]

Hi,

I’m developing an app that allows users to generate a material list (ComboKit) composed of one or more kits, and each kit contains one or more materials.

I’ve managed to define the tables and their relationships. I want to create a bot that, when a button is pressed in the ComboKits view, generates a PDF, Doc, or Spreadsheet with the total material list, summing the quantities of identical materials that are part of different kits.

I’m unsure how to generate the total material list from the ComboKit table.

These are the tables:

1. Vehicles

   

2. Materials

   

3. Kits

   

4. Kits_Details

   

5. ComboKit

   

6. ComboKit_Details

   

   Expected outcome example:

   

   APP

   

[Suvrutt_Gurjar]

I think you may want to mention the following

1. Is the [ComboKit_ID] column in the 'ComboKit_Details" table a reference column to the “ComboKit” table?

2. Is there a 1: 1 or 1: Many relationship between the “Vehicles” and “ComboKit” tables? Meaning, can there be multiple “ComboKit” records for one “Vehicles” record or just one “ComboKit” record for each “Vehicle” record?

Edit: made some changes to the question 2.

[Suvrutt_Gurjar]

Till such time you revert, based on the understanding so far , please try below

1. in the “Kits” table, add a virtual column called [ComboKit_Qty] with an expression something like

[Related ComboKits_Details][Qty]

2. In the “Kits_Details” table , add a virtual column called [ComboKit_Qty] with an expression something like

ANY([Kit_ID].[ComboKit_Qty])

3. In the “ComboKits” table , add a column called say [Related Kits_Materials Details] with an expression something like

SPLIT(TEXT([Related Kits][Related Kits_Details])," , ")

4. Then the PDF report template can be something like

Material Details

ComboKits

Name: <<[Name]>>

Vehicle_ID: <<[Vehicle_ID].[Model]>>

ComboKits_Details

Material	Qty	Unit
<<Start: [Related Kits_Materials Details]>><<[Material_ID].[Name]>>	<<[Quantity]*[ComboKit_Qty]>>	<<[Material_ID].[Unit]>><>

[davidAtayde]

1. You’re absolutely right. The [ComboKit_ID] column is indeed a reference column to the ‘ComboKit’ table. I’ve already implemented this relationship.
2. Yes, that’s correct. The relationship between ‘Vehicles’ and ‘ComboKits’ is a one-to-many. A single vehicle can have multiple ComboKits associated with it.

Thank you for your input!

[davidAtayde]

Hi Suvrutt_Gurjar,

When I tried to create the [Related Kits_Materials Details] column in the ComboKits table using the formula editor, I get the following error:

Do I need to make changes to the table structure or relationships?

Thanks in advance for any help!

[Suvrutt_Gurjar]

Sorry, I forgot to mention one more column that needs to be added.

In the ComboKits table, please add a column called [Related kits] with an expression something like

[Vehicle_ID].[Related Kits]

Thereafter you add the column [Related Kits_Materials Details] in the “ComboKits” table.

[davidAtayde]

Hi Suvrutt_Gurjar,

Thanks, I applied the changes and I was able to generate the [Related Kits_Materials Details] column correctly. I generated the template and set up a bot to generate a report whenever the ComboKits table is updated and the status is equal to “done”.

Bot setup:

When I tested it,

I got the following error:

This is the template. I checked for any errors in the code and there doesn’t seem to be any. What could be wrong?

Thanks a lot for your support! Best regards.

[Suvrutt_Gurjar]

Thank you for the details. Please ensure the column [Related Kits_Materials Details] references the “Kits_Details” table. The column’s configuration should be something like below

[davidAtayde]

You were right! I had the element type as “text” instead of “Ref”. Once I made the change, I was able to download the report.

I just have one more question, how can I sum the quantities of repeated materials like the ones highlighted in the image below?

Thank you very much for your support!

[Suvrutt_Gurjar]

Hi @davidAtayde ,

I can give you the approach to compute that addition. In fact I have tested the approach.

However the approach gets convoluted and the columns required will be sync time expensive. I will not recommend using it just for the sake of creating report. I cannot think of a simpler approach.

I may suggest you to simplify your data schema.

If you still want , I can share that approach.

[davidAtayde]

Hi Suvrutt_Gurjar,

Yes, I’d be interested in seeing that approach. My goal is to create a simplified report for our warehouse staff to easily fulfill the bill of materials.

Thank you for your valuable input.

[Suvrutt_Gurjar]

At the outset, it is substantially convoluted approach. I will try to post a better one , possibly with actions that update the columns rather than VCs, if I can come up with. In general your table relationships are so extensive that even applying reference actions that work across tables will be a bit of a challenge.

Please

1. Add a virtual column called say [Total_ComboKit_Quantity] in the Kits_Details table with an expression something like [Quantity]*(ANY([Kit_ID].[ComboKit_Qty]))

2. Delete the earlier recommended column [ComboKit_Qty] from the “Kits_Details” table.

3. Add a virtual column called say [Combokits_Report_Quantity] in the the “Kits_Details” table with an expression something like

SUM(SELECT(Kits_Details[Total_ComboKit_Quantity], AND([Material_ID]=[_THISROW].[Material_ID], [ComboKit_ID]=[_THISROW].[ComboKit_ID])))

4. Add a slice called say “Quantity_Summation_Slice” in the the “Kits_Details” table with a filter expression something like

[ID]=MINROW(“Kits_Details”, “_RowNumber”, AND([ComboKit_ID]=[_THISROW].[ComboKit_ID], [Material_ID]=[_THISROW].[Material_ID]))

5. Add a virtual column called say [Minrow_For_Report] in the “Kits” table with an expression something like

REF_ROWS(“Quantity_Summation_Slice”, “Kit_ID”)

6. Add a virtual column called say [Related_Kits_Details_For_Reports] in the ComboKits table with an expression something like

SPLIT(TEXT([Related Kits][Minrow_For_Report]), " , ")

7. Now the report template can be something like

Material Details

ComboKits

Name: <<[Name]>>

Vehicle_ID: <<[Vehicle_ID].[Model]>>

ComboKits_Details

Material	Qty	Unit
<<Start: [Related_Kits_Details_For_Reports]>><<[Material_ID].[Name]>>	<<[Combokits_Report_Quantity]>>	<<[Material_ID].[Unit]>><>

[davidAtayde]

Hi Suvrutt_Gurjar,

I’m encountering an error while implementing step 3. When attempting to use the [ComboKit_ID] column, I receive an error message.
Do I need to add a reference column for ComboKit_ID?

Thanks in advance for your guidance!

[AleksiAlkio]

Another approach I have used..

When you have 3+1 tables and they are related like ComboKit > Kits > KitMaterials + the original Materials table, you could trigger the Bot from the ComboKit table, but your Start: formula reads data from your Materials table. Then you would not need any additional virtual columns. The template would have something like..

<<Start: SELECT(Materials[ID],IN([ID],SELECT(KitMaterials[MaterialID],[KitID].[ComboKitID]=[_THISROW].[ID])))>>

The result would be a list of IDs from the original Materials table so every row your ComboKit includes, will exist on your template. The qty you can then calculate with..

<<SUM(SELECT(KitMaterials[Qty],[MaterialID]=[_THISROW-1].[ID]))>>

[Suvrutt_Gurjar]

@davidAtayde

Do I need to add a reference column for ComboKit_ID?

Oops. yes , sorry. Please add a virtual column called [ComboKit_ID] in the “Kits_Details” table with an expression something like [Kit_ID].[ComboKit_ID]

[davidAtayde]

I’m having trouble finding the [ComboKit_ID] column in the [Kits] table. Am I missing something, or do I need to create a new [ComboKit_ID] column in the [Kits] table?

[davidAtayde]

Hi AleksiAlkio,

Thank you for your interest in helping me.

I tried to replicate the Start expression considering that the KitMaterials table you mentioned is the same as the Kits_Details table. Assuming this, I got a couple of errors when trying to generate the report. I’ve attached a screenshot:

Is there anything I’m doing wrong or missing?

[AleksiAlkio]

Let me create quickly a sample app for this.

[AleksiAlkio]

Check the app MaterialListTemplate app from https://appsheet.com/portfolio/77079

[davidAtayde]

Hi, I’ve tested the app and it works! I’d just like to add the material name and unit of measure. How can I reference this data in the template?

Here’s the current template:

Material	Qty	Unit
<<Start: SELECT(Materials[ID],IN([ID],SELECT(KitMaterials[MaterialID],[KitID].[ComboKitID]=[_THISROW].[ID])))>>	<<SUM(SELECT(KitMaterials[Qty],[MaterialID]=[_THISROW-1].[ID]))>><>

This was the result with the current template:

Thank you for your help!