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

    Import data to modify existing posts

    ErikO

      Title

      Import data to modify existing posts

      Post

      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                                     
      28Storåker                                                                     
      29005500001234                                                                 
      200097012333524963                   000000000000020000320000000000020         

       

      Kind regards

      Erik O

       

       




        • 1. Re: Import data to modify existing posts
          philmodjunk
             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
            TSGal

            ErikO:

             

            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.

             

            TSGal

            FileMaker, Inc. 

            • 3. Re: Import data to modify existing posts
              ErikO
                

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

               

              Kind regards

              Erik O