Complex scoring system based on drop down items

Ask the community
#1

OK, with the risk of this being too big a question:

I am working on porting a pretty massive scoring system of grant applications. Each application has let’s say 100 questions, each ranging from 2-15 possible answers (some multiple, some single select) and each answer is worth a certain number of points (integer).

I’m thinking one transparent way to do it without massive scripting (which may be what we end up doing), is to have each answer in a linked table, and in that answer table, have a column that is the point value. Then use roll-up to calculate a score (except where the score has to choose the highest scoring component, which i can do with a formula).

HOWEVER - I don’t want to have a different linked table for each question. SO - I can use the ‘Limit record selection to a view’ option to narrow the options for each question. HOWEVER, this would result in hundreds of views to cover every question in each of four program areas. Maybe I could break up the linked answer tables by program and make it manageable, but is there any other way to narrow select options based on some condition or logic?

Or another way to assign score to drop down answers without using linked records? I guess if could have a SWITCH column for each answer column, but that doubles the table length and makes rollup a manual formula, etc.

Any thoughts appreciated. Thanks!!

Damon

#2

I like the linking records idea that you came up with, but you’re right that it wouldn’t be easy to narrow down the options that the user can choose from. (You could try to use this approach for limiting options, but it is tricky to setup & has limitations such as depending on only one record being incomplete at a time.)

So, probably the easiest & most direct way would be to just create one additional formula field for each question field that figures out the total for that particular question. Then, when you’re done, create a final formula field that adds up all the other formula fields.

Alternatively, you could put the value for each option in parenthesis after the name of each option, and create a JavaScript that parses out the values from all the selected options in all the fields and adds them all up.

Also, you didn’t mention how these applications are getting added into Airtable. Are they coming in through a form?

If so, you could turn to JotForm, which lets you assign a hidden numerical value to each option in a single-select or multi-select field, and it lets you automatically total up the value of each question based on the numerical value of each option.

Then, all of those answers & calculated totals can be brought into Airtable, either through JotForm’s native integration with Airtable, or through Integromat (which lets you parse data in a more advanced manner).

The downside to the JotForm approach is that the calculation of these numerical totals is taking place in JotForm instead of Airtable, so if you make a change in Airtable later, the numerical totals won’t change.

p.s. If you have a budget for this project and you’d like to hire an expert Airtable consultant to help you set any of this up, please feel free to contact me through my website at ScottWorld.com.

#3

Awesome. Thank you! The Java idea is a good one, I may go that route if a front side scoring option ends up seeming too cludgey. I did get my initial scoring sample working using sub table, and some rollup fields using MAX, followed by a simple formula rollup.

But it may end up being more custom formulas for each one. I’d love to do low/no code so that future staff can alter the scoring system as needed w out too much coding.

Most of the scores are researched and assigned by hand by our staff. We get the applications from a really big FormSite form, and there may be some future idea of scoring out of that, but our practice is more hands on/confirm by staff stuff.

Thanks for this!

1 Like