Even editors pay full freight?


#1

I’m a big fan of Airtable, and I think I have convinced my boat club to use it as our place for member data, sail plans, etc. But, our club has about 20 people who have a task of reviewing and approving of volunteer work chits. I can easily see how I can make forms for our members to submit their chits, but, short of adding additional 20 billable subscriptions, I’m not really sure how I can give each of the chit reviewers the opportunity to add an “approved” flag to the chit records. As I understand it, my options are:

  1. each of the 20 people is an Editor, but this would increase our yearly costs by about 18x.
  2. each of the 20 people is a commenter and only comments on records they want to approve, but this seems very cludgy at best.

Am I missing some other option?


#2

I’m pretty sure option #2 would cost you the same as option #1 anyways.

There is a workaround you can use that will allow you to have your 20 person task group work as “Read Only” collaborators (thus free of charge), and submit their “Approvals” via forms. It’s not as straightforward as checking a checkbox, but it will save you loads of money…

Create a new table called Managers or some-such. Each record in this table will represent one of your 20 club managers. I’d suggest having a field for {First Name}, {Last Name}, perhaps {Middle Initial}, and then concatenating those into the Primary field (it always defaults to the field title {Name}). You can add fields for any other info you might want to track for them, like address, date joined, a checkbox for {Retire} or {Inactive} or something like that.

Create another new table called Approvals. Each record in this table will represent a club manager’s approval of a volunteer chit. This table will primarily need 2 fields – a “Linked to another record” field pointing at the Volunteer Chits table, and a “Link to another record” field pointing at the Managers table (for both of them, uncheck the “Allow multiple linked records” option – we want each Approval record to be only associated with 1 Volunteer Chit and 1 Manager). You could add other fields, if desired, such as a “Created time” field that will automatically capture the date/time that the approval was submitted; and you could perhaps use a formula in the primary field to pull in the Manager’s name and the Date.

Next, create a form view for this Approvals table, so that your managers can submit these Approvals via form.

At this point, you have a functional system where your managers can have “Read Only” access (which does not cost anything) to the base and can view all the Volunteer Chits. They can then open up the form and submit an Approval for any given chit by selecting the Volunteer Chit record from the list that they want to approve, and selecting their name from the list of Managers in the form.

There may be a couple more things you could do to further enhance this system – message back if you think this system would work for you, and you’d like any further help with setting that up.


#3

Thanks very much. I’m going to give it a try, but I see a couple problems with this:
Filtering the records: Each approver would want to see only their records, but there doesn’t seem to be a way to filter the selections. I could of course grant them access to all pending chits and use a naming convention to help find appropriate records, but that seems quite sub-optimal and an odd limitation for a database.

Removing previously approved records: Once a chit is approved, it really shouldn’t be available in the record selector. Again, a filter on the selector seems like a natural way to go, but that’s not available. I guess this filter would need to be able to display the records in the chit table that do not have a matching record in the chit approvals table. Seems like we are doing a lot of extra work to get navigate our way around a billing policy, no?


#4

I am trying to figure out a very similar problem to you, in this situation there are a hundred trained event volunteers for an ambulance service who offer their time for free a few times a year to assist with ambulance attendance at various events around the city.

I want them to be able to look online and select an event they are available for, Airtable seems to be perfect for this application but obviously the ambulance service cannot buy a hundred usages of the database for this occasional requirement. I too am trying to work out how to make this work with forms so they can look up events and dates on Airtable and volunteer through the form. It seems like it should be doable though I am not yet sure how a volunteer can trigger the form from within Airtable.


#5

@Mark_Lewis - I can help with that. I’ll post a solution for that later today.


#6

Alright, so in your “Events” table, you can create a Formula field that concatenates a clickable URL for your form, with that event pre-filled into the form.

The thing you have to be careful of is making sure that the URL you build has no special characters (un-escaped characters) that URL’s can’t handle. See here for a list of those and an explanation.

When I have done this in my own bases, I have kept it simple by making sure that the “Name” field (or whatever you rename the “primary” field to) of the records I want to auto-fill into a form only contain letters, numbers, and dashes/underscores. I use the dashes/underscores instead of spaces. This way I don’t have to escape any characters in the URL’s. But if you need to use spaces, we can escape those easily enough.

Here’s what that formula would look like:

'<< your form share URL here >>'
&
'?prefill_Event='
&
SUBSTITUTE(Name, " ", "%20")

That construction assumes that the field in your form that the event links to is named “Event”, and that the primary field in your “Events” table is named “Name”. And that formula escapes spaces by replacing any that exist in the “Name” with %20.

Once that formula field is in place, make it visible in the View that you share to your volunteers. They can find an Event, and then click that link to open a Volunteer form with the event they clicked from automatically filled into the form, so it’s almost like it’s a unique form just for that event (the only thing that makes that not the case is that they can still delete the Event that is pre-filled and replace it with another).

Let me know if you need any other help with that.


#7

Thanks! This looks promising. I will give it a try. So, I’m going to end up with a table of submitted chits and another, near-identical table with all the same fields and an additional approval field. That’s not too painful. I like the fact that the approver will have the opportunity to edit any of the fields.

However, it would be much better and easier if the billing policy was more suited to my needs. Rather than having them approve each record individually, I’d love to be able to show the approvers a list of items that require their approval and have them approve/edit the pending items in a table view. Collectively, they will only generate about 500 records per year, performing the task once per month. So, it’s really not worth it to purchase 20 seats in these circumstances. I wish that, there were some other way of measuring usage such as records created/viewed/edited.


#8

Thank you so much for the help Jeremy, I really appreciate it. Can I confirm with you that view only users of the solution will be able to click on the record.


#9

They will be able to click the link to open the form… is that what you are asking?


#10

Yes that is what I meant to ask. Thank you once again.