5 Replies Latest reply on Apr 29, 2010 2:08 PM by philmodjunk

    Script to revise data entry in a field



      Script to revise data entry in a field


      Hi all, I am trying to write a script that will do the following using data from two related tables (D and S)


      Upon commiting the value in S::FieldID (value list),a script trigger runs the following script:


      If S::FieldName is empty, then copy D::FieldName to S::FieldName.


      If S:Fieldname is not empty (that is, it was entered previously upon selection of a FieldID) and S::FieldID is different from the original entry, then change the S::Fieldname to the D::FieldName.


      I thought I had the script correct, but then my client disclosed that he would like to make certain the S::FieldName would change if he changed the S::FieldID.  I have chosen not to use the related field to automatically complete the S::FieldName, but my client needs the option of manually filling the field (just in case).


      Any help would be appreciated.  I cannot figure out the proper order of script terms to get this to work, so any insights would be great.




        • 1. Re: Script to revise data entry in a field

          Perhaps instead of a script, you should just enable the looked up value field option on this field. That option will automatically copy the data every time your ID field is updated.

          • 2. Re: Script to revise data entry in a field

            Phil, I thought about using a look-up, but worried about all the records being updated every time my client makes a change, which is not desirable.  Can I write a script that will perform a 'look-up only for the current record?



            • 3. Re: Script to revise data entry in a field

              That's exactly what this feature does. A looked up value will be copied from the related table only when an update is performed on the key field in the current file.


              Classic example of this is an invoice system where you can use looked up values to copy using prices fro a products table. If you enter or change the product ID in the current table, the matching unit price is copied. If you edit other fields and or records, the value does not change.

              • 4. Re: Script to revise data entry in a field

                Phil, Thank you for the explanation.  The reason for my confusion stems my having set fields as look-up fields and when a record was updated, I believe the other records in the table were updated.  Or, do I have this understanding incorrect?

                • 5. Re: Script to revise data entry in a field

                  There's more than one kind of "lookup" you can devise for a filemaker database. That's why I've been careful to refer to a "looked up value field option".


                  What I am referring to works like this:


                  Define field in the main table where you will store the looked up value.

                  Define a relationship linking the main table to the look up table.

                  Double click the field definition in the main table to bring up field options.

                  Click the auto-enter tab.

                  Click the looked up value check box, specify the table occurrence name of the lookup table and select the field storing the value you want to copy over.


                  If you modify the key field in the main table, only the current record's lookup field will copy the related value. All other records remain unchanged. If you modify data in the look up table, data stored in the main table's fields will not be modified unless you either update the main table's key field or put the cursor in this field and select "Relookup".