Hardest question: how to distribute list of items to list of recipients uniquely

[Alexander_O_Str]

Hi guys,

in the spirit of Christimas I’m in charge of a support group for people in need.

We have got a lot of applicants who really need our help to make sure their kids are going to get a gift this year.

I’ve got a ton of gift cards in a single column in excel, and I’d like to distribute all the gift card codes to the recipients.

Now here’s the kicker: the recipients have different amounts of kids, which means they need different amounts of giftcards.

I’m trying to figure out an automation that links a number of times from the recipients sheet to the gift cards code sheet, while never linking to the same code more than once.

The route I’d go would be to make a formula field which counts how many links are in a ‘link to an another record’ field, and if there’s less than the number of children in the family (a manual number field), then it keeps adding a new link from the gift card sheet.

But the last part is not really doable in automations, as I see it…?

Worst case: I’ll do it by hand, but it would be great to know how to solve it in Airtable.

How would you solve it?

Cheers and happy holidays !

[Alexey_Gusev]

Hi,

From your description, it’s not clear - what number is bigger: amount of cards or amount of kids (total). What do you mean by “never linking more that once” - for the same recipient? or in total (means each card can be used only once at all?).

To count number of links, you can use “count” type of field.
I would recommend start from empty links and set filter with condition
“count”<NumOfKids. It’s a minute of work then.
in gifts table, sort by link field, so empty links will be at the top.
now copypaste list of recepients to the gifts table (linked column). it will link all recepient to at least one gift, and those who have 1 kid, will be filtered out
then return to recepients table, and repeat (for 2nd child) then again and again (depending on max kids per recepient in your table)

[Alexander_O_Str]

Number of kids is 2-3 timer bigger than number of recipients.

And the number of kids and gift cards is equal.

I think I understand your solution. Pasting a full column of gift cards (repeatedly) in the recipients sheets, but have the recipients filter out as they reach number of gift cards = number of kids?

That’s pretty clever. Not automated, but clever indeed.

[Alexander_O_Str]

My current solution is to have the gift cards in a separate table and have a column link to it. But in the field have it limit the possible selection of the links to a view (a filter which removes any gift cards which have already been linked).

And then just add links. The recipients list then groups itself by a formula field showing if the number of gift cards matches the number of kids.

That way I can just keep clicking the top row and keep adding gift cards, and the lists will remove themselves as they get filled.

[Alexey_Gusev · Accepted solution]

Yeah, that’s even better, i think. I choose non-automatic solution, because it’s faster and sometimes users hit automation run limit.
But if you want, you can create automation to select some random record to link, for example, when you fill some checkbox near recepient. After “matches condition” trigger, set “Find records” step (where link is empty). The output is array of IDs, but you need single ID, so you should set script step to choose first of array. for example:

[Alexander_O_Str]

Ah, that’s clever ❤️

Thank you !