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.).

[Steve · Accepted solution]

---

@akkarthe wrote:

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.

---

Correct. What you want cannot be done using inline tables or forms.

[akkarthe]

Thanks Steve, If that is the case I am wondering what would be the best way to achieve this?

[Steve]

Allow quick edit columns in a view

[akkarthe]

Thanks again Steve, but looks like I will not be able to use it as this is a form type and not detail, it is not giving me the option to enable quick edit unfortunately as you said, so thinking of solving this creatively somehow, let's see.

[Steve]

As I said previously, matrix input is not possible in forms.

[akkarthe]

Agreed Steve, I am thinking of adding a button that opens up that particular input in detail view and gathering those info or something like that.

[Steve]

Yep, makes sense to me. You could also have a dashboard view that includes both the detail view and the editable table view so the desktop or tablet user could have both on the screen at the same time.

[akkarthe]

That's a good idea, let me try that out, Thanks 🙂

[Steve]

Note that detail view can also have QuickEdit columns, so you could allow the user to make changes to the details view, too.