Display contents of field in a linked db

[Vera Milosavich]

This is an extract of a setup I have and the simple problem I’ve been trying to solve:

I have two linked dbs with the following relevant fields (and field types) set up:

1. TRANSACTIONS (has many records with the same Month/Year): Month/Year (calculation:text); Invoice (script:number); link_Invoice (link to form:join where Month/Year matches in both dbs)
2. INVOICES (Month/Year will be unique to each record): Month/Year (text); invoice number (number)

I want the Invoice field in TRANSACTIONS to display the Invoice Number from INVOICES where Month/Year matches so I used this script:

var invoice_number = link_invoice[index].getFieldValue('fld-8b601a885fab4ddca84e44bed77e8bc5');

I didn’t key in anything. I only selected the snippets I wanted then double-clicked the field I wanted from under the section with the link I set up. I get this error when I run the script in the editor view:

5/11/20, 4:11:06 PM / Paypal Imports (405) / Invoice-script
Invoice-script: ReferenceError: Can't find variable: link_invoice, line:(null)

I used Join as the link type, but I tried the others with no more luck. I can’t tell what I’m doing wrong because I can’t decipher the script.

I’m hoping it’s something simple…… although I’m officially beyond simple with my nonexistent javascripting abilities!

[Sam Moffatt]

Did you end up figuring this out? For what ever reason I stumbled back upon this and notice it didn’t have a reply though I could have sworn there was a reply on this somewhere.

What you might be missing is the iteration, instead of double clicking on the field name for child fields you need to select the field name and then select “child records loop” which will then put in the scaffolding to make a line like that work and define link_invoice for you. From there you can join them together and return it as a string for display.

[Vera Milosavich]

Hi Sam — No, I have not yet figured it out. I’ve been super busy on another job and unable to study it any further. I will probably end up back at square one because I already forgot everything I learned! Brendan responded to related questions I posted so one of those might be what you saw.

Thank you for the additional info. I’ll get back to it eventually.

Vera

[Brendan]

Oops. I must have missed seeing this post back in May. Sorry about that. Sam’s suggestion will get you there for sure.

[Vincent Nunez]

Any further developments here?

I want to lookup values in an external table. I am joining the two tables by matching with fields both tables have in common. I *believe* these are properly linked with a Join field. But my external table does not seem to be returning values no matter what I try. The external table’s fields do appear to be available. Yet are they really?..

Approach #1 — I attempt to use a script field. I get the ‘ReferenceError: Can’t find variable’ error. (I am wondering, why would I have an index to iterate with when I am using a field-level script? I pull from a source, I assign a waiting destination field – yet nothing goes through. I can run the script with local table information, no problem.)

Approach #2 – I attempt to use “mail merge” syntax like so – [ExternalTable::JoinedField]. But this does not update when printed (to PDF). Local merge fields are updating (when out to PDF) no problem.

Approach #3 – I attempt to use a calculation field as text output. But when I try to use any of the external table values I only seem to be able to access them through Count operation. I want to access a string, not a count of values.

Approach #4 – Almost forgot. The closest I have gotten was actually my first attempt of embedding my external lookup into a plain layout. I am able to export that out as hardcopy, yet what I need is a reliable CSV export, not a copy/pasting activity based on PDFs. (Approach #2 would be ideal..)

What basic ideas am I missing?

Thanks,
Vincent

[Sam Moffatt]

A JOIN field is a many to many relationship, you need to iterate over the values. For Approach #1, use the ‘child records loop’ snippet with the field you are interested in and it will put the rest of the loop scaffolding in for you. You will have to choose what you do from there, merge everything together, return the first one you see or something in the middle.

I’m not sure why Approach #2 isn’t working, though I’ve not used that syntax and I suspect the JOIN link messes with it as well for similar reasons to Attempt #3.

The JOIN type’s many to many relationship is why Attempt #3 with the calculation field is giving you issues because there could be more than one record linked, so it defaults to giving you a count because the calculation tool doesn’t have the depth to let you handle multiple values.

If this is supposed to be a singular value, I’d recommend creating a Link to Form in your other form as 1:M and then ticking show inverse relationship. It’ll create a Link From Form field in your current form which will simplify Attempt #1 and Attempt #3 will let you pick the individual fields.

If using the Link From Form doesn’t get you across the line you can also have the scripting create the CSV for you and then copy it to the pasteboard (not recommended for terribly large amounts of data but a few MB of data shouldn’t stress it too much). Then you’d run a form script to populate your pasteboard and you can paste it into a destination app or TextEdit to save to disk.

[Vincent Nunez]

Hi Sir Moffat – I do appreciate your time and your complete reply. Thanks to your emphasis on that ‘child records loop’ snippet, and thanks to all the tutorials available through the website here, I took a more patient look and Approach #1 is now working for what I need at this time. Honestly it’s been awhile since I last hacked about with scripts. I didn’t realize how (relatively) easy Tap Forms was making it for me. I’ll keep at it! – Respectfully, Vincent

[Sam Moffatt]

Great to hear it’s working for you!

[Author]

Posts