7 Replies Latest reply on Feb 21, 2017 7:53 AM by philmodjunk

    DUPLICATING/COPYING RECORDS

    piaccounting

      Hello

       

      Being extremely new to Filemaker I am currently evaluating whether to purchase Filemaker Pro 15 Advanced. I do like what I have seen so far.

       

      My primary objective is to replace a Microsoft Excel solution, where VBA code is used to perform a specific function which could possibly be greatly enhanced with Filemaker. I have a specific requirement and would like to know whether Filemaker is capable of handling it. Therefore would appreciate any comments. I am not looking for anyone to provide the solution rather advise whether this creation of additional records as described below is possible.

       

      The task is defined below:

       

      I want to allow the user to create some records into a portal. Once completed the user can save the records – when I then want to append some new records (either to existing portal or another table). The fields in the newly added records may be a mix of exact copies, default text or calculated values of the original portal records.

       

      In its simplest form, say, three records are entered, by the user, into the portal.  I will then script three additional records with each field either being the same as its original user entry value, or a global default value or a calculated value.

       

      In all of the online videos, training DVD's and examples I’ve reviewed I’ve not found this task specifically detailed. However a Knowledgebase article “How to copy data to one field from multiple portal records” encourages me to think it is possible.

       

      Many thanks for your assistance and guidance.

       

      Dave

        • 1. Re: DUPLICATING/COPYING RECORDS
          TSPigeon

          piaccounting:

           

          Thank you for your post!

           

          One option might be to Set Variables for the fields you want, then Set Field into the new records on the separate table.

           

          I'm also going to move this thread from the FileMaker Community Feedback Space, which is specifically for input on the Community itself, to the Discussions Space where you should receive more views and potentially more feedback on this topic!

           

          TSPigeon

          FileMaker, Inc.

          • 2. Re: DUPLICATING/COPYING RECORDS
            philmodjunk

            From such a general description, I can list multiple options, not all of which may apply to the same situation.

             

            Duplicate Record can do exactly what it says. The duplicated record is exactly the same except for an auto entered serial number if such a field is present.

             

            Import Records can copy blocks of records from one table to another. The source and target tables can be tables in the same file  but not the same table.

             

            Like any relational DB a set of default records could be set up in a related table. Setting a foreign key to a matching value can trigger a "look up" that copies data from any number of fields in the template record to the new record in the other table.

            1 of 1 people found this helpful
            • 3. Re: DUPLICATING/COPYING RECORDS
              siplus

              Independently of the method chosen to create the copies, I would create a UUID associated with all the portal records created in one session by a user, so that it's easy to get them together via Find, ExecuteSQL etc.

               

              user wants to start adding lines:

               

              $$CurrentUUID = Get(UUID)

               

              insert $$CurrentUUID in a field belonging to each new portal row

               

              user completed task:

               

              $$CurrentUUID = ""

              1 of 1 people found this helpful
              • 4. Re: DUPLICATING/COPYING RECORDS
                piaccounting

                Thank you all for your very helpful replies. Firstly to TSPigeon for moving the post to a more suitable “space” and also to philmodjunk and siplus for their comments which has led me to revisit some of the presumptions I had made.

                 

                I had believed that importing records related to imports from either another file (solution) or from an external source. Similarly I thought that SQL commands applied to manipulating data outside of Filemaker only. Thank you for the gentle reminder to include the unique ID!

                 

                I now believe I may be able to solve my problem with an SQL statement. Therefore may I ask if it is possible to write an SQL command purely within Filemaker pro 15 Advanced to execute a command (notwithstanding correct syntax/construct) the gist of which is?:

                 

                Insert into newtable from portal field1 as field1, field2 as field2, “gText” as field3, field4 * -1 as field4 group by field1, field2 Order by…..

                (Again not looking for the solution - just trying to understand what Filemaker can do).

                 

                If so, is there a current equivalent for Filemaker of what I think used to be called TSQL – it showed the way to properly construct everything?

                 

                Sadly the much documented 30-day trial turns out to be only 14/15 days, so I am now without! However I am optimistically looking for reasons to buy and not reason not to buy.

                 

                Thanks again for all your help.

                 

                Dave

                • 5. Re: DUPLICATING/COPYING RECORDS
                  philmodjunk

                  Most general operations in FileMaker are not done with SQL. If by "insert into newtable", you want to create a new table "on the fly", then that is something rarely done in FileMaker. There are, generally speaking, ways to accomplish what you want that don't actually create new tables during the execution of a script.

                  1 of 1 people found this helpful
                  • 6. Re: DUPLICATING/COPYING RECORDS
                    piaccounting

                    Hi philmodjunk thank you for your reply. By "newtable" I was only trying to distinguish it from the portal table.
                    It would – most probably - be an existing table.

                    My concern was that, being new to Filemaker, I couldn’t see examples of additional records being appended to another table based on user input and where those additional generated records were, in some cases based on sub totals of the original entries. 

                     

                    Therefore before buying I was trying to avoid the “No it cannot do that – why didn’t you say?” scenario. Once again thank you very much for your advice – it is very much appreciated.

                     

                    Dave

                    • 7. Re: DUPLICATING/COPYING RECORDS
                      philmodjunk

                      Yes, a script can take  user input, create a new record and populate fields with that user input.

                       

                      But, generally speaking, it's not done using SQL.

                       

                      One of the mental "humps" developers experienced with other SQL based systems have to climb is that most tasks in FileMaker are not done using SQL. You reach inside your "toolbox" for a familiar, much used tool only to find that it doesn't work for FileMaker and you have to find and use a completely different tool to get it done.

                      1 of 1 people found this helpful