How to Organize Inventory within Coda

#1

Hello!

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.

Does that make sense?

Thanks in advance!!!

#2

Are one of your concerns the practicality of a very long row in the Master List? If so, using a view may help. Coda provides a good starting point on views here: https://help.coda.io/organizing-your-doc/setup-best-practices/creating-a-view

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:

formula%201

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).

Hope this helps.

1 Like
#3

@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.

#4

@Joseph_B That’s a very clear example! I’m wondering if you might have a suggestion for me on a related problem? It might help others as well.

I’m trying to filter a select list based on the value in the previous column, as per the illustration below.

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?

Thanks, in advance,
John

#5

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 created an extended version of the Menu table:

In the Fix 1 Column, I had the following formula:

menu2

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:

This formula looks at the two new columns, which led to this:

image

You can extend the OR expressions in the Choose Item formula as many times as needed if you have more columns.

You can then hide the “Fix” columns or create a view if you do not want them showing up.

Again, very hacky, but worked for me. Hope this helps.

1 Like
#6

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 :wink:

Cheers,
John

2 Likes
#7

No coffee needed! I’ve gotten enough help from community boards over the years that I’m happy to pay it forward. Good luck.

7 Likes
#8

Awesome, thanks so much! I implemented a modified version of your suggestion and it’s working really well!

2 Likes