4 Replies Latest reply on Feb 16, 2009 5:59 PM by hiatts

    Comparing external file (text or Excel) with database before input



      Comparing external file (text or Excel) with database before input


      I am a old Filemaker user and have really only dabbled with the later versions. I have developed systems in VB6 and used SQL but now have an opportunity to develop a full blown system in FM. It will have upwards of 20 users. It will require input of media (about 15,000 images/models per month) and will run to several terabytes of data (external to FM but referenced in).

      I have a number of challenges but the main one (I think) is that the user will present a text or Excel file with 200 plus records of information which needs to be compared against the existing database - both prior to import and also for export. In SQL I would create a temporary table and do the work between the tables. I have already done something similar in Vb by importing my data into arrays and comparing that with database. There does not seem to be a temporary table available in FM10 and I need to keep user1's local file data separate from user2's. The 'input' file is likely to have around 20 fields of information per line, and I may need to compare more than one field although usually one will be the key.

      Does anyone have ideas how I might tackle this?  

        • 1. Re: Comparing external file (text or Excel) with database before input

          Hi tonyh27 and welcome to the forum


          You cant create a temporary table and setup relationships on the fly in FileMaker unfortunately, so your best approach would be to create a new table in the database just for storing the imported data and comparing to your real data, and to tackle the issue of multiple users I would suggest a UserID being set in an additional field and Global Variable [$$] and use that to distinguish between sets. And then when the comparison / Import / Export is complete you delete all that users records.


          Does this give you an idea of how to go about this?


          If you need any more detail on this just post.

          • 2. Re: Comparing external file (text or Excel) with database before input

            Hi Orlando.

            Thanks for your input. I had sort of arrived at this as an option. However, since it is possible that more than one user may login with the same login name how would you make sure each had a unique ID? I could create a table with Ids which increment sequentially on each logon - I think that would work, but you may have a better idea.

            I am still finding my way through the functions so there may be a unique id anyway and I missed it. 

            • 3. Re: Comparing external file (text or Excel) with database before input

              You could use a timestamp [ Get ( CurrentTimestamp ) ] or the system IP address [ Get ( SystemIPAddress ) ] or a combination to create a temporary unique identifier for that import.


              So you could generate a string using something along the lines of the following:


              Filter ( get ( currenttimestamp ) & Get ( SystemIPAddress ) ; 0123456789 ) 


              This will remove everything apart from numbers from a combination of Timestamp and IP address.

              • 4. Re: Comparing external file (text or Excel) with database before input

                have done what you are trying...


                yes you use a predefined temp table, rather a permanent table that is used to validate data prior to load into main table (tables if normalised) 



                1. make use of the Access and Privileges to id the users.


                2. on each table where required add an auto-enter field with Get(AccountName) as the formula. This will ensure the data is tagged with whoever added the data to the table.


                now just vision... the user logs in with their individual id (account name) and when they import to the tables you set this way, each record has a field with their account name in it.


                3. after import you merely enter find mode and do a find on that account name, or even just use * wildcard, and the user will only see the records they are permitted to (this one only if you set privileges to be view records if createdby=get(accountname)


                4. make sure you delete before import, and filter for accountname prior to delete.


                5. once you have filtered imported data do what you want with it... use relationships, checks accross fields, calculate fields...


                when you are happy, just use fmp import from the fmp file and import from the temp table to the main table on the found set in the temptable.


                good luck