Button script to autofill one field with data from two other fields in same table

[db_tragic]

Ignore Below - Just realised I didn't have a surname value present in the record I was testing it on so there was none to copy/past (Is working fine)- Doh!!!        

 

 

 

Simple scenario:

Contacts table includes 'FirstName' and 'LastName' fields. Also has a field 'Invoice To'.

 

I want to place a button next to the 'invoice To' field, which, when clicked, fills the field with 'FirstName'(space)'LastName' fields (ie like inserting two merge fields I guess, so it reads as "Bob Jones" in the 'Invoice To' field, where 'Bob' is from the 'FirstName' field, and 'Jones' is from the LastName field.

 

It looks like the 'lookup' function only allows me to reference a different table, and I don't know the correct script syntax to merge the content of two fields into another.

 

Tried the following script, but the script seems to stop after the space insertion, so the surname doesn't get added after the first name has been copied:

 

Copy [select; Client Details::Owner_FN]

Paste [Client Details::Invoice To]

Insert Text [Client Details::Invoice To;" "]

Copy [select; Client Details::Owner_LN]

Paste [Client Details::Invoice To]

 

Is there another way? Or do I need to split this into 2 scripts (can you not copy/past more than once within a script?)

 

Thanks in advance.

[comment]

Is there another way?

 

Yes. You could have defined Invoice To as a calculation field =

FirstName & " " & LastName

 

Or, even better, you could just merge the two fields in a text object - see:

http://www.filemaker.com/12help/html/create_layout.9.28.html#1028554

[LaRetta]

I see this a bit differently ... an Invoice To usually designates which employee within a company should receive the invoice (such as an attention line) and so Invoice To should remain standard field whereby User can enter a different name or even a department or 'bookkeeper'. If this holds true in this situation, the Invoice To could be auto-enter (replace existing value) with:

Case ( IsEmpty ( Self ) ; First Name & " " & Last Name ; Self )

This would allow entry of alternate person while providing the concatenated Client name if empty. If you have existing records, you will need to set the values through all the records first. Quick way (back up first) is to

1. Change Invoice To to a CALCULATION (result is TEXT) with the above calculation

2. Exit field definitions back to your layout.

3. Go back in and change Invoice To back to regular text

4. Open auto-enter by calculation and you will see the calc is still there.

5. Uncheck 'do not replace existing value if any'

6. Say OK and exit

This will change all records in your table. Be sure this is handled when not served or when Users are not in system to avoid record locking or other breaks which can occur by you modifying definitions while multi-user.

Oops. Corrected calc ... missed a parenth

Also uncheck 'do not evaluate if all referenced fields are empty'. I can't believe I almost forgot that!

Edited December 11, 2012 by LaRetta

[LaRetta]

BTW, I also suggest that you switch to script-step Set Field [] instead of copy and paste. They require that the fields be present on the layout whereas Set Field[] does not and copy/paste also destroys a User's clipboard contents. :-)

[db_tragic]

Yes. You could have defined Invoice To as a calculation field =

FirstName & " " & LastName

 

Or, even better, you could just merge the two fields in a text object - see:

http://www.filemaker.com/12help/html/create_layout.9.28.html#1028554

 

Thankyou, I had considered that,however I want to retain the option to leave it blank, or put an alternative name in that field.

 

I'm still very new to calculations. so they are not my first choice at the moment, and am trying to develop my scripting where I can (though I realise that scripting and calculations will eventually crash into each other!)

 

Thanks for the reply.

[db_tragic]

I see this a bit differently ... 

 

Hi LaRetta,

 

Thanks for your input. Phrases like "Case ( IsEmpty ( Self ) ; First Name & " " & Last Name ; Self )" are still foreign to me, and don't appear to me as 'intuitive' (as yet!)

 

Would you be willing to break it down for me? 

I have a FM9 Functions reference manual, and looking at the 'Case" function,  I understand the process in principle.

However, the expression "IsEmpty (Self)' - Does this mean that if the field is empty, it will return a value of (Self)? What then is 'self'?

The 'result2' value of the expression (First name & "" & Last Name)  - I presume the expression will insert the contents of the two fields, separated by a space here.

The result3,'Self'  - again, what is self in this expression?

 

This is a new database with only a handful of records, so no, don't have to 'retrofit'.

 

I can see the value of using SetField instead - will keep that in mind.

 

Thankyou

[LaRetta]

Case ( IsEmpty ( Self ) ; First Name & " " & Last Name ; Self )

If itself is empty, fill with 'first then space then last' otherwise leave the value as is.

Case() is asking 'if' ... Self simply looks to its own field value. What you reference as the result2 value is actually result1. IsEmpty() is the test, semi-colon separates the parameters

Case ( test ; result1 ; optional result2 ) ... which in the case of Auto-Enter (replace) means that if you do not provide a DEFAULT result, will return an empty field. So if the field is not empty, leave the manually-entered value alone.

WHEW! Now Michael (Comment) could have explained it better with a quarter of the words, LOL.

ADDED. BTW, you did a good job of breaking it down yourself. :-)

Also there is more to Case() as you had read ... I just kept it short for the example.

Edited December 13, 2012 by LaRetta

[db_tragic]

Thankyou LaRetta,

 

That explains it well - no variation necessary!

There's definitely a knack of understanding these expressions, and this is a good start. 

 

You can read/memorize a dictionary all day, but makes you no wiser on how to construct a sentence, let alone a whole paragraph.

 

That's what it feels like reading things like a function reference manual.