6 Replies Latest reply on Apr 18, 2011 5:57 PM by tomo17

    Search data and create new record if needed



      Search data and create new record if needed


      I have a database that is imports data on a weekly basis.  the data relates to individuals within a group, however the individuals providing data can change on a weekly basis.  is there a way that i can script so that FMP searches and finds when a new individual provides data and then creates a related record for that individual in another table.  This record is used as a summary page of the data that is collected from that individual.  At this stage i have the individuals info table with information that has been manually entered related to the data import table (this currently works fine in bringing information across) but cannot work out how i can set it up so that it works "automatically" or by pressing a button to run a script.

      Many thanks


        • 1. Re: Search data and create new record if needed

          Your description is too abstract to provide substantial answer.  It seems that the answer would be to import (possibly just the name and GroupID?) directly into the other table but I have no idea without example file or a lot more details, sorry.  :^)

          • 2. Re: Search data and create new record if needed

            Thanks LaRetta

            i wasnt sure if i had given enough detail.  

            Table 1 - Individual's Info eg Name, Club, Number.  The layout has a portal that then draws information relevant to this person from table 2.  in the portal it has the last five entries for this person

            Table 2 - Data Import table.  This table imports data from an excel spreadsheet which is used to collect data.  This spreadsheet has data that is updated weekly.  Data includes Name, Club, then 4 x data fields relevant to their weekly performance.  Some individuals will have a data record for each week, whilst others may not.  There may be a new individual in one week that has not had a previous record.

            eg week 1 Name Date Club Field x Field y Field z

              john Smith 230311 A 6 8 10

            Alan Johns 230311 b 8 5 12

            Brad Brown 230311 A 5 10 4

               week 2 Name Date Club Field x Field y Field z

            john Smith 300311 A  4 3 10

              Alan Johns 300311 b 10 6 3

            Greg White 300311 A 6 7 4

            The two tables are linked by name. How do i get it to work so that a new record is created for the new individual in Table 1 (eg Greg White), without creating duplicate records for those that already have one (eg John Smith, Alan Johns)?  i dont want to do it manually as there are over 800 records already (there will be close to 4000 at its maximum) in table two and i would prefer not to have search manually for new entries each week.

            Thanks for any assistance.


            • 3. Re: Search data and create new record if needed

              Hi Craig,

              I realize it may be difficult to change an existing process but I am worried  that you are using the individual's name as the unique entry for table1.  What  if you get two John Smiths?  How will you tell them apart?

              Anyway, one answer is the set the name field in the Individuals table to  validate as unique, i.e. go to Name field and select Options and go to  Validation tab.  Under first section 'Validate data in this field' choose  'always' , uncheck 'allow user to override' and then select under Require  'Unique Value.'

              Do not put anything else (such as a message) since you want this automatic  when you import.  Now take your file which you will be importing into table 2  and import into table 1.  In this instance, you don't need to show all records  in table 1 first.  Specify only 'Add new records' and select only the name.

              If validation fails on this import, any records that exist (failed  validation) will not import.  Only unique names will import into your  Individuals table.

              As an aside ... I truly wish you could find a way to use unique IDs in the  Individual table and use those IDs to write back to the import table.  Names are  truly not dependable for the reason I indicated above AND because someone can  change their name (ask any young lady who recently married).

              Anyway, I hope this helps.  Let me know if I've missed the mark. :^)

              • 4. Re: Search data and create new record if needed

                Thanks LaRetta - Your solution seems to have worked for me!

                I have considered your suggestion about using a serial/unique code - is there a way that this can be done retrospectively such that it wont break what now appears to be working?


                • 5. Re: Search data and create new record if needed

                  You current have an existing relationship between Individuals and Imports based upon name so a transition would be easy.  It would be as follows:

                  In your Individuals table, create an auto-enter serial (type of number) increment by 1 called IndividualID (or whatever you wish).  Then go to a layout based upon Individuals.  Show All Records and then unsort them.  Place your cusor in this new IndividualID field and select from menu (back up first) ... Records > Replace Field Contents ... select 'replace with serial numbers' initial value of 1 and increment by 1.  Be sure it is CHECKED to update serial number in Entry Options.

                  You now have your Individuals serialized and any new records added (if you import be sure to specify 'perform auto-enter') will have the next serials in line.

                  Now go to your Import table.  Add a field called IndividualID (type is number).  Show all records and place cursor in this field and Replace Field Contents but this time, replace by CALCULATION which would be pointing at your Individuals table and the new IndividualID in it.

                  If your imported data in table 2 won't have this new serial, you will need to leave the existing relationship.  Then import into Individual first.  Then import into import table and, after your import and while you still have your found set, run Replace Field Contents again to set the newly imported records with their proper IndividualID from Individuals.

                  In the future, this ID should be incorporated in the imports as well to eliminate the issue of two people having same name.

                  • 6. Re: Search data and create new record if needed

                    Perfect - works as you suggested - many thanks!