Help

Annually recurring data entry forms

Topic Labels: Base design
Solved
Jump to Solution
2130 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Little_Shiva
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello Airtable community! I founded an all-volunteer project to help street cats on the island of Vis, in Croatia, and design a calendar every year to raise money for the project.

Last year was my first year dealing with calendar orders via Airtable, and I thought I had a clever set-up where people could fill out a form to order. It DID work for that one year, but I don’t think I designed the base correctly, because it’s not sustainable year-to-year.

The base is called CALENDARS+ (in case we start to sell any other fundraising items in the future) and all the data I collected lives in a table called 2022 calendar. Each name entered via the form becomes a record, with the person’s preferred language, email address, postal address and the number of calendars they want.

If I do this again for the 2023 calendar, there will be lots of duplicate records, so it’s obviously wrong. I need the people’s data to live in one place so that in following years when they come back to buy another calendar, they can say if they’re a new or returning customer, and if returning and their info is still the same as the previous year, they could just skip to telling us how many calendars they want.

Is this clear? Should I be thinking of this as a DONOR (or customer) table, knowing that for people coming for calendars there will be that added info, as opposed to other donors throughout the year who give one-time donations, usually when they’ve brought a needy cat to our attention?

The first base I built for the project is an expenses and income base, and I have a DONORS table in that base. But since I built the calendar base separately, I have donor overlap, and want to fix this before this next fundraising drive (to sell the 2023 calendars).

1 Solution

Accepted Solutions

You would have several work arounds here.

  1. Use Airtable forms but in a separate table. Then use automations where it can check if the record (email) exists in the DONORS table, and if it doesn’t then it would create a new record, if it does then it would link the order to the existing record. If you do that, you need to have email_formatted field, where you use a simple formula, LOWER({Email Field}) to make sure that the formatting doesn’t mess up the search (i.e.: if someone puts the first letter as uppercase while it is all in lower case in the DONORS table)

  2. You can do the same thing with any form (Typeform for example) then use ZAPIER to automate it the same way. This way your data won’t be duplicated in Airtable

See Solution in Thread

3 Replies 3

Hi @Little_Shiva

You did think correctly by having a separate table for DONORS. To use the existing one, you can duplicate it to another table, link both tables, add something like a Product table to know which calendar they ordered.

However, keep in mind that if you use the Airtable forms to fill the data they will be able to see all DONORS which is not something you would want

Exactly: they can’t see all donors.

You would have several work arounds here.

  1. Use Airtable forms but in a separate table. Then use automations where it can check if the record (email) exists in the DONORS table, and if it doesn’t then it would create a new record, if it does then it would link the order to the existing record. If you do that, you need to have email_formatted field, where you use a simple formula, LOWER({Email Field}) to make sure that the formatting doesn’t mess up the search (i.e.: if someone puts the first letter as uppercase while it is all in lower case in the DONORS table)

  2. You can do the same thing with any form (Typeform for example) then use ZAPIER to automate it the same way. This way your data won’t be duplicated in Airtable