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!

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!)

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?

@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).

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…

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.