I have something I’m having trouble wrapping my head around and thought someone might be able to help. I am trying to keep a “Master List” with the following key items:
People Name + People Demographics
Devices
**iPads
**Miscellaneous Devices
All of the devices are “owned” by the organization, and may be moved amongst people (e.g. if they leave the organization, one gets broken, etc.). There’s USUALLY a 1:1 model (i.e. one person gets one device for their time at the organization), but there are “loaner” devices I need to keep track of assignments too (e.g. new person needs device on day 1). I’m trying to figure out how to “parse” the data in order to be able to “pair” a device with a person and have everything change quickly. I know coda can probably do this better than a spreadsheet, but I can’t figure out how to organize it upfront.
Right now I’m just doing REALLY long rows with the following key details (and just showing different views of the master table):
Person Name
Person Demographics (id#, site location, etc.)
Device ID
Device name, description, details, etc.
I’d really like to be able to “pair” device ID/details with person name/demographics, and allow for easy change in case a device needs to change hands.
To your larger question, there are likely several ways to approach this organization. One idea is to have a “Person” table and a “Device” table. On the Person table, you can have a device column that uses the “Select list” format (i.e. the dropdown format). This way, only devices from a given list can be selected. In this case, the given list of devices can be the devices in your Device table. Likewise, the Device table will look at the Person table and automatically fill-in the current user. Pictures to help explain:
In the Person table, the CurrentItems column has a Select list format, and uses the following formula in the select list:
This means when you go to pick a device for a person, only the “unused” devices show up in the dropdown, when combined with the following formula on the Device table in the User column:
This formula also means when you assign a device via the Person table, it automatically shows up on the Device table (yay for consistency).
This may be the optimal way for you. For example, you may want to be able to assign devices on the Device table, in which case you could do all of this in reverse.
This use-case is an example where Coda beats spreadsheets - if you want a dropdown list that can remove items as they are used. That is not easy to do on Sheets or Excel (I’ve tried).
@Benn_Bennett great question. You could also consider adding a “status” column to the Device table that @Joseph_B mentioned. The status could be type “single select” and you could include options such as available, assigned, on-loan, etc. That also opens up the possibility for some cool Kanban views based on the status of the item.
While I am able to do that when the value in the previous column is one item, I can’t figure out how to filter the list when the previous column contains multiple items. In my example, I think it’s because the value “Vegetables, Fruits” in the Menu table isn’t matching anything in the Food Group column of the Foods table. In English, I want the “Choose Item” formula to express, “This select list should include items found in Vegetables OR in Fruits.”
Am I making sense? If so, do you have a suggestion on how I might do this?
I did follow what you are asking. I re-created the tables/formulas, and see what you mean. I found a hacky workaround, which I’ll put below, but others may have a better idea. Also, maybe the team at Coda wants this implemented in a more direct way.
I repeated this formula in the Fix2 column, but replaced the 1 with a 2. This pattern could be repeated to account for the total number of Food Groups that may be possible. Effectively, this broke out all the groups in the Select Group column into individual parts by grabbing the first group, then the second group, etc.
I then changed the formula in Choose Item to this:
Wow, thanks so much. Can I buy you some coffee? That’s a pretty extensive answer and I appreciate your time and thoughtfulness.
It would be great to get a more direct implementation from the Coda team – in my actual, real-world table, I’d need a maximum of ten “fix” columns. Realistically, the number could reach three, so in the meantime, I’ll give this a trial. Thank goodness for the ability to hide columns
