Automating Eligibility Survey Results

[Kirsten_Simmons]

I’ve built a database of grants and funding opportunities, and a survey that asks users the questions to determine their eligibility for the various grants. I have a table that lists the grants and how the various questions have to be answered to determine eligibility.

I’d like to create a table that populates which grants a survey respondent is eligible for when a survey response is submitted. How would I go about doing that? My first thought is that it would be a formula, ie if Survey Q1=X, Survey Q2= Y and Survey Q3 = Z, Awesome Grant = Eligible, Awesome Grant = Not Eligible. But Airtable doesn’t allow formulas across tables. Then I thought an automation, or a junction table, but neither of these seem to be the answer when I dig deeper.

So, rather than struggle for another couple of days, help?

[Rupert_Hoffsch1]

Hi @Kirsten_Simmons, good challenge!

My question would be how you’ve tagged in the grants table which questions have to be answered. I guess this produces some sort of unique string if you would concatenate all values. Maybe you can create the same string for incoming applications and then have an automation run over the grants table to compare the application string with all grant strings?

For more intricate logic a small script would be necessary I guess (partial matches etc.)

Let me know if that helps and if you have questions re. this!

[TheTimeSavingCo]

Hmm, so each survey response needs to be checked against a bunch of grants (records) in the grants table, and you want a list of grants that said survey response is eligible for, is that right?

If so, I think I would:

1. Create a link between the Survey Results and Grants table called “Eligibility Helper” or something
2. Create lookups in the Grants table that will display the relevant fields from the linked Survey Results record
3. Create an automation that would trigger whenever a new survey result was received that would:
   • Find all the records in the Grants table
   • Link the new record to all of the Grants records
4. Now that the Survey Result record is linked up and the lookups are all displaying data, I’d have formula fields that would help me check eligibility, with a final formula field that would output the eligibility
5. Create another link between the Survey Results and Grants table called “Eligible Grants” or something
6. Create an automation that would trigger whenever the formula field mentioned in step 4 is not empty AND the linked Survey Result record’s Eligible Grants field was empty, and its action would be to:
   • Link all the eligible grant records to the triggering record
   • Clear the record’s Eligibility Helper field

(You can also hire me to set it up for you too!)

[Kirsten_Simmons]

Hmm… I think it would have to be a script using this method, because the survey includes questions for all grants, but all grants don’t require the same conditions. Therefore any string coming out of a survey response would contain relevant and non-relevant information for any particular grant. And any time a grant was added or the conditions changed, I’d have to edit the script. Right?

[Jeremy_Oglesby]

@Kirsten_Simmons
What is your reason for wanting the the list of grants the survey respondent is eligible for to be in a new table?

What if you just had those results in the same table with the survey itself? The fields can be made to not show in the survey form itself, and then you can create a view that shows only those “result” fields from the survey (if that’s what you’re getting at).

[Kirsten_Simmons]

Thank you!

So the table I created that includes the grant names, survey questions and required answers would become lookup fields in the table with the information about each grant. Ok… I’m realizing that how I’m thinking about data organization may be part of what’s getting in my way. Let me mull on this one for a bit longer…

[Kirsten_Simmons]

Likely because my understanding of database structure is pretty elementary, and I haven’t stopped of thinking about this all like a giant Excel workbook. Which is what the minimum version of the tool was. Between your comment and Adam’s, I’m starting to see how that mindset is limiting what I can do.