2 Replies Latest reply on Oct 29, 2015 6:57 PM by angelal

    Writing a script to import into various tables


      Hi everyone

      I am in the process of putting together a few scripts that are going to do a couple of imports and I need some help.


      What I need to achieve is a script that imports nominations into a table called "Nominations" these nominations are of umpires who are being nominated by their local umpiring group to trial for a position on the state league panels for the following year, it is once per year process.


      These nominations are completed on a web form that is exported to Excel and then I want to import them into FM


      The import into the Nominations table itself isn't the problem but what I then want to do is to create a new record in my "People" table if that umpire isn't already in the database and link the nomination to the person by the Person UUID, this is where I am coming unstuck.


      I have tried first importing into the People table and using the update matching records based on their first and last names and then import the nominations as a second step however I can't see how to match up the UUID in the People table to the match field I have in my Nominations Table


      Gosh I hope that make sense to someone out there :-)


      Any advice would be appreciated

        • 1. Re: Writing a script to import into various tables

          Hello, Angela.


          What you want to do is certainly possible. What you'll likely need to do is create a table just for the imported data. It can be a duplicate of the Nominations table (with unnecessary bits stripped out, if you like). Then, create a relationship between NominationsImport and People, based on the two fields FirstName and LastName. This will establish which records exist - or not - in People.


          At this point, you can Find all the records in NominationsImport that don't have a match in People just by doing a Find on any related field in People and selecting Omit (the UUID is a good choice). Import those records to People.


          Big, large, huge, ginormous caution here: Doing a relational match on first and last name is very, very risky. Duplicates are quite common. (How many "Bob Smiths" and "Jane Browns" have there been in the last 50 years?) You can somewhat mitigate it by comparing other data, like street address or phone, but I would recommend you see if there's a way to get a unique key of some sort on the incoming data to prevent missing one who's a name duplicate, but not actually the same person.




          • 2. Re: Writing a script to import into various tables

            You have no idea how much that has help Mike, thank you!!

            Yes you are right about the First Name and Last Name matches because it also matters when the spelling is different. Or we have a Mike or Michael, Matt or Matthew etc. For now I will go ahead just to get the first lot of info in there and then once it becomes a yearly process I will change it to Last Name and DOB.