Return into form field, data from linked form?

[David Oxtoby]

Hi, my fist post, so hope don’t mind the newbie question…. so i have a customer form with usual data name, company etc. I then have a bookings form that takes info like room booking, date, etc. i have the customer form linked so i can pull the right customer record. Is there a way when that link record is then shown on the form to have a separate field pull the customer number from the linked record into a field. Reason is the summary records in the side column, it’d be good to show the customer name in that summary. See attachment for what i mean.

Attachments:

You must be logged in to view attached files.

[Brendan]

Hi David,

If you have a One to Many Link Type from Customer to Venue, then you can enable the Show Inverse Relationship option and you can get one customer to display on your Venue layout.

However, it looks like you probably have a Many to Many relationship. Is that required?

The only other way to get the customer information to appear in the parent field is to use a Script and pull out the first Customer record and populate the field on the parent form.

Thanks,

Brendan

[David Oxtoby]

Thx Brendan, i’d assumed i needed a script, i just wasn’t sure how to even start referencing the form/records in the linked form to pull out one of the values, any pointers would be great. Thank you

[Sam Moffatt]

If you make Company a script field, you can use the Child Records Loop to iterate over the Customer Link to Form and extract the name. The reason is that you can technically have multiple customers so you need to iterate overall of them and process them.

That said I’d probably flip the model a little. You’ve got a Link to Form there and I’m guessing it’s a many to many or M:M type. If for a given venue booking there is only one customer, I would recreate the field as a Link to Form One to Many (1:M) going from the Customer form to the Bookings form with show inverse relationship ticked. What this will do is change the type of control you see on your booking form to be the single record selection layout which may or may not be desirable for you. It’s like the single column list view sort of rendered into your form. The advantage of flipping the relationship is you can easily use a calculation field to pull out records. Since the booking record will only ever have a single parent, you can create a calculation field to pull the parent record’s values out (or multiple calculation fields for each field you care about). Make sure when using a calculation field to update the default output type from number to text otherwise it’ll appear empty and you won’t know why.

[Brendan]

Excellent recommendations Sam.

Hopefully what Sam said makes sense David.

[David Oxtoby]

Thx for that Sam, i’m a bit new to the whole scripting thing, i see how to make the Company field into a script field, and then add in the snippet of code for Child Records Loop, but if gives me the following the editor.

function recordsLoop() {

	var parent-field-name_id = 'parent-field-id';
	var booking_date_time_id = 'fld-8fb5dadd45ee4276b4813669aeef34e3';
	var parent-field-name = record.getFieldValue(parent-field-name_id);

	for (var index = 0, count = parent-field-name.length; index < count; index++){
     	var booking_date_time = parent-field-name[index].getFieldValue(booking_date_time_id);
		if (booking_date_time) {
			// do something
		}
	}
	return;
}

recordsLoop();

how do i then tell it to loop through my customer form/table to pull out the first record Company Name?

sorry to ask the basics on this, but it’s a long time since i programmed, and back then it was COBOL! eek showing my age.

[Brendan]

Hi David,

I took COBOL in university. So that shows my age too! :)

I’m going to edit your response a bit to enclose your code in back-ticks. That will show the code more nicely formatted and it’ll be easier to read. I’ll respond properly in my next reply.

[David Oxtoby]

Thank you, i also tried Sam’s suggested about reversing the way i’m linking, and it gets me the desired result of the company name etc in the summary side-panel, so thank you for that. But having the code snippet would be great as well, if nothing else, as an aid to help me learn how to use the scripting, as this looks to be a great feature with loads of potential, once i get my head around it. Thank you everyone for speedy and helpful response.

[Brendan]

For the Child Records Loop snippet to work properly, you need to first select a child field from the Fields list on the Script Editor.

Then double-click on the Child Records Loop snippet.

You’ll get something that works then.

However, since you have a many-to-many relationship, you have to think about which customer record you actually want. The Child Records Loop snippet will loop through all of the child records. The // do something comment means this is where you want to do some custom programming.

But in your case, if all you want is the first record, then you don’t really need the loop itself. You can just grab the first child record.

So something like this:

function getCompany() {

    var company = '';
    var contact_records = record.getFieldValue('fld-abc123.......');

    if (contact_records.length > 0) {
        var first_contact = contact_records[0];
        company = first_contact.getFieldValue('fld-xyz987......');
    }

    return company;

};

getCompany();

So this will pick out the very first record from the contacts Link to Form field and return the company. It will appear in your Company Script field.

I hope that makes sense.

[Brendan]

By the way, you have to replace the field IDs with your own proper field IDs.

But since you used Sam’s idea and switched to a One to Many Link Type, then you could also simplify the above script to:

function getCompany() {

    var company = '';
    var contact_record = record.getFieldValue('fld-abc123.......');

    if (contact_record) {
        company = contact_record.getFieldValue('fld-xyz987......');
    }

    return company;

};

getCompany();

By the way, I’ve just typed in this code and haven’t actually run it since I don’t have your form. So it might not work out of the box.

[David Oxtoby]

Yay! with a bit of head-scratching etc, i got it to work, thank you so much! Thats a huge help.

[Sam Moffatt]

If it’s the way I have it set up, you don’t even need to use a script field any more. A regular calculation field will work and all you’ll have to do is select the field from the field list on the left. Easy peasy!

Technically you could also do something similar with scripting and a little bit of chaining:

record.getFieldValue('fld-lnkfromform').getFieldValue('fld-targetfield');

If there isn’t a parent record set, it’ll internally error out but that should be mostly harmless as a single line.

[Author]

Posts