Script equivalent of “Select existing linked records”

[Brian Lennon]

I have been very impressed with how easy it was to import several thousand records from a previous database into Tap Forms 5. Adding pick lists, calculation fields and the like worked so well. There is one aspect I need advice on.

The way I previously stored records of training completed by each person is one I need to change. (The previous TRAINING records only carried a course code and a person code.) So far I have managed to write a script that extracts the course codes for each person from a one-to-one linked field (“TRAINING”). Now what I want to do it insert these codes (“course_code”) into another many-to-many linked field (“TRAININGnew”) and I don’t know how to script this.

Manually it works. I go to TRAININGnew, click on the little tick at the bottom that prompts “Select existing linked records”, I click on the course code from the linked form COURSES and the relevant course code and details appear in TRAININGnew. I need the script equivalent of this action. So my question is, how do I script this so that it will use the course_code extracted from TRAINING and select an existing course record identified by this course code in the TRAININGnew link so that the full course details will appear in the TRAININGnew link.

Many thanks

Brian Lennon

[Brendan]

Hi Brian,

I think this will take a bit of work. There is an API called record.addRecordToField(someRecord, field_id); in Tap Forms. You’ll need to call that to add the record to your Link to Form field.

Now the tricky part is to get the record that you need from the linked form. That would be the someRecord in the above API.

You will also probably need to use the var records = form.getRecordsForSearchTerm("search term"); API call. Now, the form you use will need to be the Link to Form field’s form. So to get that, you’ll need to call the document.getFormNamed('My Movie Library'); API and assign it to a variable (not named form).

For example:

var link_to_form_field_id = 'fld-......';
var linkedForm = document.getFormNamed('Linked Form');
var records = linkedForm.getRecordsForSearchTerm("some unique course code");
var linkedRecord = records[0]; // assuming your course code is unique you'll get just one record back
record.addRecordToField(linkedRecord, link_to_form_field_id);
form.saveAllChanges();

Hopefully that’ll get you what you need. I haven’t tested this code (just off the top of my head), but it should generally work. You may need to do some testing to check to see if you actually get records back.

• This reply was modified 1 year, 1 month ago by Brendan.

[Brian Lennon]

Many thanks Brendan – I will try your solution.  It will take me some time to absorb Javascript’s way of working!

[Brian Lennon]

Brendan – I tried to build in your suggestions but no luck so far.  In fact, getting the different entries in the old record worked fine.  Popping these into the new link doesn’t seem to work.  Remember, I’m practically a total novice with Javascript so I’m probably committing massive mistakes.  Here’s what I’ve tried so far with comments that hopefully give a clue as to what I’m trying to do.  Any suggestions will be welcome but I acknowledge this may be a big job so don’t worry if you don’t have time.

 

//PURPOSE: restructuring imported data from earlier utility
//to relocate a training course code to a different linked form

//Getting list of existing training course codes for each person
//from the old TRAINING linked form 1:1

function recordsLoop() {

var oldTrainingForm_id = 'fld-732ad9dbc05249feac197450c84ef299';//incoming linked form
var oldTraining_code_id = 'fld-f8bb7875bd094c4c94ea0ae98c5536d8';//course code id
var oldTrainingValue = record.getFieldValue(oldTrainingForm_id);

//var oldCodelist = [];//array to store incoming codes

//new link (to COURSES form) to receive the code and so link to COURSES
//the new training form is linked many:many

var newTraining_id = 'fld-ac031e652f864a4a8ad98d20efb7f360';

var newLinkedForm=document.getFormNamed('newTraining_id');//not sure about this!
var newCode_id = 'fld-549ed54657f94e81a9f1a3f0c4bff844';//field to receive incoming code

for (var index = 0, count = oldTrainingValue.length; index < count; index++){//looping through incoming linked form

var oldTraining_code = oldTrainingValue[index].getFieldValue(oldTraining_code_id);//get value of each incoming code
if (oldTraining_code) {//if there is a code
// read each course code from old TRAINING form
console.log (oldTraining_code);//getting the right incoming codes here!  They appear as a list in console

//the bit that's not working
//attempting to add the oldTrainging_code to the TRAININGnew link to form COURSES
//My attempt to program what can be done manually by clicking the tick ("Select existing linked records") below the TRAININGnew table.

//console.log("ACTION "+ oldTraining_code_id + "--" +newCode_id);

//this looks like it does what I want ... but it does nothing!
record.setFieldValue(newCode_id, oldTraining_code);

}
}
return;
}

recordsLoop();
document.saveAllChanges();

• This reply was modified 1 year, 1 month ago by Brendan. Reason: edited to add back ticks around the code

[Brendan]

Hi Brian,

So there are many issues with your code.

• This syntax is incorrect unless you actually have a form named newTrainingId.
  • var newLinkedForm=document.getFormNamed('newTraining_id');
• You weren’t using the variable newLinkedForm anyway in the rest of your code.
• The line record.setFieldValue(newCode_id, oldTraining_code); is just setting the value on the same parent record over and over again.

It would help me to better understand what you’re wanting to do if you posted your form template so that I can look at your form structure and see if I can help you with the script.

[Brian Lennon]

Brendan – I feel I am presenting an issue that would probably use up too much of your precious time.

I’m still struggling with the syntax of javascript and am probably trying something that’s too far beyond my present capabilities though  I did have success with some simpler scripts. (I feel like someone who has just started to learn English and is seeking help with an understanding of the grammar of Joyce’s Ulysses!)

Anyway, will attach the form template as requested.

My original database used 1:1 links from PEOPLE to TRAININGold (that just carried the person’s code and the course code) and this in turned linked to COURSES via the course code.  I’m aiming to simplify this so that PEOPLE links directly to COURSES.  With over 4,800 records in the database, a script is the only sensible way to process this import.

I’m not sure how much the template communicates so here are the current linkages:

Form PEOPLE > Field TRAININGold >>link 1:1 >> Form oldTRAINING (original data here)

Form PEOPLE>Field TRAININGnew>>link many:many>>form COURSES (wanting structure to use this link)

In the script I sent earlier I had changed the names of some variables to make them more understandable to myself and perhaps that was not the right thing to do.

If you can give me a few pointes in the right direction I’ll struggle on in my learning process.

Many thanks for your support.

 

Attachments:

You must be logged in to view attached files.

[Brendan]

So just for clarification, your TRAININGold and FACULTY LEVEL Link to Form fields are both Join relationships, not 1:1 (one to one). They’re technically many to many, just connected by a common field in the two forms.

So what you need to do is loop through the records of your TRAININGold form, then make new records and add them to your TRAININGnew Link to Form field.

There’s a function called var newRecord = record.addNewRecordToField(field_id); which you’ll want to use to add the records to the TRAININGnew Link to Form field.  That function returns a new record which you can then set the field values on.

[Brian Lennon]

Many thanks Brendan – you capture exactly what I’m trying to do.  I will follow your suggestions.  I need to tune in more to the conventions of Javascript.

Great support!

Brian

[Brian Lennon]

I have one more question, hopefully a straight-forward one and best illustrated by the accompanying screen grab.

Field TRAININGold links to a form called TRAINING and contains the old data.  The TRAINEEid is the reference code for this person in the PERSONS form that holds these fields.

Field TRAININGnew links to COURSES and is the intended destination of the new records.  I have manually inserted a record using the tick at the bottom and selecting course coded 38 from the COURSES form.  This is the action I want to script to complete data import and restructuring.

The question:  If TRAININGnew links to courses as a join and sharing a common code, where is the common code in TRAININGnew?  In TRAININGold, the TRAINEEid (137905) linked to TRAINcode 38, 62 and 78.

When I use the tick to show course 38 in TRAININGnew, it is obviously linking in some way to the TRAINEEid of 137905 but this shared link code is not visible in the linked table at TRAININGnew.  Somehow I reckon I need to incorporate this invisible link into my script that attempts to replicate the tick action.

Once I have all the TRAININGold records transferred to TRAININGnew, my intention is to add new course records using the tick approach.

Thanks again

 

 

Attachments:

You must be logged in to view attached files.

[Brendan]

One to Many and Many to Many links use an internal relationship. There’s no “code” linking them together. Only the Join link type links together with values from two form fields that match.

[Author]

Posts