Display linked content

[Vera Milosavich]

I have two linked forms (from different sources): TRANSACTIONS and INVOICES

Both of them have fields named month-year and and invoice-number

In TRANSACTIONS, month-year is a calculation field (based on a date field in the same form).
In INVOICES, month-year and invoice-number are text fields (not number or date) and I type in the info when I create an invoice. Both are unique values across all records. Everything is fine up to this point, but…

I want the invoice-number field in TRANSACTIONS to display the invoice-number from INVOICES for all TRANSACTION records where month-year match. I tried both a calculation and a script. The calculation inserts a count formula, which is not what I want. The script gives me an error when I test it inside the script editor. I attached a screen shot of the script & error.

The forms are linked by month-year — one to many — but I tried the other linking options and they were no better.

I’m sure my logic is not what it should be due to my JS illiteracy. Can you tell me what I’m doing wrong?

Attachments:

You must be logged in to view attached files.

[Brendan]

Hi Vera,

There are a couple of problems with your script.

First off, you haven’t defined link_invoice anywhere, yet you’re referencing it.

Also, you haven’t defined index, yet you’re using it as an array reference.

Take a look at the Child Records Loop snippet. If you select your invoice number field from your Linked form on the Fields list on the left, then double-click on the Child Records Loop snippet at the bottom-left of the Script Editor, Tap Forms will write a function for you to loop through all of the records in your link_invoice Link to Form field.

Since it’s a many-to-many, there can be multiple values for Invoice Number.

But if all you want is the first one, then you could do:

function get_invoice_number() {
   var invoice_number = '';
   var link_invoices_id = 'fld-.....';
   var link_invoices = record.getFieldValue(link_invoices_id);
   if (link_invoices.length > 0) {
      var first_invoice = link_invoices[0];
      var invoice_number_id = 'fld.....';
      invoice_number = first_invoice.getFieldValue(invoice_number_id);
   }
   return invoice_number;
}

get_invoice_number();

You of course have to replace ‘fld….’ with your own field IDs, which you can get from the Fields list on the left.

[Vera Milosavich]

I’m sorry for being so dense, but I’m afraid I don’t completely follow. And I also think I neglected to specify something…

When you say I haven’t defined link_invoice, that’s the name of the link-field I had set up to link the two forms, but it wasn’t in my screen shot. I assumed a link field is more like a reference field (like hidden instructions) and wouldn’t display any content on my forms. If it should display content, can you tell me what and how? Or do you mean it needs to be defined as a variable at the top? But I’m still at a loss for what to define.

I followed your instructions for the Child Records Loop script and got my hopes up when it didn’t return an error in the script editor, but I still did something wrong. It puts “Hello World” in all the invoice_number fields when that should either be one of two different invoice numbers or blank — to coincide with the month-year field contents. Where the “do something” placeholder comment appears, I tried doing what made sense to me. The result is the same with or without that. And I don’t know where it got “Hello World” from. It doesn’t appear in any record of either file — except where it put it.

My new attached image shows all this.

Attachments:

You must be logged in to view attached files.

[Vera Milosavich]

The forum deleted my post and attachment. I’ll try again but I don’t have the time or energy to rewrite it so I’ll just try attaching the new screen shot. It shows the results of my attempt to set up the child records loop you suggested along with my annotations of what I hoped it would do.

Attachments:

You must be logged in to view attached files.

[Brendan]

Hi Vera,

Sorry about that. I don’t know why, but sometimes the forum marks messages as spam. I found it and I approved it. So it’s there now.

[Brendan]

Hi Vera,

Ok, so your script is good, except the only thing you’re not doing is returning your invoice number value.

Where you’ve said record.getFieldValue(invoice_number_id), you could just prefix that with return. As in return record.getFieldValue(invoice_number_id);.

But better would be to just return the first invoice number from the first record in the array.

To do that, use this code after the var link_invoice line (line 5).

if (link_invoice.length > 0) {
    var first_invoice_record = link_invoice[0];
    return first_invoice_record = first_invoice_record.getFieldValue(invoice_number_id);
}

The above code just checks to see if there is at least one link_invoice record. If so, it then gets the first link_invoice record from the list of linked records. Then it gets the value from the invoice_number field.

And that’s it!

Hope that works for you.

Thanks!

Brendan

[Vera Milosavich]

Thank you for the help, Brendan. I’m sorry for the delay in responding — I had some urgent projects that needed to be completed. I’m still working on them. I will check out your fixes next chance I get and let you know how it goes.

And no worries about the spam markings!

Thanks again.

[Author]

Posts