3 Replies Latest reply on May 4, 2009 1:27 AM by ErikO

    Import data to modify existing posts



      Import data to modify existing posts


      I would like to import data to modify posts. I get a file from my bank with details about paid invoices. Predefined rows in this file include a refernce number which is my Filemaker invoice number. For those matching invoices I want to set stats to "Paid" and write current date. Here is an example of the file. Rows starting with "20" contains Invoice number in positions marked with red. Is it possible to do this? Maybe I have to pre-process the file fisrt?


      01BGMAX               0120040525173035010331P                                  
      050009912346          SEK                                                      
      20000378351165598                    000000000000010000320000000000010         
      25Här är betalning från mig till dig                                           
      26Kalles Plåt AB                                                               
      27Storgatan 2                        12345                                     
      200097012333524963                   000000000000020000320000000000020         


      Kind regards

      Erik O



        • 1. Re: Import data to modify existing posts
             It's hard to respond with specifics without knowing more about your database and tables. Most likely, you'd import the data into an interim table and run a script to process the data into updates to your other tables.
          • 2. Re: Import data to modify existing posts



            Thank you for your post.


            It appears that whenever a "20" is encountered in the first two positions, you want to take the number starting in the 13th position of that string.  It appears the invoice number is 6 characters in length, but looking at the third line that also begins with "20", the invoice number is 5 characters.  What is the maximum number of characters of an invoice?  For this example, let's assume it is 8.


            Create a table for importing.  Each line of the file will be imported into a field "Import" as separate records.  Create a calculation field, INVOICE NUMBER, with the formula:


            If ( Left ( Import ; 2 ) = "20" ; Trim ( Middle ( Import ; 13 ; 8 ) ) ; "" )


            This looks for the line beginning with "20".  If true, then we start at the 13th character, take the contents for the next 8 characters, and then remove (trim) any spaces.


            Let me know if this works correctly for you.



            FileMaker, Inc. 

            • 3. Re: Import data to modify existing posts

              Thanks very much for your answer! I will try this out as soon as possible!!


              Kind regards

              Erik O