How do I change values of several fields (all linked to one table) with values from another table

[Nick_Martin]

Hi there,

I am a complete novice.

I have a billing table with several procedure code fields, all of which are linked to a procedure code table from which they can be selected.

I would like to build a macro where a type of surgical procedure (a surgical procedure may have one or more procedure codes) can be selected from a field in the billing table. the selection field contains the name of a surgical procedure and is linked with a templates table which contains the procedure name as the primary key with it’s corresponding procedure codes (the procedure code fields also are linked to the procedure code table.

The aim is to improve billing ease and coding consistency.

I really hope that there is a solution to this (knowing me, it is probably embarrassingly straightforward) - if there is code involved, I would be grateful if someone could write me an example.

Thanks much,

Nick

[Andy_Lin1]

Let me know if I’m understanding your base structure correctly.

[Billing]

• {Link to [Procedure Code]}* (multiple?)
• {Link to [Surgical Procedure Templates]}
  [Procedure Code]
• {Link from [Billing]}
• {Link from [Surgical Procedure Templates]}
  [Surgical Procedure Templates]
• {Link from [Billing]}
• {Link to [Procedure Code]}

If this is correct, and I’m understanding your questions correctly, you want it so that when you select a Surgical Procedure in the Billing table, the corresponding Procedure Codes will populate in the Billing table?

If that’s the case, it seems like you could just add some Lookup/Rollup fields that pull the Procedure Codes from the Surgical Procedure entry?

However, if it’s more complicated than that, you might need to create an example base so people can better understand what you’re asking.

[kuovonne]

Welcome to the Airtable community!

It sounds like you have a background in traditional relational database design, but are new to Airtable.

Airtable is similar to a traditional relational database, but it does not handle joins the same way and there are no “primary key” or “foreign key” fields. (The leftmost field is the “primary field” but it is not a “primary key”.) Instead, Airtable uses linked record fields to indicate relationships. All links must be explicitly linked (either through the user interface, or via an automation or other code).

I suggest you look into lookup fields to see if they provide the functionality you want. Note that a lookup is based on a linked record field.

If lookup fields do not work for you, you may need to use automations or scripting (which is what Airtable has instead of macros.)

[Nick_Martin]

Hi Andy,

Thanks for the fast reply. You seem to have it spot-on.

A “Templates” table holds rows which contain the procedure name (primary field - linked to the “ProcedureName” field in the “Billing” table), as well as several “ProcedureCode” fields which contain object arrays derived from a table containing all possible procedure codes.

The “Billing” table allows the user to select up to 9 procedure codes as appropriate. The therein contained data informs the billing process.

What I would like to be able to do is to select a procedure by name from the “ProcedureName” field in the “Billing” table and for this selection to populate the “ProcedureCode” fields in the “Billing” table.

I could code for that his, however, I struggle with the initiating argument (i.e on change of the “procedureName” field value - something occurs) as well as with handling the object data type (i.e. how do I refer to a specific field in the “Templates” table and how do I handle the object in the code).

I hope that makes sense.

Thanks so much,

Nick

[Nick_Martin]

Hi kuovonne,

Thanks for the reply. Your posts on other topics have really helped me in getting to know Airtable.

I had figured out the unique handling of joins recently and I have adapted my tables and the relationships appear to be intact and working.

You seem to have pointed me in the right direction, if I update the “Templates” table content every time I enter a code combination that does not already exist, it works.

The only remaining issue is that I can not change individual codes in the “Billing” table (without making changes in the “Templates” table), which would obviously be ideal. Let me know if you have any thoughts on this.

Thanks again for your input.

Best,

Nick

[Nick_Martin]

Hi kuovonne,

Apologies, the system unfortunately breaks a lookup relationship further downstream, which prevents the billing amount calculation.

Looks like I am back to square one, trying to update linked fields with values from a separate table, based on the selection made in a different field (in the table that is being updated).

Thanks again,

Nick

[kuovonne]

Since using lookups alone does not work for you, it sounds like you need to use either automations or scripting to copy the values over.

Would copying the lookup value to an editable field work for you? You could do this with an Update record automation.

[Nick_Martin]

Hi kuvonne,

I can not thank you enough. It works with an update automation, based on changes in the “template” field of the “Billing” table.

You are an absolute star!

Thanks again,

Nick