8 Replies Latest reply on Mar 28, 2013 9:26 AM by datarunner

    Guidance Needed, Adding Relational Data

    datarunner

      I'm seeking some guidance/ideas on the best way to handle the on-going addition of new data.

       

      Background:

      I have thousands of records containing recruit contact information that we may get on our own, or may be sent to us from our clients. If a list of recruits are sent to us from a client, they can only be contacted for specific projects which we refer to as a "closed list." However, at times we receive a recruit that we already have on our own recruit list, or possibly we have on a seperate closed list from a different client. We need to be able to track all instances. If they are ours, client a, client b, etc.

       

      Currently, I have a "Recruit" table that assigns a system generated unique number when someone is added. The "Recruit" table has a one to many relationship with the table "ClosedList". Lists of recruits are sometimes added using imports (which I perform), and sometimes recruits are added by support staff one by one. I'm looking for the best way to automate the following whether it's done using an import or one-by-one using a support staff interface/layout:

       

      1) Enter/import a new recruit(s) (name, email, phone, etc.). Only unique email addresses are allowed but it is possible to override for special circumstances.

      2) Automatically find any email address, first name, last name that already exist in the DB

      a) If the recruit already exists

      1) Flag the recruit that they have at least 1 record in ClosedList

      2) Add the UniqueID to the ClosedList and the ListName (this could be manually entered for one-by-one entry, or would already exist in a xlsx being imported)

      b) If the recruit does NOT already exist

      1) Add all appropriate fields to the Recruit table, including a ClosedList flag

      2) Create a record in the ClosedList table containing the UniqueID the system generated as the recruit is added as well as the ListName (manually entered for one-by-one entry, or automatically from the xlsx import)

       

      I'm not looking for a specific answer necessarily, but direction on good ways to go about this and things to keep in mind that may cause issues.

       

      Thank you,

      Leah

        • 1. Re: Guidance Needed, Adding Relational Data
          taylorsharpe

          I cringe when I see different data being kept in a table with the same field names.  Except for some unusual circumstances, you should make them all one table with many records and use a field to identify the differences between the groups.  If you set up the security properly, you can make sure people don't see other people's data in the same table.  The problem of breaking things up into multiple tables is that if you want a report of all the information, there is no one table with everything in it.  FileMaker doesn't do Unions, so the only way to make a combined report is to go take all those tables and temporarily import them into a new table and make the report from that table and then delete the table contents afterwards and make sure security keeps people from seeing that table that should not have access to everything.  What a pain. 

           

          My recommendation is that you have one people table for all people and you use coded fields to identify those who are recruists, closed or open, or internal staff or vendors or whatever.  But they should all be in one table or you will run into difficulties down the road.  I am also assuming you know how to set up security to keep people from seeing records that they are not authorized to do so through validation (not through layouts, conditional formatting or scripting). 

           

          That's my 2 cents for whatever little it is worth. 

          • 2. Re: Guidance Needed, Adding Relational Data
            datarunner

            This is a suprising response, due to the size and future size of the DB utilizing a relational DB is imperitve to ensure scalability and the proper normalization.  If I were to put it into one table I could have thousands of records with repeated data, or I would have to add a never ending list of new fields to flag for every possible client list.  Possibly I'm misunderstanding you?  I am viewing this as the typical 'line item to account"  data management solution needed but with automation to ensure I'm not adding new "accounts" when one already exists.

            • 3. Re: Guidance Needed, Adding Relational Data
              taylorsharpe

              Maybe we aren't communicating well and that's probably my fault.  I thought you were trying to keep a table of open people and a table of closed people.  It seems you are really only doing this at a job table.  So you should have a person table and a job table.  From the person table you have related tables of characterstics like the link to the job, coding of open or closed or phone numbers or addresses or emails.  Those would all be separate normalized tables with a relationship back to the person.  You would first create a person if they don't already exist and then you would create the job that is related to the person.  At least that is what I was thinking.  Does that make sense?

              • 4. Re: Guidance Needed, Adding Relational Data
                datarunner

                Yes, this is more what I'm trying to do.  I understand where you were going before now - it isn't that "closed list" means people can't see it.  It means that when we contact any of our recruits - they can only be contacted on behalf of work for that client specifically.  However sometimes, we can get the same contact information from multiple clients.  We need to be able to track which recruits can be contacted on behalf of which client.

                 

                Thanks for your patience, it's difficult articulating in a snapshot the business/data flow.

                 

                So now back to the original question, any ideas/guidance or examples on scripting/automation?  I'm not really sure where to start.....

                • 5. Re: Guidance Needed, Adding Relational Data
                  taylorsharpe

                  Is the issue that you need some way to test if a record is a duplicate or not... such as checking last name, first name, city, and state or something like that? If you are putting this in maually, what I do is create a search portal that has a script trigger that searches each time you type a character so it slowly narrows down your list to see if it already exists before clicking a button to create a new record.  If you are automatically importing, then you'll need a script to identify the potential duplicates and create a report where you can click a checkbox identifying the new duplicates so they won't stay in the database.  See if that is along the ideas of what you are wanting.  

                  1 of 1 people found this helpful
                  • 6. Re: Guidance Needed, Adding Relational Data
                    datarunner

                    Yes - thank you, this will get me started!

                    • 7. Re: Guidance Needed, Adding Relational Data
                      taylorsharpe

                      There are a number of examples on the web, but one of the "search as you type" examples with video is at:  http://sixfriedrice.com/wp/script-triggers-filter-as-you-type/

                      • 8. Re: Guidance Needed, Adding Relational Data
                        datarunner

                        Excellent!  Thanks again.

                        1 of 1 people found this helpful