13 Replies Latest reply on Apr 29, 2013 8:20 PM by debi

    script to copy contents of source_field to target_field

    s

      FileMaker Pro 12 on Server 12 - OS X

       

      I have a client who wants a button he can place on any layout between two fields (which could be in the same table or different tables or even a different file), then specify the source and target fieldnames as the script parameter, which will then copy the contents of source_field to target_field.

       

      The client is trainable to understand the parameter must look like this:

      "source table::source_field"

      & "¶" &

      "target table::target_field"

       

      or even this:

      "source table::source_field¶t1_t2_x::t2_field¶"

       

      I can then set variables for each field and the data they contain using variables:

      Set Variable[$sourcefield ; getvalue(get(scriptparameter); 1)]

      Set Variable[$sourcedata ; evaluate($sourcefield)]

      Set Variable[$targetfield ; getvalue(get(scriptparameter); 2)]

      Set Variable[$targetdata ; evaluate($targetfield)]

       

      Then I can use Set Field By Name [$targetfield ; $sourcedata] to copy the data.

       

      That works fine, but my question is this:

      How do I make sure that the fieldnames he types into the script parameter are real fields and do not contain typos? In other words, confirm that $sourcefield and $targetfield are real.

       

       

      TIA

       

      --

      Steve Moore

      Cumberland, Maine

        • 1. Re: script to copy contents of source_field to target_field
          Mike_Mitchell

          Try:

           

               IsValid ( GetFieldName ( $sourcefield / $targetfield )).

           

          Mike

          • 2. Re: script to copy contents of source_field to target_field
            s

            Thanks for your reply, Mike.

            IsValid tells me if the data in the field matches the type of field - like Date or Time or Number. What I'm worried about is making sure the field names specified by the user actually exist.

            Perhaps I can use

            Patterncount(fieldnames(get(filename) ; layoutname) ; $sourcefield)

            • 4. Re: script to copy contents of source_field to target_field
              s

              Thanks again, Mike. I appreciate your help.

               

              I added a number field, then put imanumberfield into the testFieldName, and amIValid returns 1.

              So far, so good.

               

              But if I enter  "www" into the number field, amIValid returns a zero.

               

              It seems perhaps the Help file is correct - IsValid operates on the type of data entered into the field.

              • 5. Re: script to copy contents of source_field to target_field
                Mike_Mitchell

                Drat. It does pass all the way through to the contents of the field.  

                 

                Okay, you have two choices (or others I haven't thought of):

                 

                1) Build a layout that has the "legal" fields your user can use for this functionality and use the calculation you suggested, as modified below:

                 

                     not IsEmpty ( FilterValues ( FieldNames ( Get ( FileName ) ; {layoutName} ) ; $sourcefield / $targetfield ))

                 

                PatternCount is a poor choice for situations like this, because you may get a false positive (what if he types "users::first" when the real field is "users::firstName"?).

                 

                2) Use ExecuteSQL to query the database schema and get your list of field names from there. Databuzz has a good article on how to fetch the current contents of the schema here:

                 

                         http://www.databuzz.com.au/using-executesql-to-query-the-virtual-schemasystem-tables/

                 

                The advantage of choice 1 is tighter control over what your user can legitimately query. The advantage of choice 2 is lower maintenance (once set up, it will automatically adjust to changes in the database).

                 

                Sorry for the rabbit trail.

                 

                Mike

                • 6. Re: script to copy contents of source_field to target_field
                  steve_ssh

                  Hi guys,

                   

                  If I'm not mistaken, I think you were going in a good direction with Mike's idea to use an FM function to test validity.  I'd suggest checking out a few other functions before leaving that path.

                   

                  Another function that came to mind was IsValidExpression, but the one that I can see that looks even more promising would be FieldType.

                   

                  How about if in your sample file, you define the calc for  amIValid to something like:

                   

                     not IsEmpty( FieldType( Get( FileName ); testFieldName ) )

                   

                  I don't use FieldType all that much, but from what I'm seeing, its behavior is to return empty if the field name does not exist.

                   

                  Hope this helps & best,

                   

                  -steve

                  • 7. Re: script to copy contents of source_field to target_field
                    debi

                    Steve,

                     

                    This might be a long-shot, but it is simple if it is possible: Could you just teach your client to name the objects, something "object1" and "object2"? Then you wouldn't need any parameters, and the script could be very simple.

                     

                    I think this could work if - and these are big ifs:

                    There will only ever be one pair per layout.

                    You don't need to name the fields something else for some other reason.

                     

                    Alternately, I think you could test in your script much as you suggested above, perhaps using FilterValues instead of PatternCount.

                     

                    HTH,

                     

                    Debi Rubel

                    FullCity Consulting

                    • 9. Re: script to copy contents of source_field to target_field
                      s

                      No apologies needed - I appreciate the help.

                       

                      I don't need to worry about building a layout with the "legal" fields - the client can paste the button onto whichever layout he wants, and the script/calc will evaluate from that context. (The assumption is that both the source and target fields are on the chosen layout(s).)

                       

                      I thought I could use this formula in my script:

                      Appending carriage returns before and after the names should prevent the false positives when one field name is a subset of another.

                       

                      Patterncount("¶" & fieldnames(get(filename) ; get(layoutname)) & "¶" ; "¶" & $sourcefield & "¶")

                       

                       

                      However, fieldnames returns a list like this:

                      FirstName

                      LastName

                      t1_t2_x::t2_field

                       

                      So the local fields do not have the table name prefix with the double colon.

                      Ick.

                      Back to the drawing board...

                      • 10. Re: script to copy contents of source_field to target_field
                        Mike_Mitchell

                        You can use Get ( LayoutTableName ) and a PatternCount function (looking for the "::") to grab the table.

                         

                        But I think Steve's suggestion is worth a look!

                         

                        Mike

                        • 11. Re: script to copy contents of source_field to target_field
                          s

                          Thanks, Debi.

                           

                          Unfortunately, there will almost always be several pairs of fields.

                          This is a summer camp registration database. What we are doing is pulling in new registration data entered on the web site and comparing it with last year's registration data that alredy exists in Filemaker. A pair of human eyes is needed to confirm that the name entered on the website (new registration) does indeed match a name already in Filemaker (previous year's registration).

                           

                          The left side of the layout shows all the new values and the right side shows the corresponding old values.

                           

                          Then he wants to be able to easily update the previous year's data with the new registration data (email, phone numbers, parents, emergency contacts, medications, etc). He's perfectly willing to paste in the new buttons and specify the script parameters for all the pairs of fields that will need updating.

                           

                          Wll the Set Field By Name script step work with object names instead of field names?

                           

                          Thank you again.

                          • 12. Re: script to copy contents of source_field to target_field
                            s

                            Thanks, Steve!

                            FieldType does indeed solve the problem!

                            I appreciate your help.

                            • 13. Re: script to copy contents of source_field to target_field
                              debi

                              Unfortunately, there will almost always be several pairs of fields.

                              I figured it was a longshot.

                               

                              Wll the Set Field By Name script step work with object names instead of field names?

                              You could Go To Object, and then Set Field (unspecified will set the active field.

                               

                              Glad you got it all working!

                               

                              ~debi