Getting field value from related record

[Philip Jones]

Hello all,

I am trying to migrate an existing FM database to Tap Forms. Before I purchase I am trying to ensure I can make the basics of my existing db work.

Please forgive my naïveté as I am a beginning user of Tap Forms.

I have two tables: Patients and Procedures (surgical).

A patient can have one surgery; many people can have a given surgery. Therefore, on the Procedures table (screenshot 1) I have ‘Patients’ set up as a one:many field. I have the ‘link to form’ turned on so that, in my Patients table (screenshot 2) I can see the linked information correctly.

What I am attempting to do is to get the values of some of the related fields (from the Procedures table) into the Patients table. I am using a Script field for this. As per the attached screenshot, I am first trying to get the ‘OHIP code’ (‘C111A’ in the second screenshot) into its own field on the Patients table.

I have attached a screenshot of my script where I attempt to do so. It is not working and I know the fix is likely something quite simple.

In the Script window, the field ID was obtained by double-clicking on the “Code” field in the “Procedures” table on the left-hand side of the window.

Any help to get me on my way would be much appreciated.

Thank you.

Phil

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

When you’re working with a linked record, you need to get a copy of that record. If you double click on “Procedures” it should insert some code to help you get the procedure record. It should look something like this (if it inserts just an ID then you can update it to match):

var procedures = record.getFieldValue('fld-1234');

Where the fld-1234 piece will be the value that the script editor puts in when you double click on the “Procedures” section heading. I did a video on building a script that has some details that might be useful. There are a few other videos on the channel that could fill in some gaps. There is also T.L. Ford’s Tap Forms Scripting 101 and 102 as other resources that might help.

That said since you’re on the “one” side of the 1:M field, if all you’re after is copying the value from the parent then using a calculation field is likely going to be simpler. Calculation fields in that situation will let you just double click the field you want and it should do the heavy lifting for you.

One schema recommendation is to use a third form that stores two link fields for going back to patient and procedure. You link 1:M from Patients and Procedures into this new form, let’s call it “Appointments” (I’d use “Operations” but that could be confusing), make sure you tick “show inverse relationship”. This leaves the “Appointments” with a single link back to the other two forms which means you can keep stuff like patient name unique unless you don’t have a lot of recurring patients. If what you’ve got it working that’s great but I’d generally take a step further to split patient from the instances of a procedure they have so that their data (name mostly it seems) is only in one place.

[Philip Jones]

Thank you Sam, for the missing link (getting a reference to the record before referencing the field in question) and for the helpful suggestions!

It works now that I have double-clicked the Procedures section heading.

I had actually watched your deep dive video but I will watch it again as well as your others.

Merry Christmas!

[Sam Moffatt]

Good to hear this helped you along :)

I have another video that covers link to form fields with some examples as well that might help. Need to get back to putting those together again over the break.

[john cesta]

yea, thanks Sam!

[Philip Jones]

Great video, Sam. Watched it and it solves my problem. I have altered the overall schema as you suggested to use a bridge table with several 1:m relationships.

I appreciate your help!

[Sam Moffatt]

Great to hear that it was useful for you!

[Chris Ju]

Thanks for the video, Sam. It helps me with another issue i had today!

[Sam Moffatt]

Glad to hear it helped someone else too :)

[Author]

Posts