Nov 01, 2022 03:29 PM
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?
Nov 02, 2022 12:34 AM
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!
Nov 02, 2022 01:22 AM
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:
Grantstable called “Eligibility Helper” or something
Grantstable that will display the relevant fields from the linked
Survey Resultrecord 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
Grantstable called “Eligible Grants” or something
Eligible Grantsfield was empty, and its action would be to:
(You can also hire me to set it up for you too!)
Nov 02, 2022 09:03 AM
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?
Nov 02, 2022 09:11 AM
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).
Nov 02, 2022 09:16 AM
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…
Nov 02, 2022 09:19 AM
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.