Multiple Selections of an Identical Linked Record - Help!

[Michael_Reed]

Hi everyone, I’m new to Airtable. I’m hoping I’m missing something really obvious here!

When linking to a record in another table, AT gives you the option select multiple records. However, it doesn’t let you chose the same record twice. Why is this, and is there any easy way around it?

For context, I’m trying to set up a base for a non-profit diaper bank. Diapers come in about 8 sizes. Sometimes, families have two or more children who wear the identical size of diaper (e.g. twins). Currently, the base will let me use a single field [“Diaper Bundles”] to indicate that a family received size 1 diapers and size 5 diapers, for example. But if I want to indicate that they received multiple qualities of size 1 diapers, as well as size 5 diapers, things get complicated in a hurry!

Any suggestions here? I’d love some advice!

[Celeste_Bancos · Accepted solution]

A number of different solutions are coming to mind:

1. You could create more diaper bundle records that say “Size 1 diapers (2 pack)”, “Size 2 diapers (2 pack)”, etc. Maybe add a quantity field to the diaper bundle table.

2. You can duplicate your “Size 1 Diaper” record and just have several identical copies of it. If you have three records on your diaper table, you can link all three to the same family on your appointments tab. Airtable will keep track of them even if they are all named the same thing.

3. This is probably overkill, but you could create a new diaper record for each pack given to a family. So rather than your diaper table being a list of the types of diapers available, it would be a list of the actual packs of diapers given out. (It’s probably more trouble than it’s worth to do it this way).

[Celeste_Bancos]

The link field allows you to create a link between one record and another record (or multiple other records). But once you link a record, it’s already linked. You can’t link it again because the Airtable designers didn’t make a way for the link field to track “double” links. In Airtable, two records are either linked or not linked.

A link field that allows multiple records is a simple version of a many-to-many relationship. Each family can connect to many different types of diapers and each diaper can connect to many different families. But if you want to get more complex and add information that is specific to the relationship between two records (vs. information that is just specific to one record or the other) you’d have to create a junction table. You can read more about many-to-many relationships and junction tables here: https://support.airtable.com/hc/en-us/articles/218734758-Airtable-s-guide-to-many-to-many-relationsh...

You could create a junction table called “Diapers/Families” to track this many-to-many relationship in more detail. The Diapers/Families table would have a link field for families and a link field for diapers. Every time a specific family gets a specific type of diaper, you’d have to add a new record to this table. (This is cumbersome to maintain by hand, so probably not a good solution in your case.) If you want to track the quantity of diaper packs of “this specific size that were given to this specific family” it would go on this junction table.

But my recommendation would be to go with option one or two from my first post. I think either of those will do the job without too much hassle.

[Michael_Reed]

This is amazing! Thank you! I can’t believe I didn’t think of options 1 and 2. I think that did it!

I have one more question that goes beyond the scope of my original question. Do you have any tips about generating reports? I’ve taken your advice, and went with option 2, and then used a formula to run a count of the number of identical linked records in the [Diaper Bundles] field. (I.e. a count of how many bundles of each size per appointment).

Now I’d love to have an easy way to pull up stats based on different conditions. For example, how many bundles of each size did we give away in a given day, or month, or year. How many people attended in July 2020? Etc. Should I manage that by setting up different views, or blocks, or pivot tables? I’d love any advice.

Here’s a copy of my current Base, in case you’d like to take a look!

Airtable

Diaper Bank Test - Airtable

Explore the "Diaper Bank Test" base on Airtable.

[Celeste_Bancos]

Views are a good solution (and great for sharing with others) though they can get unwieldy if you have a lot of them.

If you have the Pro plan, you can use a pivot table block.

Another option if you want to see multiple months in the same view but still get the totals per month is to make a new field called Month with a formula that takes your date field and formats it with just the year and month (e.g. "DATETIME_FORMAT(Date, “YYYY-MM”).

Then you can group by Month and then by Diaper Size to get your breakdown. At the top of each group, it’ll give you a summary to get the total or the average or whichever kind of summary you pick for that field.

[Michael_Reed]

I think I’ll play around with all of the above, but this makes sense. Thanks very much for all the help!