Extracting data from linked record in One to Many-relationship

[Melanie Schaaf]

Hi,

I have run into a problem extracting data from a Link to Form-field with a One to Many-relationship.

I usually use Many to Many-relationships in my database, which is probably why I’ve never had this problem before. What I am trying to do is use a script to extract data from a certain field in a linked form and display it in a script field, because I can format that to match my layout.

What I usually do with a Many to Many-related form is this:

function Collection_Series() {

var embroidery_collections_id = ‘fld-9be76f6c90334bbbaa0e2c4dc3556358’;
var collection_series_name_id = ‘fld-40cb304839354352875dcaefb9403a2f’;

var childs = record.getFieldValue(embroidery_collections_id);

var child_names = [];
for (child of childs){
child_names.push(child.getFieldValue(collection_series_name_id));
}

//child_names.sort();
var done = child_names.join(‘, ‘);
console.log(done);

return (done);

}

Collection_Series();`

Now, I am aware that this script is designed for multiple values to be returned, from multiple linked records. But this should also work for a single record from a One to Many-relationship…

But it doesn’t work. I should say that I am using this on the reverse relationship variable, so basically on the “One”-side of the One to Many-relationship.

Can anyone help me perhaps with a different method to extract that value from the linked record?
Like I said, it is simply an aesthetic problem; I don’t like the look of Link to Form-Fields in a layout and I really just want to display the value and not the control elements, because I will be linking records from. the other side.

I might as well just use a Many to Many-relationship, but I feel like there should be a way to do what I am trying to do with a One to Many-relationship as well.

Thanks in advance!

[Daniel Leu]

The only thing I noticed is your return statement. Try to replace it with return done;.

Since it’s the reverse relationship of a one-to-many, you could use a calculation field as well.

Here’s a script for a single value, although it doesn’t contain any error checks: https://www.tapforms.com/forums/topic/totally-lost-on-script/#post-53221

Cheers, Daniel

---
See https://lab.danielleu.com/tapformspro/ for scripts and tips&tricks

[Melanie Schaaf]

Thanks for the tip Daniel!

Unfortunately I wasn’t able to make your approach work, but I might not have implemented it correctly. I am a very beginner level script-writer.

However, while I was looking for a solution in the forums last night, I found out that there is a custom GPT on ChatGPT and with its help I was able to fix the script and now it works on a 1:M relationship.

The problem was that while a linked form in an M:M relationship returns an array of records and while in a 1:M relationship the form also returns an array on the “Many”side, it does not return on array on the inverse relationship side, as there is always only one record.

The corrected script (with the aid of the Chat GPT assistant) now also extracts several fields with no problems:

function Extraction_Collection_Data() {

// get the linked parent record (ONE record in 1:M)
var collection = record.getFieldValue(‘fld-9be76f6c90334bbbaa0e2c4dc3556358’);

// destination field IDs in Embroidery Designs
var entire_collection_on_file_id = ‘fld-ef5768afe70c46968999aae8f538aa65’;
var collection_series_id = ‘fld-ee2bff7b5d8f4e7f80797e6ad144dc33’;
var collection_series_photo_id = ‘fld-a1b6349df4124933a70abb6dd30728d5’;
var collection_website_id = ‘fld-cb6dd1f0e77c4fbcba331141ecd66d5d’;

// — CASE 1: no linked collection (collection == null)
if (!collection) {

record.setFieldValue(entire_collection_on_file_id, ”);
record.setFieldValue(collection_series_id, ”);
record.setFieldValue(collection_series_photo_id, ”);
record.setFieldValue(collection_website_id, ”);

return;
}

// — CASE 2: a single linked parent record exists

var entire_collection_on_file = collection.getFieldValue(‘fld-c0f51c1134e84335aaf27c64fa0c0bf1’);
var collection_series_name = collection.getFieldValue(‘fld-40cb304839354352875dcaefb9403a2f’);
var collection_package_photo = collection.getFieldValue(‘fld-0ed3dcb5bf194e3996de901f86a390d5’);
var collection_website = collection.getFieldValue(‘fld-40ed47e24443496b9e0bdfd9831ff570’);

record.setFieldValue(entire_collection_on_file_id, entire_collection_on_file);
record.setFieldValue(collection_series_id, collection_series_name);
record.setFieldValue(collection_series_photo_id, collection_package_photo);
record.setFieldValue(collection_website_id, collection_website);
}

Extraction_Collection_Data();

I am very happy with this solution and can only recommend using the ChatGPT assistant for basic scripting help. For me it worked brilliantly!

[Daniel Leu]

Looks like you’re using TapForms5.

Sometimes ChatGPT does a good job, sometimes it hallucinates and creates strange recommendations like inventing nonexisting API functions. In your script, you should add a document.saveAllChanges(); before you the return statement.

Happy coding!

Cheers, Daniel

---
See https://lab.danielleu.com/tapformspro/ for scripts and tips&tricks

[Author]

Posts