9 Replies Latest reply on Jan 31, 2013 12:47 PM by philmodjunk

    New Group of Records from a Found Set

    KatieSullivan

      Title

      New Group of Records from a Found Set

      Post

           Hi,

           I am fairly new to Filemaker, so please forgive me if I am not totally clear in my explanation here.

           I have a database that contains 3 tables: "Contacts", "Events", and join table "Invitees".

           I am able to add individual contacts to specific events, mark RSVPs for each individual event, etc.  The way I am currently adding guests to my events is through a portal set up in my "Contacts" layout which displays data from the "Invitees" table (I use a pop-up menu to select the Event's primary key: __pkEventID.  Each portal row is a new event that the contact has been added to.  I know I can do this the other way around by creating a portal in the Events table and choosing contacts to be added, but because I have about 8,000 contacts it is much faster to choose an event from a pop-up menu then it is to choose a contact from a pop-up menu).

           I also have a Guest Report layout that shows all of the records in the Invitees table.  I have the records sorted by event name, then by contact last name.  I also have button set-up on my Events layout that will take me to the Invitee records for that particular event only.

           My question is:  If I perform a find for a particular group of contacts (for example everyone who attended Test Event A), how can I take that found group and add the whole thing to another event?  As of now I have only been able to figure out how to add each person individually.

           Thanks for any help and I am happy to give further clarification if needed.

        • 1. Re: New Group of Records from a Found Set
          philmodjunk

               Do you know how to create a script?

               If your found set is a found set of Invitee records, a script can loop through them using duplicate record to duplicate them and using set field to assign the new event ID to each record thus produced.

          • 2. Re: New Group of Records from a Found Set
            KatieSullivan

                 That sounds like the solution I need.  Would I then create a button that runs the script?  The found set would have to be in the invitees table, right? (It couldn't be a group that I found in the contacts table displaying info from the invitees portal?)

                 Lastly, what if I have an exisiting event that I want to add the group to?  Would that need to be a different script?  Something like add found set to event, and then choose the event ID from a pop up menu?

                 I am understanding this but just need some help in the script writing department.

                  

                 I know where to go to create a script etc. but don't know how to actually create one from scratch (I've done a few very simple ones like find a specific set of data when entering a layout).  Any chance you can help me in that respect?

                 My Event primary key field is __pkEventID

                 Contact primary key field is __pkContactID

                 Invitee match keys are _fkContactID and _fkEventID (not sure if you need this info, but there it is).

                  
            • 3. Re: New Group of Records from a Found Set
              philmodjunk
                   

                        Would I then create a button that runs the script?

                   That would be the simplest way to set this up.

                   

                        The found set would have to be in the invitees table, right? (It couldn't be a group that I found in the contacts table displaying info from the invitees portal?)

                   A found set of Invitees or a found set of contacts could be used to produce this result. The details, particularly how the correct event is specified will differ depending on which set of records you work from.

                   

                        what if I have an exisiting event that I want to add the group to?  Would that need to be a different script?  Something like add found set to event, and then choose the event ID from a pop up menu?

                   It might be possible to use the same script via a script parameter or values in a global field or variable, but for an existing event, I would select the event before adding the found set.

                   Your relationships as I understand them:

                   Contacts::__pkContactID = Invitees::_fkContactID
                   Events::__pkEventID = Invitees::_fkEventID

                   The details of the script will vary a bit depending on the starting context. For this example, let's say that you have found an Event on an event layout and you want to generate invitees records for a new Event not yet created in your database:

                   If [ Not IsEmpty ( Invitees::__fkEventID ) // make sure that there are invitees records to work with]
                     Freeze Window
                     Go To Related Record [Show only related records; From table: Invitees ; Using layout: "Invitees" (Invitees)]
                     Go to Layout [Events]
                     New Record/Request
                     Set Variable [$EventID ; Events::__pkEventID]

                     Go To layout ["Invitees" (Invitees)]
                     Go To record/Request/Page [First]
                     Loop
                        Duplicate Record/Request
                        Set Field [Invitees::__fkEventID ; $EventID]
                        Omit Records---> omit newly created invitees record
                        Go to Record/Request/page [First]
                        Omit Records---> Omit original that was just duplicated
                        Exit Loop if [ Get ( FoundCount ) = 0 ]
                     End Loop
                     Go to Layout [Original Layout]
                   End If

                   If you wanted to invite this group to an existing event, the steps shown in Blue would need to be changed. One way to handle the script differences between inviting a group to a new event or an existing event would be to add an If Else EndIf block to this script.

                   A script to do this from a set of contacts records would need to loop through records on a contacts layout, shifting to an Invitees layout to create the new records in Invitees before shifting back to go to the next contact record. ID values for event and contact would be kept in variables to move that data from table to table.

              • 4. Re: New Group of Records from a Found Set
                KatieSullivan

                     Thanks so much.  Quick question before I try this.

                     Where you say:

                "For this example, let's say that you have found an Event on an event layout and you want to generate invitees records for a new Event not yet created in your database:"

                     Does this mean that I should run the script from the Events layout, on the event that contains the list of people I want to add to the new event?  What if I do not want to invite all guests from that particular event, but rather a selected group (for example, everybody who did not RSVP).  How do I sepcify which invitee records I want to duplicate?  

                     Is it easier to find the group of people in the Invitees layout (just using a simple find with the event name and RSVP fields) and then run the script from there duplicating only those particular records and adding them to a new event?

                     Sorry if this is exactly what you just told me and I am reading it incorrectly.

                • 5. Re: New Group of Records from a Found Set
                  philmodjunk
                       

                            Does this mean that I should run the script from the Events layout, on the event that contains the list of people I want to add to the new event?

                       That is the assumption I used in creating this example script.

                       

                            How do I sepcify which invitee records I want to duplicate?

                       That would depend on the design of your layout (how do you know who didn't RSVP?) There are several different approaches you could use to work with that. A script can perform a find instead of using Go TO Related Records or after the Go To related records, the resulting found set can be constrained to just the specified subset of related records (all that didn't RSVP for example).

                       And there can be different ways to specify different criteria to use. You can have several different buttons all set to pass a different script parameter to the script to tell it what to do. A drop down list could be used or even a check box field.

                       But all of these are variations on the same method. Once you have the EventID in a variable and the correct set of records in Invitees, all these different options would loop through the set of invitees records as shown in the sample script.

                       If you have never used a script to perform a find, you may find this thread helpful:  Scripted Find Examples

                  • 6. Re: New Group of Records from a Found Set
                    KatieSullivan

                         Hi, 

                         Thanks so much for your help.  I have been working with these scripts in various capactites and seem to be having some luck getting what I need.

                         I have another question for you now.  When the records duplicate, they include all of the previous event's information along with them - AKA the response, # of guests, any notes - things that are specific to that particular event (the fields that are in the "Invitees" table).

                         I can clear that information out manually, but is there a script step I can add that would automatically clear the information out for me?

                         Separate from that I have another question that is a little trickier.  I currently have the "Email" field of my "Invitees" table set as a lookup field that finds the and inputs the work email address  stored in my "Contacts" table for that person.  Currently the lookup is triggered by creating a new "_fkContactID".  The reason I have this as an "Invitee" field and don't just simply use the field from the contacts table is because for certain events we need to change an email address, but want that change to only apply to the one particular event. 

                         When I use the script you just helped me with above, it duplicates the records but does not seem to trigger the lookup.  I would like the emails to come from the work email stored in my contacts, not the email used for the previous event.

                         Anny suggestions?  Thank you again, you have been immensely helpful so far.

                    • 7. Re: New Group of Records from a Found Set
                      philmodjunk

                           Set Field [YourTable::Field ; "" ]

                           will clear that field.

                           and

                           Set Field [Invitees::EmailAddress ; Contacts::EmailAddress]

                           Will reset that field back to the contacts original email address.

                      • 8. Re: New Group of Records from a Found Set
                        KatieSullivan

                             Oh perfect.  And those should be included within the loop portion of the script, correct?

                        • 9. Re: New Group of Records from a Found Set
                          philmodjunk

                               Right after using Duplicate Record to create the new duplicate record.