5 Replies Latest reply on May 14, 2014 6:56 PM by whitetailtech

    Paste entire record from one layout table to another layout table?

    whitetailtech

      Got thrown into FileMaker13 overnight and picking up the pieces as I go. My users can copy a record from one layout table but cannot past it into another. Any suggestions? Ideally I would like to make a script to remove the record from the first table and then add it to the other one.

       

      Any help is appreciated.

        • 1. Re: Paste entire record from one layout table to another layout table?
          flybynight

          A little more info about what you are trying to accomplish would be helpful. I don't believe that you can copy and paste a whole record, like you can a row in Excel, if that is what you are thinking.

           

          Are these actually different tables, or just different layouts?

          If they are different tables, do they have the same fields? ie- Would the data line up correctly if you were doing this in an Excel-style table view?

          If so, then that begs the question: Why do you have 2 tables with identical fields/attributes? Sounds like you have some data modeling issues to straighten out.

           

          That said, if you still want to go down this path, you would need to do it through a scripted process. Just a rough example, you would have a button that captures each field to a variable, then go to the desired layout, create a new record, then set each corresponding field using your variables.

          Set Variable [$field1 ; Value: TableName::field1]

          Set Variable [$field2 ; Value: TableName::field2]

               etc, etc for each field.

               (or you could do it by setting everything concatenated into one variable and parsing it out later, but that's up to you)

          Go to Layout [Destination layout for 2nd table]

          New Record/Request

          Set Field [DestinationTableName::field1; $field1]

          Set Field [DestinationTableName::field2; $field2]

              etc, etc for each field.

           

          But, I would first take a hard look at your schema and figure out if this is actually how you should be doing things. It could save you a ton of re-work down the road.

           

          HTH,

          -Shawn

          1 of 1 people found this helpful
          • 2. Re: Paste entire record from one layout table to another layout table?
            whitetailtech

            You nailed all of my thought processes at once.  The fields do line up exactly except the ID.  The data should not be replicated accross multiple tables but that is a propblem for another day. The formula is what I need to get started so thank you for your help. 

            • 3. Re: Paste entire record from one layout table to another layout table?
              erolst

              Leaving aside the usefulness of this endeavour: an import is always an option, or, if the field names are identical, you could

               

              • relate both tables via a global, i.e. tableB::globalIDTableA = tableA::primaryKey

              • put a list of the field names to process (i.e. all) into a variable (e.g. via SQL: http://www.databuzz.com.au/using-executesql-to-query-the-virtual-schemasystem-tables/)

              • use a script and Set Field by Name to copy over the data via the relationship

               

              [on layout of tableA ]

              Set Variable [ $sourceTO ; Get ( LayoutTableName ) ] // assuming this is the TO of the relationship; otherwise hardcode

              Set Variable [ $ID ; tableA::primaryKey ]

              Go to Layout [ tableB ]

              Set Variable [ $targetTO ; Get ( LayoutTableName ) ]

              Set Variable [ $fieldList ; ExecuteSQL ( " SELECT Fieldname FROM FileMaker_Fields WHERE TableName= 'tableA' "; "" ; "" ) ]

              Set Field [ tableB::globalIDTableA ; $ID ]

              New Record/Request

              Loop

                Exit Loop If [ Let ( $i = $i + 1 ; $i > ValueCount ( $fieldList ) ]

                Set Variable [ $curFieldName ; GetValue ( $fieldList ; $i ) ]

                Set Field by Name [ $targetTO & "::" & $curFieldName ; GetField ( $sourceTO & "::" & $curFieldName ) ]

              End Loop

               

              Obviously the appeal of this method is directly related to the number of fields you need to copy …

              • 4. Re: Paste entire record from one layout table to another layout table?
                BruceRobertson

                IF you're on a Mac and applescript is a possibilty then there is a technique that allows you to copy a record; plus all of its related data to a new record.

                 

                See attached basic demo. This demo does not have a related record set.

                 

                It requires two layouts which contain no calc or auto enter fields; and which contain the required "match" fields to be in correct tab order on the source and target layouts.

                 

                Note that in this case the tables have a subset of fields in common.

                 

                If you look at creation order for the fields in the two table notice that they do not match.

                 

                The basis script is:

                 

                try

                set targetLayout to "People2 Target"

                set theData to (get data current record)

                go to layout targetLayout

                create new record with data theData

                 

                 

                on error errmsg number errnum

                display dialog errmsg

                end try

                • 5. Re: Paste entire record from one layout table to another layout table?
                  whitetailtech

                  This saved me a ton of time.  Now I can focus on cleaning up the data and setting the tables up correctly.

                   

                  Thanks again.  Great Forum!