Im trying to make a master list of items where I want to choose and add only certain items to another table.
Do anyone know if thats possible?
That sounds like something that is quite possible in Coda. Do you have a shareable document or any additional information that may help with providing you more tailored guidance?
Two functions that may be useful: Lookup() (https://coda.io/formulas#Lookup) and Filter() (https://coda.io/formulas#Filter)
Another solution may be creating a View; a table that is a subset of the master table (i.e. it has the same data, but only shows certain parts). More information here: https://help.coda.io/organizing-your-doc/setup-best-practices/creating-a-view
View Example:
First I created an example master item table that looked like this:
Next, I created a view of this table. To do this, I hit the + at the top of the screen, and under “Insert View Of”, I chose Master Item Table.
This outputs a clone of the Master Item Table. I renamed this new table “Electronic Items” and added a filter to only show items that have the type electronic. To do this, I hit the filter button, and added the following formula:
Now the new table only shows items from the Master List that are specified as Electronic under the Type column. The new table looks like this:
Hope this helps. Let me know if I can clarify anything further.
I’m trying to do something similar, but with a formula and can’t seem to do it.
Using you example, assume I have a second table and the columns are Item, Type, and Details. I want the Item column to pull from the Master Table and give me a Select List only of the Items of the Type in that row.
e.g.: Type = Electronic, then the Select List of the Item column would show only Computer and Camera.
Yes, that is also doable. Here is a similar setup, but in this case the SecondaryTable is not a view of the MasterTable (but you could do it that way).
I used the names Type1 and Type2 for the “type” columns to avoid confusion in this explanation. First, I made the Type2 column a Select List formatted column of the Type1 column in the MasterTable. This is ensure only types from the Master can be selected. To do that, I used the following formula:
Then, in the SecondaryItem column, I also formatted it as Select List, and used this formula:
Now the selectable options are only ones of the specified type, like so:
1 Like
Hi Joseph
I see how the above could help me.
What im trying to do is basically make a Bill Of Materials from master list.
I need to choose a Type in first field then field 2 will only show me the materials available in the specific chosen type. When I choose the material it then automatically populates fx color an price in field 3 and 4.
Is that possible?
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.
2 Likes
thank you so much. I can’t pinpoint what I did wrong, but your formula worked 
Glad to hear that it worked!
Works absolutely perfect. Thank you so much and sorry It took me a little while to get back.
Two last question relating to this is it possible in the ItemName2 drop down list to see the name not just of Itemname but fx Itemname + ID number?
Im expanded my conditional selection to 3 field in the secondaryTable.
First you choose type, then ItemName and then a color (if there is more colors of a certain material)
How do you do the filter in the price column?
To clarify on the second question, are you asking how to have the price automatically fill-in once the type, name, and color are selected? Or do you want a dropdown menu with price options based on those 3 inputs?
Hi Joseph
Sorry its taken a while to get back.
My question is your version 1 - how to automatically fill in price once, type, name and color are selected.
1 Like
This would be a variation on the example I posted above. In short, you use “AND” to add an additional condition to your filter.
In an example above, the Price column of the Secondary Table has this formula:
Master.Table.Filter(ItemName=ItemName2).Price.First()
Assuming your Master Table has multiple color options per Item (e.g. a Electronic-Computer-Red row and an Electronic-Computer-Blue row), then first, rename the Price column in the Secondary Table to to “Price2” (this is not required but can help minimize confusion for this example). Then, you can modify the above formula in the Price2 column of the secondary table to read:
Master.Table.Filter(ItemName=ItemName2 AND Price2=Price).Price.First()
Effectively, you are looking for rows where the item name matches AND the price matches, and return the Price when there is such a match. You can add additional AND conditions (or OR conditions) as needed.
If I am properly following, this sounds quite possible. In the Master Table, let’s assume there is an additional column called ID with an ID for each item. You could then create another column in the Master Table that combines the ID and ItemName. For example, this new column in the Master Table could be called DisplayName, and it would have the following formula in it:
=Concatenate(ItemName," ",ID)
The Concatenate formula is combining the ItemName and ID together into one output, with a space between the two (please note there is a space between the quotations). You can put a comma, or no space at all if you prefer.
Next, in the Secondary Table’s formula for the dropdown menu, you would modify the formula to reference this new DisplayName column instead of the ItemName column.