14 Replies Latest reply on May 27, 2010 1:30 PM by Miyamoto

    Duplicating records with portal information intact

    Miyamoto

      Title

      Duplicating records with portal information intact

      Post

      Help!

       

      I'm a newb, creating a database based on the "Events Management" template. I'm using Filemaker Pro 10 on OSX 10.6.3. I really haven't changed the structural hierarchy of the database template at all -- tables and layouts have retained their names for the most part.

       

      What I'm looking to do is find a way to duplicate Event records and retain portal information from the record being copied. The events that I'm managing in many cases happen over the course of several days with slightly varying contacts associated -- but in some cases its 40 contacts that I'm dealing with, and there must be a better way than re-entering the data for each event instance (I need to keep each day as a separate record for budgeting purposes).

       

      Any advice? Thanks in advance!

        • 2. Re: Duplicating records with portal information intact
          ryan

          are the contacts in a separate table?

          if so, what relationship have you established betweens the Contacts table and the Events table?

          • 3. Re: Duplicating records with portal information intact
            philmodjunk

            I see two ways to interpret your post:

             

            1) you want the duplicated record to display the same exact records in its portal as the the original

             

            2) You want the portal records to be duplicated also and these duplicates should appear in the portal fo the new parent record.

            • 4. Re: Duplicating records with portal information intact
              Miyamoto

              Hi Phil,

               

              Option one -- I'd like the duplicated record to display the exact same records in its portal as the original. Any help would be HUGELY appreciated.

               

              Thanks!

              • 5. Re: Duplicating records with portal information intact
                Miyamoto

                Yes, contacts are in a separate table. I haven't altered anything from the template's format, in terms of relationships.

                • 6. Re: Duplicating records with portal information intact
                  philmodjunk

                  I don't think you really want to do that. You have three tables involved, not two.

                   

                  This is a classic Many to Many relationship as many contacts can be linked to many different events.

                  Events---<Events_Contacts>---Events_Contacts_Contacts   (Events_Contacts_Contacts is a table occurrence of Contacts)

                   

                  Your portal connects to Event_Contacts not Contacts ( or Events_Contacts_Contacts).

                   

                  This structure is used to link contact records to events. You may want to schedule the same list of contacts for two or more different events, but you would not do this by pointing two events records to the same group of Events_Contacts records. If you did that, removing or modifiying one of these rows would always produce the same change in both Event records.

                   

                  Instead, you would use a script to duplicate the Event_Contacts records as well as the event record if you want to start with the same list of contacts as are already scheduled for the previous event.

                   

                  Make sense?

                  • 7. Re: Duplicating records with portal information intact
                    Miyamoto

                    Hi Phil,

                     

                    Yes, that absolutely makes sense -- you're right, I need to be able to delete and/or add contacts to events without effecting other event instances. Ideally, my workflow would go something like this: populate an event with guests, then duplicate the event, change the date, and make small changes (in some cases, no changes) to the guests.

                     

                    Thanks for clarifying. :)

                    • 8. Re: Duplicating records with portal information intact
                      philmodjunk

                      You'll need a script that isolates the Event_Contacts records in a found set and loops through them, duplicating each and assigning them the new Event ID value needed so they appear in the portal of the duplicated record.

                       

                      Here's a link to another thread where this same issue was discussed:

                      http://fm.lithium.com/t5/Using-FileMaker-Pro/Copy- Records-with-Portals/m-p/49049#M38360

                      • 9. Re: Duplicating records with portal information intact
                        Miyamoto

                        Thanks! I'll give it a shot and let you know how it goes!

                        • 10. Re: Duplicating records with portal information intact
                          Miyamoto

                          Hi Phil,

                           

                          Okay -- couple of questions (and boy, do I hate asking 'em. My wife is always getting mad at me for not asking directions when we're driving, and I'm usually the same when it comes to computer stuff... :) ).

                           

                          So, the following is the script laid out in the linked example you provided.

                           

                          If [Not IsEmpty(gNewNumb) /* make sure a number was first entered in this field */]

                             #duplicate any portal records

                             IF [count (PortalRecs::MDO) > 0 /* there are portal records to duplicate */]

                                 Go to Related Record [ From table: “PortalRecs”; Using layout: “PortalRecs” (PortalRecs) ] [ Show only related records ]

                                 Go to Record/Request/Page [ First ]

                                 Loop

                                    Duplicate Record/Request

                                    Set Field [ PortalRecs::MDO; Main::gNewNumb ]

                                    Omit Record

                                    Go to Record/Request/Page [ First ]

                                    Omit Record

                                    Exit Loop If [ Get ( FoundCount ) = 0 /* All records have been duplicated */ ]

                                 End Loop

                                 #Duplicate Parent Record

                                 Go to Layout [ original layout ]

                                 Duplicate Record/Request

                                 Set Field [Main::MDO ; Main::gNewNumb ]

                          End IF

                           

                          I suppose that my first problem is with the gNewNumb field -- I'm not sure that I'm implementing it properly (mainly because I don't really understand what it's supposed to be doing). 

                           

                          Now, what I've got going is this:

                           

                          If [not IsEmpty(Events::gNewNumb)] not sure if this is correct to do? When I input as it appears above, I get a "The specified table cannot be found." message.

                                 If [Count (Events_EventsGuests::EG_Character) > 0]

                                 Go to Related Record [Show only related records; From table: "Events_EventsGuests"; Using Layout: "Events_EventsGuestsLayout" (Events_Events Guests)]

                           

                          the syntax here is different than in your example, and I'm not sure if it's either important or how to change it.

                           

                                 Go to Record/Request/Page [First]

                                 Pause/Resume Script [Indefinitely]

                                 Loop

                                    Duplicate Record/Request

                                    Set Field [Events_EventsGuests::EG_Character; Events::gNewNumb]

                                    Omit Record

                                    Go to Record/Request/[Page [First]

                                    Omit Record

                                    Exit Loop If [Get (foundCount) = 0]

                                 End Loop

                                 Go to Layout [original layout]

                                  Set Field [Events_EventsGuests::EG_Character; Events::gNewNumb]

                           

                          I suspect this is where my breakdown is happening, (though I may well have managed to break it before arriving here!): in your original example, you'd used Main::MDO, but I don't have an corresponding EG_Character field in my Events table, so I'm not understanding this step.

                           

                                 End If

                          End If

                           

                          My result after running the script is that, upon entering into the gNewNumb field, the EG_Character field's data is (predictably, I suppose) replaced with the number from the gNewNumb field (though the rest the information on that line is correct. However, all of the duplicates are appearing on the original Event record. For example, if the first column were EG_Character, I'd get something like:

                           

                          Joe          Mark

                          Frank      Sam

                          Sonny     Jim

                           

                          Now I have:

                           

                          Joe          Mark

                          Frank      Sam

                          Sonny     Jim

                          3              Mark

                          3              Sam

                          3              Jim

                           

                          If "3" had been entered into gNewNumb. The new record that is created has all of the non-portal information intact, but within the portal, I have one entry:

                           

                          3

                           

                          ...with no other information on that line.

                           

                          Any thoughts? Thanks so much for your help!


                          • 11. Re: Duplicating records with portal information intact
                            philmodjunk

                            Hmm, I seemed to have glossed over some key details... :smileysad:

                            The main difference is that in the other thread, the user wanted to specify the ID field that was linking the main record to the portal record where in your case, the ID field's value will be automatically generated when you duplicate the record. 

                             

                            First, you need a layout for events_contacts. It can be blank, it just needs to refer to events_contacts or Events_EventsContacts in the Show records from box.

                             

                            For you, the first part of the script should read:

                             

                            #Duplicate parent record first We'll do this first because the primary key is auto-entered when the record is duplicated

                            Duplicate Record/Request

                            Set Variable [$NewID ; Events::kp_ID_Event] //I used a global variable in the previous thread, a variable makes more sense in your case

                            Go To Record [ Previous ]

                            If [Count ( Events_EventsContacts :: kf_ID_Event ) > 0 ] //any non-empty field in this table will work

                                  Go to Related Record [ From table: “Events_EventsContacts”; Using layout: “Events_Contacts” (Events_EventsContacts) ] [ Show only related records ]

                                   Go to Record/Request/Page [ First ]

                                   Loop

                                      Duplicate Record/Request

                                      Set Field [ Events_Contacts::kf_ID_Event; $NewID]

                                      Omit Record

                                      Go to Record/Request/Page [ First ]

                                      Omit Record

                                      Exit Loop If [ Get ( FoundCount ) = 0 /* All records have been duplicated */ ]

                                   End Loop

                                   #Don't duplicate the original record, you've already done so at the start.

                                   Go to Layout [ original layout ]

                                   Go To record [next]

                            End IF

                            • 12. Re: Duplicating records with portal information intact
                              Miyamoto

                              Hi Phil,

                               

                              Sigh... No joy.

                               

                              With the following:

                               

                              Duplicate Record/Request

                              Set Variable [$NewID; Value:Events::kp_ID_Event]

                              Go to Record/Request/Page [Previous]

                              If [Count ( Events_EventsContacts::kf_ID_Event ) > 0]

                                    Go to Related Record [Show only related records; From table: "Events_EventsContacts"; Using layout: "Events_EventsContacts_Layout' (Events_EventsContacts)]

                                    Go to Record/Request/Page [First]

                                    Loop

                                         Duplicate Record/Request

                                         Set Field [Events_EventsContacts::kf_ID_Event; $NewID]

                                         Omit Record

                                         Go to Record/Request/Page [First]

                                         Omit Record

                                         Exit Loop If [Get ( FoundCount) = 0]

                                    End Loop

                                    Go to Layout [original layout]

                                    Go to Record/Request/Page [Next]

                              End If

                               

                              I end up with a duplicated event record, but nothing happens within the portal. However, with the following:

                               

                              Duplicate Record/Request

                              Set Variable [$NewID; Value:Events::kp_ID_Event]

                              Go to Record/Request/Page [Previous]

                              If [Count ( Events_EventsGuests::kf_ID_Record ) > 0]

                                    Go to Related Record [Show only related records; From table: "Events_EventsGuests"; Using layout: "Events_EventsGuests_Layout' (Events_EventsGuests)]

                                    Go to Record/Request/Page [First]

                                    Loop

                                         Duplicate Record/Request

                                         Set Field [Events_EventsGuests::kp_ID_Record; $NewID]

                                         Omit Record

                                         Go to Record/Request/Page [First]

                                         Omit Record

                                         Exit Loop If [Get ( FoundCount) = 0]

                                    End Loop

                                    Go to Layout [original layout]

                                    Go to Record/Request/Page [Next]

                              End If

                               

                              ...then I do manage to duplicate the records within the portal with all info intact -- however, they're duplicated within the original event record (following their original occurrence (A, B, C, A, B, C), and the duplicate event record has a blank portal. I'm sure I'm blowing it somewhere, but clearly, I'm only smart enough to get myself into trouble, but not to get back out of it. :)

                               

                              Any ideas? Thanks again for your help.

                              • 13. Re: Duplicating records with portal information intact
                                philmodjunk

                                Hmmm,

                                 

                                I see we were working with different portals :smileywink:

                                 

                                If you are trying to duplicate the guest list found on the guests tab, then your changes make sense except for one line:

                                 

                                 Set Field [Events_EventsGuests::kp_ID_Record; $NewID]

                                 

                                Should be:

                                 

                                Set Field [Events_EventsGuests::kf_ID_Event; $NewID]

                                 

                                I'm selecting this field because the relationship for this portal, as shown in Manage | Database | Relationships is

                                Events::kp_ID_Event = Events_EventsGuests::kf_ID_Event

                                • 14. Re: Duplicating records with portal information intact
                                  Miyamoto

                                  Phil, you're a freakin' genius. Can't thank you enough -- this is going to save me hours over the next few months.