8 Replies Latest reply on Feb 17, 2014 7:04 AM by philmodjunk

    Duplicate records with fields from another related table.

    DV

      Title

      Duplicate records with fields from another related table.

      Post

           Hi there.

            

            I have 2 tables set up in our database.

            

           1. an event element in first table.

           2. Schedule relating to the element in a second table. Appearing via a portal.

            

           I need a way  to do the following:

            

           1. Select a button 'duplicate event element' 

           2. When button is pushed, duplicate selected fields from 'event element' into a new record with its own automatically generated event element ID number.

           3. Duplicate certain related fields from the related schedule.

            

           Are you able to assistt?

           how do I select only certIn fields to duplicate when the button is pushed in the first table?

           how do I get the schedule to duplicate specific fields too? This part is always stuffing up in my trials...

            

           The schedule table and event element table are linked by a id number.

            

           any help with the appropriate script would be greatly appreciated.

            

           thanks!

        • 1. Re: Duplicate records with fields from another related table.
          philmodjunk

               I would use duplicate record to duplicate the records involved and then use set field [ YourTable::Field ; "" ] to clear each field that you didn't want duplicated.

               This thread may show you one say to produce the duplicate records: Duplicating Bill Of Materials (duplicating portal line items)

               Be sure to use the example script at the end of the thread as the original version has a bug.

          • 2. Re: Duplicate records with fields from another related table.
            DV

                 Hi,

                 Thanks for that.

                 I just gave this a go but it only duplicated the main record, not the information in the portal.

                 I have attached a screen shot of the script.

                  

                 Is there anything else I should be doing?

                 I have also made the Event Element ID Match field in the schedule table have a calculation: $$CURRENT_TASK_ID

                 Is this correct?

            • 3. Re: Duplicate records with fields from another related table.
              DV
              /files/62617194e4/Screen_Shot_2014-02-06_at_11.04.16_am.png 1136x447
              • 4. Re: Duplicate records with fields from another related table.
                philmodjunk

                     Yep. Better review my suggested script again. The script that I am referring you to has TWO duplicate records steps. One duplicates the parent record and the other duplicates the child records in the portal.

                • 5. Re: Duplicate records with fields from another related table.
                  DV

                       Hi,

                       I have had a look at the script again and tried numerous different things.

                       As far as I can see the script does have two duplicating steps.

                       It will duplicate information from the first table but not the information in the second table that is linked via a portal.

                       Any insights?

                  • 6. Re: Duplicate records with fields from another related table.
                    philmodjunk

                         That it does, don't know how I missed unless the Pnuemonia that has had me down for three weeks had something to do with it.

                         But you have a set field step that is incomplete:

                         Set Field [ Schedule::Event Elements ID Match field ; $Current_Event_Elements_ID ]

                         The portion in red is missing.

                         When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                    • 7. Re: Duplicate records with fields from another related table.
                      DV

                           I just added that part in.

                           Attached is a screen shot.

                           It will still only duplicate the first record, not the elements.

                           Do I need to put in somewhere exactly what fields in the portal should be duplicated?

                            

                            

                      • 8. Re: Duplicate records with fields from another related table.
                        philmodjunk

                             You aren't duplicating fields, you are duplicating records with this script. All the fields defined in each of the two tables are duplicated when the record is duplicated though fields with an auto-entered serial number still auto-enter a new unique value.

                             Did you replicate the first version of this script or the one at the end of the discussion thread? This looks like the original version, which another forum member pointed out could fail if you had more than one parent record in your found set at the time you performed the script. There's another version at the end of the thread that corrects that issue. Maybe I'm not remembering accurately, but this looks like the original version.

                             If you have FileMaker Advanced, I suggest running this script with the debugger enabled and stepping through it to see where you arent' getting the expected results. If you do not have advanced, putting so show custom dialogs temporarily into the script can help you watch it work step by step. You might insert such immediately after the Go to Related Records step (to make sure that it changes layouts as expected) and inside the loop--to see if the loop is executing and how many times.