fix the schedule table to play nicely with airtable

[markdav-is]

the Help Service -> schedule linkage is doubled-up. here are some hints at fixing it:

• take a snapshot of the database
• make a copy of the name (from name) field as a simple text field and call it something like name-old
• remove all the links from schule to help service
• verify they are gone by looking at the Base Schema app
• Add a name field that is a dropdown of "regular", "holiday", and "special" and pick regular for all the records.
• Add a link-column to the help-services table with teh relation 1 help service to many schedules
• copy paste the old service name colm to the linked column
• remove the old service name column.
• Edit the ID field to be a function that is the linked-column+name
• verify your work by looking at the Base Schema app.

[markdav-is]

a helpful article: https://support.airtable.com/hc/en-us/articles/215436738-Using-a-formula-in-the-primary-field

[colindavey]

The autonumber field looks like the way to go.

[ArthurSmid]

Yes, @colindavey, yesterday I messaged Devin, the person working on the HSDS Airtable template with Open Referral and he replied, the U in UID is unique, for unique identifier:

If you want a UID you can do it two ways - either an Autonumber field type or you can use the Formula field type and Record() formula to show the autogenerated Airtable record_id. (https://support.airtable.com/hc/en-us/articles/360051564873-Record-ID)

For context in reading his reply, here's the note I wrote to him on their Slack:

Our team entered information into Airtable before being aware of HSDS and we're still learning how to configure tables to HSDS. The first phase has been a schedule table and I configured the primary field as ID.

This is a read-only link to our base: https://airtable.com/invite/l?inviteId=invK6Fh0H2po2c4oW&inviteToken=8882388a9b5d965e119f7bd15c379d1a6a517f2783098fbba37ec5a8491601c7

The schedule field links to the Help Services table (all services are listed in one table there), pulling in Name and Hours of Operation to the schedule table so the plain language hours can be read there and inform the enter of schedule data.

You'll notice that ID field is currently blank, but I gather that Airtable API automatically assigns a number to the field? I don't really know.

@devin, do you have any recommendations for how to configure an ID in our primary field in the schedule table in that base we're using? For instance, would it be a formula? or is it left blank and the Airtable API assigns a number that exists where needed but just isn't visible in the field?

[markdav-is]

Airtable generates its oun id's automatically so adding our own is redundant. That will put two id's on the data coming back from the API. Please take a look at the airtable generated API to understand the data structure better. I don't think the folks who created the hsds example airtable realized we get id's for free and the ID is an artifact of making a copy of the schema from mysql where you have to define them.

[colindavey]

What I was trying to say is that we can use the option from Mark's article that uses a formula in the primary field that combines a string (the provider name in our case) with an autonumber field.

[markdav-is]

Then you will have two id's. Please look at the airtable API and you will see record.id if you add another you'll have record.fields.id as well. When the airtable API shows linked tables it uses the internal ID. The second ID will be superfluous. So you should call it something else to avoid confusion. Like hsds_id or something so folks know to ignore it.

[colindavey]

Is the primary field synonymous with the record.id? An autonumber field isn't necessarily the ID.

[devinbalkind]

Airtable recently made it possible to display the Airtable's autogenerated record_ids fields with the RECORD_ID() formula so we have adopted that approach throughout the HSDS 2.0 Airtable template here: https://airtable.com/universe/expAX64T1BvtFvaHc/human-services-directory-hsds-20

[colindavey]

Oh, I see. The primary field is not the record.id. If I understand correctly, all records have both, and both have to be unique. The primary field is a human readable unique id.

[markdav-is]

Thanks @devinbalkind that will make it so folks can use either field to find related records.

[colindavey]

We use the primary field (Name) to link to records on other tables. So using a formula for the Name field that combines the provider name with an autonumber field will create unique human-readable Names. So, when editing the schedule column of the Help Services table, it will be easy to pick the ones that apply to the provider.

[devinbalkind]

We use text for primary fields when users might want to link that table to another using a name and use record_id when the table's row names are irrelevant (i.e. schedule).

One thing to keep in mind: if you make a primary field a formula then users won't be able to create a new record by typing into the linked record field on another table. For example: if I were on an organization table and want to type a service name in the service linked record column then I wouldn't be able to do that if the primary field were a formula. Instead I could click the little plus sign, then add new record on the lookup menu, then fill out the service form that appears.