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.

[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!

[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]>><<END>>

[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]>><<END>>

[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!

[Suvrutt_Gurjar]

---

@davidAtayde wrote:

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?

[Suvrutt_Gurjar]

---

@davidAtayde wrote:

'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?

---

Yes, please add a column called [ComboKit_ID] in the "Kits" table with an expression something like [Related ComboKits_Details][ComboKit_ID]

Its settings should be as follows

I am aware Aleksi is guiding you. I am his solution will be very elegant. 

I am responding because I missed your the above query yesterday. So just thought of responding.

[davidAtayde]

Hi Suvrutt_Gurjar,

Thank you for your detailed response and suggestion. I'm learning a lot from both your response and Aleksi's guidance.

I think I'll follow Aleksi's suggestion for now, but I'll be sure to come back to your solution if I need it. Your input is invaluable.

Thank you again for your help!

[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]))>>

[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 · Accepted solution]

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]))>><<END>>

This was the result with the current template:

Thank you for your help!

[AleksiAlkio]

In your material column and header row, just write <<[Material]>> after your <<Start: SELECT(....)>> formula. Same with the <<[Unit]>> just before the <<End>>

EDIT: You need to move the <<End>> to the last (3rd) column.

[davidAtayde]

Hi,

The app works great. How can I implement a many-to-many relationship between kits and comboKits? What I'm looking for in the interface is for the end user to be able to only create comboKits and assign one or more of the existing kits (previously created by another user) so that new kits don't have to be generated for each new comboKit.

Thank you very much for your support.

[AleksiAlkio]

Because you need the ability to give the quantity for your related Kits, you need to have a table between ComboKit and Kits.

[davidAtayde]

Hi,

I've created the ComboKit_Details table:

I assume I need to create some virtual columns to relate the Materials table to the ComboKit ID, allowing me to iterate through the materials in the report.

Additionally, I think I need to create a virtual column to calculate the total sum of materials in the report. This is because in the ComboKit_Details table, the user can choose the quantity of each Kit to add to the ComboKit.

How can I achieve both of these results?

I really appreciate your time and support so far.