VI import to AP Invoice Header using Expense Distribution Tables with automatic lines being created

SOLVED

We're trying to import AP invoices using Expense Distribution Tables.  When entering an AP invoice manually, the lines auto-populate, but using VI there is just an error (with the same input).

No L fields (because the goal is to let the lines be auto-populated).

The Header data is accepted, but the import fails because it's out of balance.  Is this strategy possible (i.e. are the Expense Distribution Table automatic lines programmed using UI objects)?

Parents Reply
  • 0 in reply to Steve Passmore

    When I get to the Perform Maintenance screen, I entered the following:

    I cannot enter a Table Name, and obviously not a Column Name, as I am sure that is available only after a table is selected. Is this where I would point to the Expense Distribution Table? 

    How do I get access to the Table Name selection?

    I am using Sage 100cloud Standard 2020, Version 6.20.0.1

Children
  • 0 in reply to Mike Peck

    The Table Name and Column Name should be left blank.

  • 0 in reply to Steve Passmore

    If I am creating a batch of invoices to be imported via VI, at what point do I designate the A/P Expense Distribution Table?

    Do I understand correctly that I do not need any 'L' line records? Where would I add Comments for the line distributions?

    Sorry for all the questions, but I feel I am getting close to accomplishing updating a monthly batch with invoices from several Vendors, each of which is distributed to multiple Accounts.

  • 0 in reply to Mike Peck

    The TableCode for the A/P Expense Distribution Table can be assigned to each Vendor and then will automatically be set in the Invoice entry being imported or it can be imported as part of the V/I job.

    To answer the question about the 'L' records, if you are using the perform logic to force the expense distribution to create the detail lines then no, you do not need them.

    Unfortunately, if you want to import comments for each line then you would need the 'L' records.  

    Someone else may have a workaround for you to get both the expense distribution and then import the comments into that detail.  Is the comment different for each detail line?

  • 0 in reply to Steve Passmore
    SUGGESTED

    No, I am sure the AP folks will be willing to give up the line comments to get the batch imported each month with the Distribution Accounts and Amounts included.

    In the Import Job Maintenance Screen, 2.Data tab, Do I need to enter anything in the H.TableCode field?

    Thank you for your assistance. I will mark your response as the Suggested Answer. Is there anything else I need to do so you get credit?

  • 0 in reply to Mike Peck

    If each Vendor is already assigned an Expense Distribution Table Code then no it does not have to be in the import file.  

    Assigning a default Expense Distribution Table Code to a Vendor was just added as a feature a couple of years ago.  If your current version of Sage 100 does not have that feature the table code will have to be imported.

  • 0 in reply to Steve Passmore

    I am using Sage 100cloud Standard 2020, Version 6.20.0.1

    I didn't see it in the sample, but the invoice total amount should be in H.InvoiceAmt, I would assume. Is there a recommendation for where that should be inserted? Before or after Table Code, for example? 

  • 0 in reply to Mike Peck

    It shouldn't make a difference before or after. 

  • 0 in reply to Steve Passmore

    The integration failed due to the Distribution Table not exploding into the constituent account numbers, causing the record to be evaluated as out of balance.

    Is a path, or some other additional context, needed before the string ‘coBusiness’Lines’DistributeBalance()’ ?

  • 0 in reply to Mike Peck

    I got past the records failing because the Perform Command Line did not did not explode into all the Expense Distribution Table Account Distribution Lines.

    When I execute the integration every line passes validation. An AP batch is created but there are still no distributions accounts. The Control Totals are all zeroes.

    Any suggestions would be appreciated.