Pivot Table with multiple linked fields

[rachelkm2]

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:

Produce	5/1/24	5/10/24
Basil	2	0
Cilantro	1	1
Cucumbers	1	1
Celery	0	1

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:

ID	Person Name	Food Box Day (linked)	Produce Preferences (Multiselect)
1	Bob	5/1/24	Basil; Cilantro; Cucumbers
2	Judy	5/1/24	Basil
3	Jane	5/10/24	Cilantro; Cucumbers
4	David	5/10/24	Celery

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

Produce Item	Food Box Participation (linked)	Food Box Day (Lookup from Food Box Participation)
Basil	1; 2	5/1/24
Cilantro	1; 3	5/1/24; 5/10/24
Cucumbers	1; 3	5/1/24; 5/10/24
Celery	4	5/10/24

But the pivot table ends up looking like this:

Produce	5/1/24	5/1/24; 5/10/24	5/10/24
Basil	2	0	0
Cilantro	0	1	0
Cucumbers	0	1	0
Celery	0	0	1

Any thoughts on how I should proceed?

[TheTimeSavingCo]

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

[rachelkm2]

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:

 

 

 

[TheTimeSavingCo]

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