8 Replies Latest reply on Jan 10, 2015 10:01 AM by philmodjunk

    Picking a specific record out of a large table

    PeterDurant

      Title

      Picking a specific record out of a large table

      Post

      I've asked a similar question in the past but am still struggling with a problem of picking a specific record out of a list and populating a field, I'm hoping someone can help.  What I have is a table of people who have attended specific events.  The table includes 30 million records, in the configuration of one record per person per event.  I have a separate database with all the people who could have attended the events and I created a field that can display the name of a specific event that a person attends.  Once the field is populated, it will never change.  

       

      I linked the tables together by unique ID for each person, but because the first table contains one record per person per event any time I populate the field it only populates it with the first instance of that person showing up in the first table.  How can I specifically pull the person for a specific event?

       

      Very frustrating for me. Thanks for any help.
      Peter

        • 1. Re: Picking a specific record out of a large table
          SteveMartino

          Trying to understand your structure.  I think you should have a basic structure of 3 tables:

          Event Table -> Event_Attendees <-People Table  Where Event_Attendees is a join table and a portal on the Event Table.

          Think of it like an Invoice, where Event=Invoice  -> Event_Attendees=Line Items <-People Table = Products.

          Then you can have in your people table a portal that shows events attended for that person only.  Your Event Table would have a portal for all people who attended that event.  With that in mind:

          On the Event_Attendees layout you would have a foreign key for Events, and a foreign key for People, that you would populate when adding attendees (people) to the event.

          I think this is better then making one record per person per event (which is actually what you should be doing on the Event_Attendees table and not the Event Table)

          • 2. Re: Picking a specific record out of a large table
            PeterDurant

            Thanks Steve,

            One of the problems is that I Events table which has one record per person per event is the the way the data came.  Since there are 30 million records and probably more than 50 events it will be a PITA to try and separate them out.  I'm hoping (maybe against hope) that I can pull the record out of the table and populate the field simply.

            Portals won't necessarily work because users create complex searches based on which events someone attends.

            • 3. Re: Picking a specific record out of a large table
              philmodjunk

              What you are dealing with is a classic many to many relationship with a very large number of records documenting the "join" between contacts and events.

              Contacts---<Contact_Events>-----Events

              If your imported data is reasonably well organized, You can use the tools found in FileMaker to populate all three tables from that single source--filtering out the duplicates as you go. It will take a lot of time while your system chugs through the data imports needed, but it can be done.

              Without doing that, it sounds like you need to match on two fields instead of just the Contact ID fields. You would match on both the person's ID and a specified event to pull up the record for that person's involvement in that event.

              This can be either a relationship using two pairs of match fields or you can enter the criteria into a pair of global fields and use a script to perform a find on your table looking for the record or records that match that data.

              • 4. Re: Picking a specific record out of a large table
                SteveMartino

                The frustration probably occurs from the database structure.

                You probably could script an import that would fix all the problems.  The 'complicated searches' could also become less complicated with the proper structure.  Also don't forget you can link external data sources to your FM DB too.  Personally, and I'm no experienced developer by any means, if it were me, I would consider putting the energy & resources into a one time fix to get the headaches to go away.

                Well you could try this, although I don't think its the best method.  When you select a person for the event, won't there be multiple records with that person's name?  I guess the first problem would be is the person's name unique?  What if there are multiple John Smith's?
                Let's say you have a list, and you can pick the person out of a list and populate the record you desire.  Then, the persons name field can be a button where you can attach a script, something like

                Set Variable [$id; PrimaryKeyFieldFromPeople]

                (if there are multiple fields, add multiple Set Variable steps)

                Go to Layout [Events] or Close Window (if the people layout is a second layout opened in the DB)

                Set Field [ForeignKeyFieldOnEvents; $id]

                Should populate the record with all the information.  Sorry I can't help you more

                Edit:  Just saw Phil's & I were typing at the same time...stick with him :)

                • 5. Re: Picking a specific record out of a large table
                  PeterDurant

                  Thanks Phi and Steve for the help.

                  My imported data is organized thus:  Event Date; Event Name; Contact ID; Contact Name.  The Event Date and the Event Name are not unique, but instead it is the combination of Event Date and Event Name that makes the event unique. (for example: 1/1/15 Local Meeting 01 Steve; 1/1/15 State Meeting 02 Phil; 1/20/15 State Meeting 02 Phil - as you can see, neither the date nor the name is unique but it is the combination that is unique).  As for duplicate records, I am 90%+ sure there are none.

                  I am using this table as an external FM Data Source.  Right now I have them matched on Contact ID but as we have stated I can only pull the first instance of the Contact ID to populate the field. 

                  I think I like the idea of the global fields to solve the problem, can you elaborate?

                  Thanks,
                  Peter

                   

                  • 6. Re: Picking a specific record out of a large table
                    philmodjunk

                    A relationship that matches by Contact ID, Event Date AND Event Name would appear to match to just one record in this table, correct?

                    If so, you can set up a relationship to match to this data.

                    But a scripted find where you specify the same three items in three global fields can also be used. See this thread for examples of scripted finds that work from data entered into global fields: Scripted Find Examples

                    It's also possible to use the ExecuteSQL to query such a table for such information.

                    And if this is an External Data source that is linked to FileMaker via ODBC, there's an import from ODBC option that allows you to set up the SQL query as part of the import.

                    • 7. Re: Picking a specific record out of a large table
                      PeterDurant

                      Thanks Phil,

                      A relationship that matches by Contact ID, Event Date AND Event Name would appear to match to just one record in this table, correct?

                      Yes, that is correct.  My skill level is still not to the point where I am not comfortable with the scripted find or the ExecuteSQL.  The other reason I want to stick with the three matches is that I think it will keep the application looking and feeling like what my customers are using now.  Currently, I have one table for each event and make a match based on ID.  I could do this again, but it comes with other problems that we don't need to go into.

                      So please pardon if this is a stupid/rookie question but...  If I do the three matches the ID is easy enough, but do I need a field in the Contacts table for each possible date and name and is it a global field?

                      Thanks,
                      Peter

                       

                      • 8. Re: Picking a specific record out of a large table
                        philmodjunk

                        No. And that would not work. You need one global date field and one global event name field. Format the global date field with a drop down calendar for picking dates if you find it helpful and format the EventName field with a use values from field value list listing the event names from your main table of data.

                        In manage database, you would then drag from these three fields one at a time from one table occurrence box to the corresponding 3 match fields in the other. (You can also drag to link the first two fields and then double click the relationship line to open up a dialog where it's easier to find the right fields to select as Match field pairs in your relationship.)