Hi everyone, I'm a bit new to using tools like AT and am looking for some feedback. My overall goal is to maintain membership data within organization(s). These organizations are linked together to some capacity and have overlapping members. I want to use this to my advantage so that updates from one organization update the profile of the person (and thus other organizations).
I included some organizational charts below to get a better idea of what I’m trying to accomplish.
The main problems I’m having with the current setup is:
How to handle the unique person ID problem. Data lists provided are not 100% standardized with complete information. only 10% of records may have a phone/email, so do I resort to fuzzy matching based on first/last + zip code?
User defined fields. Some clients may have a field they want to store for information like membership ID #, DOB, etc. How do I allow for this without adding a new field everytime and updating all my automations.
How to safely handle bulk imports and allow secure client-specific views and updates. I created the staging step as a way to build in an automation step that flags records > as a potential match > if not, it goes through automations from People > Membership.
Current AT table set up (staging for import, people is the overall pool, memberships would be the filtered in the interface for client viewing/exports)
Overview of the data organization
Overview of the data handling/project overall
Page 1 / 1
For someone new to using tools like AT this is extremely well thought out and considered haha!
I agree that your memberships table (in effect a junction table between contacts and orgs) is necessary to express the various differences between categories that organizations might want. Nice thing about the membership junction table to is it’s easy to express time limited memberships, so if there’s something where dues occur every year you can really clearly express that. As for your fields issue, as long as the orgs aren’t collecting reams of unique, extremely specific data, I don’t think it’ll be a problem. You can keep one Member ID field and just put John Smith’s unique member ID for each org he’s in. The real question is more deciding what field stays in Memberships and what’s worth adding to People (DOB can move to people, Member ID in Memberships, but there might be edgier cases).
You're right that deduping is going to be really annoying, especially if it’s only zip+first/last. I do thing this might be a circumstance where an autonumber as a primary field in your people table makes sense, you could end up with multiple John Smiths in the same zip code. Airtable does have a deduper extension, and you might setup a formula field that flags potential duplications.
The staging table is a good idea for versioning control and bulk importing. I suppose I wonder a bit about the Update Requests table vs the Staging table, but sometimes the extra space is handy for a specific workflow. As for your question about access and sharing, I’d recommend setting up interface pages based on the membership table, filtered by each organization. You can add lookup fields from the people table for whatever info the org specifically wants.
How to safely handle bulk imports and allow secure client-specific views and updates.
For the client specific views, you can try creating an Interface for this, and a few of your options are:
Create one Interface per client that can be shared publicly (i.e. anyone with the link can view it) and give each client their respective link. The records in this Interface will be filtered to only display that client's records
Create one Interface per client and invite them to the Interface as a read-only user (requires them to create an account)
Interfaces lets users export the data as a CSV, so your clients can export data too
---
User defined fields. Some clients may have a field they want to store for information like membership ID #, DOB, etc. How do I allow for this without adding a new field everytime and updating all my automations.
Hm, what automations are you running? In my head you get a CSV from the clients and then import it via the CSV Import extension, and then attempt to match these new ‘Membership’ records to ‘People’. Once matched, you can use lookups to pull data over to the People table to display stuff
Is it the matching process where you’ve got an automation? But that wouldn’t touch the user defined fields, right?
For someone new to using tools like AT this is extremely well thought out and considered haha!
I agree that your memberships table (in effect a junction table between contacts and orgs) is necessary to express the various differences between categories that organizations might want. Nice thing about the membership junction table to is it’s easy to express time limited memberships, so if there’s something where dues occur every year you can really clearly express that. As for your fields issue, as long as the orgs aren’t collecting reams of unique, extremely specific data, I don’t think it’ll be a problem. You can keep one Member ID field and just put John Smith’s unique member ID for each org he’s in. The real question is more deciding what field stays in Memberships and what’s worth adding to People (DOB can move to people, Member ID in Memberships, but there might be edgier cases).
You're right that deduping is going to be really annoying, especially if it’s only zip+first/last. I do thing this might be a circumstance where an autonumber as a primary field in your people table makes sense, you could end up with multiple John Smiths in the same zip code. Airtable does have a deduper extension, and you might setup a formula field that flags potential duplications.
The staging table is a good idea for versioning control and bulk importing. I suppose I wonder a bit about the Update Requests table vs the Staging table, but sometimes the extra space is handy for a specific workflow. As for your question about access and sharing, I’d recommend setting up interface pages based on the membership table, filtered by each organization. You can add lookup fields from the people table for whatever info the org specifically wants.
Thanks for the input!! I didn't think closely about the question of universal person data vs membership specific data, I have since updated my flowchart :D
The deduper extension does seem to work very well, I was having trouble implementing a fuzzy matching system. This extension seems to have that natively! So I should be able to manually confirm any duplicate person IDs (people with more than one memberships).
I am now trying to work backwards starting with a clean membership table. Since the only issue would be that I don't want to run through the same verified duplicates every time I import a new client. Any ideas for this?
The update/request table is so that I could have each organization fill out the form to submit update/requests, and I would get a log of requests made.
How to safely handle bulk imports and allow secure client-specific views and updates.
For the client specific views, you can try creating an Interface for this, and a few of your options are:
Create one Interface per client that can be shared publicly (i.e. anyone with the link can view it) and give each client their respective link. The records in this Interface will be filtered to only display that client's records
Create one Interface per client and invite them to the Interface as a read-only user (requires them to create an account)
Interfaces lets users export the data as a CSV, so your clients can export data too
---
User defined fields. Some clients may have a field they want to store for information like membership ID #, DOB, etc. How do I allow for this without adding a new field everytime and updating all my automations.
Hm, what automations are you running? In my head you get a CSV from the clients and then import it via the CSV Import extension, and then attempt to match these new ‘Membership’ records to ‘People’. Once matched, you can use lookups to pull data over to the People table to display stuff
Is it the matching process where you’ve got an automation? But that wouldn’t touch the user defined fields, right?
Thanks for the advice!
The CSV import extension tool is sweet, I do feel like I could do without the staging table. Though it might be nice to have if the membership tab gets a bit crowded.
Does option 2 incur a monthly cost? When I did my early testing, I did end up racking up a few extra user charges, but I did invite/add them to my workplace as a user and didn’t try only as a interface viewer.
I started out with 3 automations:
form submissions; based on adding/editing/removing an membership
an import automation when a membership is added, find record match to person > update (if found), add person record if not. I’m not confident enough with the record matching to rely on this one
Import staging automation to find duplicates and flag them as potential matches. I was not able to get the scripting to work properly
#1 could still have some use cases, but I think I’m more confident manually making the small # of changes. #2 and 3 I have since removed, going back to the drawing board based on your replies.
then attempt to match these new ‘Membership’ records to ‘People’. Once matched, you can use lookups to pull data over to the People table to display stuff
I did some playing around with this, what is a good way to do this? Or is there a good source on how to set this up properly.
Thanks
I am now trying to work backwards starting with a clean membership table. Since the only issue would be that I don't want to run through the same verified duplicates every time I import a new client. Any ideas for this?
I don’t quite follow your question here. When you use the dedupe extension it asks you to merge data if applicable, select a preferred record, and delete the duplicate. When onboarding a new client you’re going to have to dedupe their member list against your master People table each time to ensure that John Smith from LA is the same as J. Smith from LA, but different from Johnny Smith in Nashville.
The other thought that is bouncing around my head is to conceptualize the onboarding in terms of importing membership records and not in terms of importing people/contacts.
Couple other tips from your response to Adam…
To save on seat costs you have to be specific and grant only read access. You can create interfaces and share Read access to anybody for no cost, it’s only when you have comment/edit/create that seat charges apply (and of course the default is editor level).
As you’re using forms, I’m extremely obligated to mention Fillout Forms, which is the nicest forms solution for Airtable (and much more powerful than AT’s built in forms). Fillout has a very generous free tier (I’ve used it for over a year free, and finally started paying after justifying my employer. I probably could have stayed on free but damn do those guys rock and deserve the sub haha).
I brought this up in another recent forum post, but I wish you had email addresses provided with your member lists, as I find those are the best option for a generally unique ID for matching purposes. I’ve got a ongoing registration table with automations that use email address as the contact identifier, and it helps a lot in cutting down on manual matchmaking.
Does option 2 incur a monthly cost? When I did my early testing, I did end up racking up a few extra user charges, but I did invite/add them to my workplace as a user and didn’t try only as a interface viewer.
Nope, you’d be inviting them as read-only to the Interface only and that’d be free
---
I did some playing around with this, what is a good way to do this? Or is there a good source on how to set this up properly.
Hm, if you’re talking about the matching, if you could provide examples of the data you’re attempting to match that'd be helpful
If you’re talking about the lookups, could you talk more about what you mean by properly? Is it that there are too many fields for you to create lookups or something?
