4 Replies Latest reply on Jul 15, 2009 9:26 AM by tran25

    How to update database from a comma delimited file

    tran25

      Title

      How to update database from a comma delimited file

      Post

      Hi,

       

      I would like some help in writing a scipt that would update our filemaker pro database from a scanner file that is comma delimited.  The scanner file contain date of the scanning transaction.  I only want to update the database if the date of the transaction is later than the date in the database.

       

      For example:

      The database contains the following field:  Date, Employee, Location, Part Number, Serial Number, Barcode.

      The scanning file contains Date of transaction, Barcode, Location, Employee.

       

      I want to update the database when the barcode matches but only if the date of transaction is later than the Date on the database.  If the date is later than update the Data, Employee, Location in the database.

       

      thank you for your time.

       

       

        • 1. Re: How to update database from a comma delimited file
          etripoli
             Import the file into a global field, and use a script to compare the dates, and update if necessary.  BTW - what barcode scanning system are you using?
          • 2. Re: How to update database from a comma delimited file
            tran25
              

            Sorry, I'm a newbie to filemaker pro.

             

            How do you import the file into a global field and write a script that would take the date in the global field and compare it with the date in the database?  An example would be great.  I will try to look online to learn more about filemaker pro to do this but any help would be appreciated.

             

            thanks for your time etripoli.

             

            The barcode scanner is a WASP WDT2200 programmable scanner. 

             

             

            • 3. Re: How to update database from a comma delimited file
              etripoli
                

              Oops, might have been thinking of a solution that is probably more complicated than needed.  If you're only dealing with a small number of lines to import, create a table (scan_import) with one text field (data), and a calculation field (parsed_data) that = Substitute ( data; ","; ¶ ) [type: text].  Create another calculation field (scan_barcode) that = GetValue ( parsed_data, 2). Relate that field to the Barcode field in your current table.  

               

               Here's what the script could look like:

               

              Go to Layout ["scan_import" (scan_import)]

              Show All Records

              Delete All Records [no dialog]

              Import Records [Add; ]

              Go to Record/Request/page [First]

              Loop

               If [GetValue ( scan_import:: parsed_data; 1 ) > part_numbers::date ]

                Set Field [part_numbers::date; GetValue ( scan_import:: parsed_data; 1 )]

                Set Field [part_numbers::employee; GetValue ( scan_import:: parsed_data; 4 )] 

                Set Field [part_numbers::location; GetValue ( scan_import:: parsed_data; 3 )] 

                Commit Records/Requests [No dialog]

               End If

               Go to Record/Request/Page [Next; Exit after last]

              End Loop

              • 4. Re: How to update database from a comma delimited file
                tran25
                  

                thanks etripoli,

                 

                After a few learning curves I was able to use the script you wrote and got it work.