Structuring Bases for Pain Tracking App, very basic questions :(

[Zachary_King]

Hi Team,

I am seriously struggling with the basics here, so appreciate if someone can guide a noob towards the right base structure. To give some context, the idea that I am building is as follows:

Patient goes for a guided injection treatment for musculoskeletal injury.
Imaging clinician adds Patient Name and Patient Mobile Number to Airtable form embedded in site.
At specific intervals (day 0, 1, 3, 7, 14, 28) the platform will send an SMS poll asking about pain levels. The patient replies with a single number from 1-10. [this is done via Zapier and ClickSend]
Imaging Clinician can then receive report with Patient Name and Pain Scores or alternatively log in to site (planning to use Memberstack) to access their data directly.
You can see my pre-launch site (built in webflow) here - www.mobius.xyz

So the main base I have built is as follows:

and when patient replies I built the following base with records added via Zapier:

I think I have made a mistake by using Patient Mobile Number as the primary field for both, yes? So how should I construct this instead? I want to be able to record the pain scores for each patient for each day of the poll, as indicated by the # of days since the procedure.

I would ultimately like to be able to generate something like the following:

[note that this base is obviously FAKE NEWS because I can’t for the life of me build the damn thing!]

I then want to able to deliver that base (or should it be a view???) to a customer portal for each Imaging Clinician, which I plan to do via memberstack and webflow, but I’m a ways off yet!

I hope this makes sense! Can anyone help out someone who is completely lost? I will repay with good vibes and (internet) hugs!

many thanks in advance,
zac Structuring Bases for Pain Tracking App- noob alert! Structuring Bases for Pain Tracking App, very basic questions

[Justin_Barrett]

Zachary_King:

I think I have made a mistake by using Patient Mobile Number as the primary field for both, yes?

Yes. The primary field should be unique for each record.

Zachary_King:

So how should I construct this instead? I want to be able to record the pain scores for each patient for each day of the poll, as indicated by the # of days since the procedure.

I suggest creating a [Customers] table to contain customer data. Have the customer name as the primary field (possibly using a formula if you prefer to keep first and last names in separate fields), including a field for their mobile number. Anywhere else in the base where you need to refer to that customer, you would link to their record, in the same way that I think you’re linking to imaging clinicians (I think that’s a link field; I’m guessing that you pre-shrunk your screenshots. You can upload screenshots full-size, and the forum software will auto-shrink them for display, and allow for expansion to full size for easier viewing).

When a customer reports their pain level, use Zapier to look up their record from the [Customers] table via their phone number, and link to that record on the [Pain response data] table. For the primary field on that table, you could use a formula that concatenates their name along with the date of their reply.

Does that sound like it will work?

[Zachary_King]

thanks @Justin_Barrett that’s much appreciated. The reason I thought to use the mobile number as the ‘key’ to identifying the record is that it will be unique - no guarantee that names will be so.

and I didn’t know that I could use zapier to ‘look up’ a record to add it to! that’s some next level magic right there. what zapier tool would I use to do that?

but assuming that I am understanding your approach, I could still use the mobile number as the key as per the above, but I would still structure the [Customers] table with name as the primary record, allowing me to link as necessary.

for the [Pain Response Data] assuming here that your suggestion for the primary field formula is simply to create a unique primary field, in which case I could use mobile number + date to avoid name duplications, would that suffice?

[Justin_Barrett]

Zachary_King:

The reason I thought to use the mobile number as the ‘key’ to identifying the record is that it will be unique - no guarantee that names will be so.

If you want the primary field in the [Customers] table to be the phone number, that will work.

Zachary_King:

I didn’t know that I could use zapier to ‘look up’ a record to add it to! that’s some next level magic right there. what zapier tool would I use to do that?

I don’t use Zapier a lot, but I’m pretty sure that there’s an Airtable search action/step that you could add to your zap. Search your [Customers] table to find the phone number, and Airtable will return the matching record, which you can then use in later parts of the zap.

Zachary_King:

but assuming that I am understanding your approach, I could still use the mobile number as the key as per the above, but I would still structure the [Customers] table with name as the primary record, allowing me to link as necessary.

I don’t think that it would matter if the primary field in the [Customers] table is the customer’s name or their phone number. Again my lack of Zapier experience is evident here, but I think that the Airtable search step operates on all fields, not just the primary field.

Zachary_King:

for the [Pain Response Data] assuming here that your suggestion for the primary field formula is simply to create a unique primary field, in which case I could use mobile number + date to avoid name duplications, would that suffice?

Yes, that should work fine. Good thinkin’! :thumbs_up: