Skip to main content

Hi all



I’ve had Airtable for only a few days and I’m desperately trying to replicate what I’ve got in Excel, however I’m a bit stuck and hoping for some help please. It’s essentially a points lookup.



Requirements





  • Users must answer 4 yes/no questions (represented by 4 columns/fields)


  • Each answer of Yes will add the number of points shown in table below, with the total points across the 4 fields attributed to a priority


  • Users should only see the below 5 columns - any other fields created and used to calculate/lookup to determine the priority should not be visible to the user


  • The priority should not be able to be edited by the user - this defeats the whole purpose






In the above example, user has answered Yes to B so 10 points has been assigned in total and this results in a medium priority being assigned.





Bonus Requirement



A desire but not an absolute requirement is that only once the user has entered Yes or No in all 4 columns should a priority display.



Summary



I managed to do this all relatively easily and quickly in Excel including the above bonus requirement. Happy to share how I did it in Excel but thought that might complicate or misdirect if I dumped it all here. To be clear, I’m extremely new to Airtable and not even sure if permissions will support such a use case.



Any help would be greatly appreciated.



Thanks very much, Brendan

I can think of a number of ways to achieve this in Airtable, but…



The most important thing for you to know is that the only way for users to modify data in your base is if they have creator or editor-level permission, and you have to pay for those accounts (once you max out your 5 free accounts).



There is one way “around” this restriction, which is that you can make Airtable Forms that anonymous users can use to enter a new row in a base. However, you will not be able to display the result to the user on the form.



You might be able to solve this by use of third-party integrations but the possibilities there are too numerous to explore in a post here. Here’s how I would build the base if you don’t care about paying for each user, or if you are OK with using a form for anonymous users to enter their responses:



Create a “Points” column of type “Formula”


The formula will look like this:



IF(A="Yes",10,0) + IF(B="Yes",10,0) + IF(C="Yes",50,0) + IF(D="Yes",50,0)


Create a “Priority” column of type “Formula”


The formula will look like this:



IF(Points<10,


  "Low",


  IF(Points<50,


    "Medium",


    IF(Points<100,


      "High",


      "Very High"


    )


  )


)

Thank you so much @Nathaniel_Granor , that worked! So easy when you know how!



I particularly appreciate you mentioning permissions as this is what I’m trying to get my head around. Costs are not really a problem in this regard, I am testing a proof of concept to gather requirements from stakeholders in large projects for which the business will pay.



The aim is that somebody like myself constructs the tables and fields as an admin, then sends it out for others to fill out (able to add and edit rows). I need to explore this more. Thanks again, it really is greatly appreciated.

Reply


Terms & Conditions