If I am understanding the question correctly, then yes, that is also workable in Coda. One approach would be an extension of the example above. Here is what my setup looked like:
The MasterTable is the table that I presume has all the information about each item (or material in your case). So each row will have an item, a type, a color, and price. You can add as many additional columns as needed.
I used the same formulas as above for the Type2 and SecondaryItem columns in the SecondaryTable, but I renamed the SecondaryItem colum to ItemName2 for consistency for this example. I also moved the Type2 column to the left to reflect your process (i.e. first select a type, then choose an item/material of that type in the next field).
Note that you can drag and drop columns to rearrange their order.
In the Color column of the Secondary Table, I added the following formula:
To breakdown this formula, it:
- Looks at the MasterTable
- Uses a filter to only show rows where the name of the Item in ItemName2 of the Secondary Table matches the ItemName in the MasterTable.
- If there is a match, pulls the color from the MasterTable and automatically inserts it.
The “.First()” is optional. I like it since it hides the that will otherwise appear in the rows that do not have a matching value.
I repeated this process for the Price column of the SecondaryTable:
The end result is that you can choose a type in the Type2 column, then only matching items/materials appear as selection options in the ItemName2 column, and then the corresponding Color and Price automatically fill-in.
There are other approaches to this. For example, you could likely use the Lookup function to also accomplish this task.