2 Replies Latest reply on Jun 6, 2009 12:29 AM by TKnTexas

    Spool File as Data Import

    TKnTexas

      Title

      Spool File as Data Import

      Post

      I am trying to figure out a solution in my department.  Our AP system takes two kind of invoices for import:  those with a PO for product purchased to resale and those without for general expenses.   When invoices with POs are processed, the PO batches are closed moving the information to the general AP side. 

       

       

      The problem is that one of our acquisitions is on a different system than we ours.  They have to do their purchase orders on their system paying them through there.  They will access our main system remotely to enter general invoices.  The problem is the PO invoices entered on their system.  The system does not export.  There is an output report of the transactions.  The spoolfile layout is:

       

      VendNum  *  InvoNum  *  InvoDate  *  InvoAmt  *  GL Distr  *  Loc *  Distr Amt

                                                                                 GL Distr  *  Loc *  Distr Amt

       

      The distributions continue for however many lines there are, but generally 2 to 5.  I need to read the file in.. and fill it out complete.  The first four fields are not repeated down in the spooled file, but need to be for using it upload to our system.  I already have a tool to do that.   

       

      I have parsed data from a spool file, that is not a problem.  I just need to fill in the data for all lines.  Is this scriptable?  Is it something others have done?

        • 1. Re: Spool File as Data Import
          TSGal

          TKnTexas:

           

          Thank you for your post.

           

          In your spool file, you can write a script that grabs the the VendNum, InvoNum, InvoDate and InvoAmt information, stores them to variables.  Then, when reading the following records, if those fields are empty, you can replace the fields with the variables.  Here is a sample script:

           

          Go to Layout [ <layout where spool fields are located> ]

          Go to Record/Request/Page [ First ]

          Loop

             If [ not IsEmpty (VendNum) ]

                Set Variable [ $vend ; VendNum ]

                Set Variable [ $invo ; InvoNum ]

                Set Variable [ $date ; InvoDate ]

                Set Variable [ $amt ; InvoAmt ]

             Else

                Set Field [ VendNum ; $vend ]

                Set Field [ InvoNum ; $invo ]

                Set Field [ InvoDate ; $date ]

                Set Field [ InvoAmt ; $amt ]

             End If

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

          End Loop

           

          -------------

           

          In essence, the script first goes to the layout where the spool file information is located.  Then, we go to the first record and enter the loop.  We evaluate and check to see if the VendNum field is not empty.  If so, then we store the VendNum, InvoNum, InvoDate, and InvoAmt fields to variables.  Else (if the VendNum field is empty), then we replace the fields with the values of the variables.  Then, we go to the next record and evaluate again.  Once we reach the last record and try to go to the next record, we exit the loop.

           

          If you need clarification for any of the above steps, please let me know.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Spool File as Data Import
            TKnTexas
               Thanks.  That helps a lot.  I am so going to impress my boss :smileyhappy: