Product usage; how to implement this solution for member management.

[JeffJohnVol]

I'm new and have watched a lot of excellent tutorials that seems to indicate that I should be able to do what I want, but I wanted to ask the user-base to confirm, and perhaps to add any "to do that, do this" comments. I'm on the free plan, but if i can get this to work, I definitely want to upgrade to the paid plan and support the service, but I've been so bad about getting started on projects and then get distracted by some other shiny object and abandon it.  But this time I'm hoping it's different, as the product seems pretty simple to use.

I help run a local makerspace, and we have members that pay dues to get access to our space that has tools and stuff.  People apply for membership, and our rotating board of directors (elected by the membership) approve the members.

Currently, our process is:

1. member sends google form to apply.
2. the board members get an email, and they reply all with "aye" or "nay".
3. If we get 2 "aye's",  I manually send an email to the prospect to tell them to complete the process (setting up payment, signing waiver).
4. I also manually add their data to our spreadsheet.
5. I add their email to our googlegroup email list.
6. I then confirm payment, meet with them and set up their access.

How I think it'd work in airtable:

1. I create a "member" table that has identifying information including a member type that may be REGULAR,LEAD,BOARDMEMBER,OFFICER,PROSPECT.
2. I create form that populates an application record.
3. I create an automation that takes the particulars from the application record and adds it to the member table, with the member type of PROSPECT.
4. I create an automation on when new row added to Member table and row is prospect to email a new form  to each member that has a type of BOARDMEMBER that has particulars of the prospect and they can reply with the "aye" or "nay" option box. This populates the APPROVAL table.
5. Another view of APPROVAL table that a count of "ayes" per prospect has an automation that if a row gets to be 2 or more an email goes out to the prospect to complete the payment process (which is described in a link to our wiki page). 
6. Another automation (like #5) that adds a row of members to verify payment with a status of "Unverified".
7. I would periodically check the list from #6 to see if I need to verify payment, I assume later I could use zapier or the like to verify somehow automatically.

Does that make sense?

And I assume people can submit applications without having an airtable account?  And my board members don't have to have an account either?

[Hannah_Wiginton]

Hey Jeff!

Sounds like a great use for Airtable with a couple of caveats.

You're correct in having a Members table, then having a Select field with the Type. I guess technically not everyone is a Member in this table yet, but for what you need, works great.

Yes to the Form requesting a Prospects info. By default, a Form adds the data to the table.

Your Automation would then be to 'Find records' with Board Member Type, then Email that info.

The tricky part here is replying by email. There's not a specific way to reply to an email and have it update the database. Here's a workaround, but not super simple.

To edit record data, someone has to be a User and I'm guessing you don't want to pay for every board member to be a user. To help with this, you might look at a tool like Fillout that integrates with Airtable and allows editing.

You might ask this question in the BuiltOnAir group or Facebook group to see if you can get some helpful ideas.

[TheTimeSavingCo · Accepted solution]

It seems like the data between those APPLICATIONS and MEMBERS is pretty similar, so I think I'd have one table that contained that data and use views to filter data out based on the workflows instead, that way you wouldn't need to have the automation from point 3.  For example, I'd set the default "Member Type" value to PROSPECT, that way any form submissions would have that set by default.  If I were creating stuff manually I could just change that on the fly, does that make sense?

---
re: Another view of APPROVAL table that a count of "ayes" per prospect has an automation that if a row gets to be 2 or more an email goes out to the prospect to complete the payment process (which is described in a link to our wiki page). 

I'd suggest using a conditional Count field for this in your "Members" table instead, and you could trigger your automation that sends the email to the prospect from there:

You could try doing it from the APPROVAL too, but given that there'd be multiple records for each applicant you'd have to have some additional logic to only send one email

===
re: Another automation (like #5) that adds a row of members to verify payment with a status of "Unverified".

Yeah so this would be its own table?  I'm thinking there could be multiple payments for the same member, and so having it as its own table and linked to the MEMBERs table would be good

---
re: I would periodically check the list from #6 to see if I need to verify payment, I assume later I could use zapier or the like to verify somehow automatically.

Yeah, depends on your logic on verifying stuff though.  Might be possible for you to just set a formula field to check whether it's past their payment due date and their payment status is "Unpaid" or whatever, and have it automatically email them and BCC you

===
re: And I assume people can submit applications without having an airtable account? And my board members don't have to have an account either?

Seems like both the application submission and board member stuff is done via forms, so they don't need accounts

===
Here's a link to the base I set up, and in it I also threw together the prefilled form link that you can send to your board members:
Example link: https://airtable.com/app2IgfYV04zGROFj/shrFbbcNuLEOSJwJF?prefill_Members=recmtznKljLfrvd2h

[JeffJohnVol]

Thanks Hannah, great suggestions.  I've been watching some vids on FILLOUT, it seems a great item to sit on top.

[JeffJohnVol]

Thanks Adam.  Everything you say makes perfect sense.  I was considering the separate members table because there might be some data acquired like "are you willing to help clean?" that we may not want to carry forward in the member table, but obviously it'd cause some duplication.

I'd suggest using a conditional Count field for this in your "Members" table instead, and you could trigger your automation that sends the email to the prospect from there:

Are you saying to have a different table that has the approvals, and roll it up into the members table for the count?  If so, that makes sense. I know how to do that in sql databases, I'll research how to do that here. And if I understand it correctly, and there is a formula that gets the count, do I need to worry about performance when the tables get larger? Once the member is approved, this count means nothing and I'd hate to have cycles wasted on it.

Yeah so this would be its own table? I'm thinking there could be multiple payments for the same member, and so having it as its own table and linked to the MEMBERs table would be good

Yes, having a payments table would be beneficial if I can use zapier to pull in paypal transactions (we do all invoicing with paypal recurring payments). This would help with member payment reconciliation later, which I currently do with excel VBA.

I must say, the responses from the community I've received so far is impressive.

Thanks Adam!

Jeff

[TheTimeSavingCo]

re: Are you saying to have a different table that has the approvals, and roll it up into the members table for the count?

Yeap!  Each form submission is going to result in a new record being created so this is how I usually like to do it

---
re: And if I understand it correctly, and there is a formula that gets the count, do I need to worry about performance when the tables get larger?

Hmm, I've read about there being caution for using the functions "NOW()" and "TODAY()", but none for using a "Count" type field

---
re: Yes, having a payments table would be beneficial if I can use zapier to pull in paypal transactions (we do all invoicing with paypal recurring payments).

Ahh yeah, this should be simple enough to do.  Each payment would have their email as well right?  And so you could use that to link it to the appropriate MEMBERS record as well