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.
What else can I try?