Dynamic report generation in AppSheet: Issue with start expression

[davidAtayde]

Following up on the development of an AppSheet app to manage the creation of ComboKits from existing kits. The goal is to generate a PDF report listing all the materials required for each ComboKit.

My table structure is as follows:

ComboKits: Contains general information about each ComboKit.

ComboKits_Details: Contains details of the kits that make up the ComboKit (and how many of each kit).

Kits: Contains general details of each kit.

KitMaterials: Contains details of the materials that make up the kit (and how much of each material).

Materials: Contains the list of materials.

Table relationships:

1. A ComboKit can contain one or more ComboKit_details.
2. A kit is associated with one or more KitMaterials.
3. A Kit detail contains a material and the quantity of that material.

Here’s a GIF showing a walkthrough of the current app:

This other table in a separate spreadsheet contains the reference to the PDFs generated for each ComboKit, so that I can display them in the ComboKits view.

This is the expected template for the report:

The expected workflow is as follows:

1. The user searches in a view of the ComboKits table to see if the ComboKit they are interested in already exists or if they need to create a new one.
2. If the ComboKit already exists, they only need to download the PDF containing the list of materials for the ComboKit.
3. If the ComboKit does not exist, the user must create it by choosing one or more of the existing kits.
4. When creating or updating the ComboKit, as long as its status is “Released”, the bot will generate the PDF for viewing or downloading.

My problem is that I can’t build the correct start expression to populate the report with the list of materials from the Materials table, especially when a ComboKit is composed of several levels of nested kits.

In the report, the table listing materials should display the total sum of each material found in the different KitMaterials, ensuring that the same material does not appear more than once in the table. Here’s an example of the desired output.

Could you please help me with this?

[davidAtayde]

An update:

I’ve successfully created a start expression that enumerates unique materials and their corresponding IDs. However, I’m struggling to calculate the total quantity accurately. To do this, I need to multiply the quantity from the ComboKit_Details table by the quantity from the KitMaterials table, and then sum up the quantities for identical materials.

Any ideas on how to achieve this?

Combo Kit: <<[Name]>>

MATERIALS

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

[Steve]

I can’t find a way to do this.

[TeeSee1]

Like @Steve says, I neither can find an expression to do this. One workaround to this is to introduce a ‘supplementary’ table attached to the ComboKits table that directly lists related materials and quantities pre- calculated.

e.i. [ID],[ref to ComboKits], [ref to Kits_Details], [ref to Materials], [Quantity].

You can populate this whenever you enter a ComboKits_Details record either by form saved event or a bot using a loop that goes over the Kits_Details per Kit (= ComboKits_Details)

This can then easily be formatted (SUM(Select…)) to get the results you want.

A sample output

DATA

TEMPLATE

Output

[davidAtayde]

Hi TeeSee1,

Thank you very much for your time and for providing such a helpful solution. I followed your suggestion of creating a supplementary table, and it worked perfectly!

I’d also like to thank @Steve for taking the time to look at my post. Your input was also greatly appreciated.

Just one question: How can I integrate the operation of multiplying the Qty value of the kit in the ComboKit_Details table [LV1Ds equivalent] by the Qty value of the material in the KitMaterials table [LV2Ds equivalent] into the summary list of materials?

Best regards,

David

[TeeSee1]

@davidAtayde

How can I integrate the operation of multiplying the Qty value of the kit in the ComboKit_Details table [LV1Ds equivalent] by the Qty value of the material in the KitMaterials table [LV2Ds equivalent] into the summary list of materials?

LV1SUP table already has quantities that are [LV1D].[Qty] (Kit quantity) and [LV2D].[Qty] (Kit Material Quantity) multiplied when rows were created using actions.