Hi. I’m having trouble creating lists with multiple references and having the data entered in a table to update in another table.
I have a table called Model ID Numbers with two columns in the text format. The display column is called Model and this is where I store product codes. The other column is called Parts and this is where I store product codes for items that are made from multiple parts. For example if I have a wireless keyboard with mouse, I have the value K254 entered in the Model column and K245M, K245K entered in the Parts column.
This is all good. Now I want to reference that data in another table where I also add serial numbers and any other details about my individual products. I also want to use that table to add new products to my inventory and this is where I’m having trouble.
My other table is called Equipment and has 3 columns: Model (which is a lookup from my previous table), Parts(which is a select list with formulas) and Serial (which is a text format column).
Whenever I try to add new products via the Equipment table, the new entry for Model gets created in the Model ID Numbers table, but if I add Parts, the new entry isn’t on the same row and has it’s own new separate row. I tried various formulas for the Parts column in the Equipment table, but I can’t seem to get any new entries to be on the same row in the Model ID Numbers table.
I tried having it as a select list with the selectable option [Model ID Numbers].[Parts]. This allows me to select any previous entries and create new ones. But the new ones are created on their own new row in the Model ID Numbers table.
I tried having it as a select list with thisRow.[Model].[Parts] and I get the same problem as above.
I tried having it as a lookup with the formula thisRow.[Model].[Parts] which only displays already created entries and doesn’t allow me to make new ones.
I tried having it as a selects list with selectable option [Model ID Numbers].filter(thisRow.[Model]=[Model]) and none of the previous entries show up in the list, any new entries I create go on their own row and under the Model column.
I tried having it as selects list with selectable option [Model ID Numbers].filter(thisRow.[Model]=[Model])[Parts] and this doesn’t work.
I think found a workaround by breaking the Model ID Numbers table into two so that I have a table where Model is the display column and another table where Parts is the display column and Model is lookup. I would have preferred not to do that because to me it seems cleaner to keep them all in one table.
I think you can do this all in one table, it’s just a matter of switching the logic of the table setup.
Currently is sounds like you have the Group first and then you break the group into Parts. If you switch the logic to listing every Part first, then adding the Group to those parts as an attribute (just a label in it’s own column), you’ll free up a lot more that you can do with the data.
By starting with the data broken up into the smallest parts first, you ensure that each individual part has its own row and can be used anywhere else in any way you need.
Part :: Group :: Color :: Size
K254M :: K254 :: Black :: Medium
K254K :: K254 :: Black :: Large
You could also create a second table that has the Group names and use those as Lookups in the first table. This would let you add descriptions or explanations of each group. And, if you change the name in the future, it will change everywhere it’s referenced in that Coda document. It may also be easier to sort and filter data later on by keeping all names consistent.
Coda also has a Grouping feature that lets you group rows by a particular column. In the layout I listed above, try this with the “Group” column by choosing “Group display Left”.
I am considering whether or not I need to index my data using various tables and lookups (this is my current approach). I decided on this approach because I want to be able to create user friendly sections for searching and filtering based on certain attributes. I also want to force users to enter new data in a logical way to avoid errors.
But as you suggested, the grouping feature might make most of my lookup tables redundant. Maybe I’m just being too thorough…
I like the idea about starting with the smallest component, but in my case the smallest part isn’t always true (is optional), so the second to smallest is my main attribute.
That makes sense and it does take some playing around with tables and attributes to land on what will work best for you. I jumped back and forth on the Equipment Inventory document the same way, but landed on a strategy that, so far, has let me expand and develop from.
The main Equipment table under Equipment and Attributes compiles all data for each piece of equipment we have, bikes, boats, etc. Many of the attributes are broken out into their own table and then I use lookups to pull those into the Equipment table. This keeps the attributes consistent throughout the doc (ex: color is “Black”, not black, or BLACK, or BLK, or anything else.).
The rest of the document is just views of the Equipment table with the relevant columns marked as visible for each particular view. This keeps all information for a particular piece of equipment in one place. I’m not losing track of which notes are in which table and I can still limit the view to only the relevant information for each section.
My next iteration is including groups of pieces of equipment, which sounds more like what you’re needing to do. I still went for the parts first approach because I want to know if I am swapping out a particular part too often on a group of items. Maybe we keep losing the stove in our cook kits for example. Being able to add another part and assign it to the kit means the kit keeps flowing and I’m not stuck creating entirely new entries for new kits just for repairs or changes in smaller parts or options.
Visually, I think of it like Lego sets. Some pieces just work well together, but if I glue those pieces together in groups to begin with, I limit what I can do with the pieces later.
Oh I didn’t realise you made that template That’s what I used as inspiration when I started. I created an Equipment table as well.
Because the range of things I have in my inventory, some are in kit form while some aren’t. But because they’re mostly electronics, everything has a code and serial. Right now I have a lookup column for parts model numbers, a text column for extras (some things come with a carry bag or instructions which don’t have codes), and a text column for things that are missing from the kit. But I also have things like cables that I listed on their own and named their storage location “with device” and made a note of what that device is…
I think my approach is a bit messy because some kits came together from the start so I never considered their parts as individual, while I had to create other kits so I consider the cables example as separate items.
That’s what I used as inspiration when I started. I created an Equipment table as well.