How to create a dynamic matrix-style input table in AppSheet with row and column combinations?

[akkarthe]

I’m building an app in AppSheet where I need to create a matrix-style input table that allows users to input data dynamically. Specifically, I want the rows to represent one category (e.g., memory options) and the columns to represent another category (e.g., color options). Users should be able to fill in values in the table cells, and these values should be stored properly in the backend.

---

Use Case:

1. Parent Table:

   • Contains a unique identifier (ParentID) and other metadata.
   • Example:
     
     

     ParentID	Name	Region	Type
     PARENT01	Example1	Europe	Type1

     


2. Child Table (Matrix Data):

   • Contains:
     • ParentID (Ref column linking to the parent table).
     • RowValue (e.g., memory options: 64GB, 128GB).
     • ColumnValue (e.g., color options: Red, Blue, Black).
     • Value (the user-entered value for the cell).
   • Example:

     ID	ParentID	RowValue	ColumnValue	Value
     C001	PARENT01	64GB	Red	100
     C002	PARENT01	128GB	Red	200
     C003	PARENT01	256GB	Red	100

3. Expected Behavior:

   • In the parent form, the inline table should display a grid:
     • Rows represent memory options (RowValue).
     • Columns represent color options (ColumnValue).
     • Users can input data into each cell of the grid (e.g., Value).
   • The entered data should be saved to the Child Table (Matrix Data).

---

The Problem:

1. I’m not sure how to configure AppSheet to display a matrix-style table in the parent form where rows and columns dynamically adapt based on parent metadata (e.g., the available memory and color options).
2. I want to allow users to input values directly into the table cells, but AppSheet's default inline table layout doesn’t seem to support this structure.

---

What I’ve Tried:

1. Parent-Child Relationship:

   • Set up a virtual column (e.g., Related MatrixData) in the parent table with the formula:
     REF_ROWS("MatrixData", "ParentID")
   • Configured the child table (MatrixData) with:
     • A ParentID column as a Ref to the parent table.
     • A column for rows (RowValue).
     • A column for columns (ColumnValue).

2. Manual Data Entry:

   • Pre-populated some data in the MatrixData table, but I can only see a flat list of rows in the inline table instead of a matrix-style layout.

3. Dynamic Prepopulation:

   • Attempted to use a Bot to generate all combinations of rows (RowValue) and columns (ColumnValue) based on the parent record, but couldn’t achieve a grid-style layout in the app.

---

What I Need:

1. Matrix-Style Input Table:

   • Guidance on how to configure a table where:
     • Rows represent RowValue (e.g., memory options).
     • Columns represent ColumnValue (e.g., color options).
     • Users can input data for each combination (e.g., Value).

2. Dynamic Prepopulation:

   • A way to prepopulate the child table with all combinations of RowValue and ColumnValue dynamically when a new parent record is created.

3. Example Configuration:

   • If this requires a custom approach (e.g., AppScript or AppSheet Bots), a detailed step-by-step example would be greatly appreciated.

---

Example Data:

• Parent Table:

  ParentID	Name	Region	Type
  PARENT01	Example1	Europe	Type1

• Child Table (Matrix Data):

  ID	ParentID	RowValue	ColumnValue	Value
  C001	PARENT01	64GB	Red	100
  C002	PARENT01	128GB	Red	200
  C003	PARENT01	256GB	Red	100

---

Example Layout in App:

I want the inline table in the parent form to look something like this:

Memory/Colour	Red	Blue	Black
64GB	100	150	120
128GB	200	250	180

Where users can directly enter values into the cells (e.g., 100, 150, etc.).