Skip to main content

I am designing a base for tracking participation in a weekly Food Box.

I am trying to organize the data in such a way that I can track the number of people who've selected a certain type of produce for any given Food Box Day.  Ultimately, I'd like to be able to create a pivot table in my dashboard interface along the following lines:

Produce5/1/245/10/24
Basil20
Cilantro11
Cucumbers11
Celery01

I have a table for Food Box Days which includes a record for each date.  Separately, I have a Food Box Participation table, which includes a record for each person signed up for each day.  

In the Food Box Participation table, I am using a multi-select to record each person's produce preferences for each Food Box Day.  The table looks like this:

IDPerson NameFood Box Day (linked)Produce Preferences (Multiselect)
1Bob5/1/24Basil; Cilantro; Cucumbers
2Judy5/1/24Basil
3Jane5/10/24Cilantro; Cucumbers
4David5/10/24Celery

I've tried creating an intermediary Produce Preferences Table, which has a link to the Food Box Participation table and looks like this:

Produce ItemFood Box Participation (linked)Food Box Day (Lookup from Food Box Participation)
Basil1; 25/1/24
Cilantro1; 35/1/24; 5/10/24
Cucumbers1; 35/1/24; 5/10/24
Celery45/10/24

But the pivot table ends up looking like this:

Produce5/1/245/1/24; 5/10/245/10/24
Basil200
Cilantro010
Cucumbers010
Celery001

Any thoughts on how I should proceed?

I think you're going to need a table where each record represents a single item of produce and a single date I'm afraid.  The good news is that creating those records should be fairly easy with repeating groups: https://support.airtable.com/docs/repeating-groups-of-automation-actions

Adam, thank you so much for pointing me in the right direction! I have two follow-up questions. I've altered the "Produce Preferences" table as suggested with a field for produce and a field for date, and an automation as follows, but I'm not sure how to:

  1. Prevent duplicate records from being created in the Produce Preferences table when the Produce Preferences multiselect is updated for a second time (ie user selects a couple options in one step, but then comes back and selects another option in a second step)
  2. Delete records from the Produce Preferences table when an item is un-selected from the Produce Preferences multiselect field in the Food Box Participation table

Here are some screenshots of my automation which may be helpful:

 

 

 

 

Adam, thank you so much for pointing me in the right direction! I have two follow-up questions. I've altered the "Produce Preferences" table as suggested with a field for produce and a field for date, and an automation as follows, but I'm not sure how to:

  1. Prevent duplicate records from being created in the Produce Preferences table when the Produce Preferences multiselect is updated for a second time (ie user selects a couple options in one step, but then comes back and selects another option in a second step)
  2. Delete records from the Produce Preferences table when an item is un-selected from the Produce Preferences multiselect field in the Food Box Participation table

Here are some screenshots of my automation which may be helpful:

 

 

 

 


Ah...with those complications, I would recommend you check out the workflow I created here instead.  It should solve point 1 pretty handily. 

For point 2, I would attempt to solve it by creating a view that only displayed records that were linked to a record from the main data table and making the pivot table display off of that.  With the workflow listed above any deleted preferences should become unlinked and thus the pivot table should display data correctly

Reply


Terms & Conditions