How to select just one record from a Linked to Form field

[Victor Warner]

I have a database with two forms.

The first form (Client Contact) contains fields such as

1. name
2. address
3. phone
4. and a Linked to Form field (passport) to another, second, form

The second form (Passport) contains fields such as

1. passport number
2. date of expiry
3. country
4. nationality

The Linked to From field works fine.

I am having a problem in the following situation:

1. Client Contact form has a record and
2. The Passport has two records which are linked to the one record in Client Contact

e.g Jane Smith in Client Contact form has two passports.

The problem is as follows:

1. I wish to create a further field (a calculation field in the Client Contact form) which brings together a number of fields from the Passport form for one of the two passports for Jane Smith; but
2. it is not possible to choose just one of the two records.

If I do enter fields (from the Passport form) in the Edit Formula box, they show up a summary tags (eg count, total).

In the above situation how would it be possible to select just one of the linked records.

[Brendan]

You’ll have to use a Script Field for this because the Calculation field cannot reference specific sub-records of a Link to Form field.

var passport_field_id = 'fld-....';
var passportRecords = record.getFieldValue(passport_field_id);
if (passportRecords.length > 0) {
   var firstPassportRecord = passportRecords[0];
   var number_field_id = 'fld....';
   var number = firstPassportRecord.getFieldValue(number_field_id);

   // and do whatever you want with the values/

}

Hope that makes sense.

But what you could do maybe instead of trying to extract the passport information on the Client Contact form into a Calculation field, you could go the other way around and if you’re using a One-to-Many Link Type from Client Contact to Passports, then you could add a Calculation field to your Passports form that references the Client Contact data. In that situation because it’s as One-to-Many relationship, the inverse of that is a Many-to-One and you can reference individual fields from the one Client Contact record from within the Passport records. You’ll notice that if you double-click on the inverse field of a One-to-Many relationship in the Formula Editor, you’ll get something like Client Contact::First Name instead of one of the aggregate math functions.

Thanks,

Brendan

[Victor Warner]

Brendan,

Thank you for the reply.

The script example, unfortunately, is beyond me (having no knowledge of JavaScript) at present.

I am not sure that I understand, fully, the following:

“then you could add a Calculation field to your Passports form that references the Client Contact data. In that situation because it’s as One-to-Many relationship, the inverse of that is a Many-to-One and you can reference individual fields from the one Client Contact record from within the Passport records.”

These seems to be suggesting the reverse what I am seeking.

I am not seeking to reference individual fields from the Client Contact record, but rather, the reverse, some fields from one of the records in the Passport form.

As I am new to Tap Forms I suspect I am missing something – further help would be greatly appreciated.

[Brendan]

Well, with a Link to Form field, there’s no way in a Calculation field to reference an individual child record. So the only way to do it is to use JavaScript.

Going the other way was just a suggestion.

[Victor Warner]

Brendan,

Thank you for the reply.

As I mentioned Javascript is unknown me (but it is clear I will have to learn it), but in the meantime I wonder if you could provide some explanation for each of the lines of code you provided (to try to understand how to relate the code to the example I gave in my initial post):

var passport_field_id = ‘fld-….’;
var passportRecords = record.getFieldValue(passport_field_id);
if (passportRecords.length > 0) {
var firstPassportRecord = passportRecords[0];
var number_field_id = ‘fld….’;
var number = firstPassportRecord.getFieldValue(number_field_id);

// and do whatever you want with the values/

}

And I in particular how I write the code for

// and do whatever you want with the values/

if I wished to produce:

“having a ” + [country] + ” passport, with a date of expiry of ” + [date of expiry] + ” and a passport number + ” ” [passport number]

Any help you could provide would be very gratefully received.

[Brendan]

// declare the field ID for the passport field. Select the field and click the ID button to get the field ID.
var passport_field_id = 'fld-....';

// From the currently selected record, get the list of records from the passport field.
var passportRecords = record.getFieldValue(passport_field_id);

// if we have any passport records, get the passport information.
if (passportRecords.length > 0) {

   // Get the very first passport record from the list of passportRecords.
   var firstPassportRecord = passportRecords[0];

   // declare the passport field ids.
   var number_field_id = 'fld....';
   var expiry_date_field_id = 'fld...';
   var country_field_id = 'fld...';

   // From the first passport record we grabbed above, get the passport number value.
   var number = firstPassportRecord.getFieldValue(number_field_id);
   var expiryDate = firstPassportRecord.getFieldValue(expiry_date_field_id);
   var country = firstPassportRecord.getFieldValue(country_field_id);

   // and do whatever you want with the values. What you had is almost what you needed:

   "having a " + country + " passport, with a date of expiry of " + expiryDate + " and a passport number + " " number;

}

[Victor Warner]

Brendan,

Thank you very much for explaining the script, and I can now understand the logic of it but I am having difficulty applying it my database.

As being completely new I am still not clear whether I should be creating

1. a new field of the type script; or
2. a Form script

If a new field script, should I creating a field in the Client Contact form or the Passport form?

Attached is a backed up version of the Forms to show how it actually looks.

Attachments:

You must be logged in to view attached files.

[Brendan]

You want a Script field just as you’ve created. Because you’re wanting to display the first passport information in the parent form.

Here’s the exact script you can just copy and paste into your Passport Script:

// declare the field ID for the passport field. Select the field and click the ID button to get the field ID.
var passport_id = 'fld-bb12ade9886146818f0cee47af4a36d5';

var result = '';

// From the currently selected record, get the list of records from the passport field.
var passportRecords = record.getFieldValue(passport_id);

// if we have any passport records, get the passport information.
if (passportRecords.length > 0) {

   // Get the very first passport record from the list of passportRecords.
   var firstPassportRecord = passportRecords[0];

   // declare the passport field ids.
   var passport_number_id = 'fld-fc5f7b08aa244466919fd400714ffa6b';
   var date_of_expiry_id = 'fld-b73ebd82a8b2479882e9d2fc5b161ff9';
   var country_id = 'fld-b502438543564135859a133912994d3f';

   // From the first passport record we grabbed above, get the passport number value.
   var number = firstPassportRecord.getFieldValue(passport_number_id);
   var expiryDate = firstPassportRecord.getFieldValue(date_of_expiry_id);
   var country = firstPassportRecord.getFieldValue(country_id);

   // and do whatever you want with the values. What you had is almost what you needed:
  
  result =   "having a " + country + " passport, with a date of expiry of " + expiryDate.toLocaleDateString() + " and a passport number " + number;

 }
 
 result;

[Victor Warner]

Brendan,

Thank you very much. I am able to produce the output.

Now that it works I can see that

// declare the field ID for the passport field. Select the field and click the ID button to get the field ID.
var passport_id = 'fld-bb12ade9886146818f0cee47af4a36d5';

confused me. I could not see, based on this explanation, how the link to the Passport form was established. It is this declaration which creates the link.

This declaration which actually creates the link (or declaration to the Passport form) is the heading in the list of fields rather than any field in the Passport form:

Attachments:

You must be logged in to view attached files.

[Brendan]

Ah yes. The heading is actually clickable. Double-click on it or click on it and then click the ID button. I guess it’s not obvious that it’s clickable. I should change that so that it highlights if you click on it.

[Author]

Posts