VI General Journal total debits and assign credit account number

SUGGESTED

I'm trying to import a journal entry. I've calculated the Debit Account number by combining other fields to make the complete account and that works.

Now I need to total all the debits to get the credit amount and then assign the credit account number.

I tried in fields 16 and 17 to "Sum(debits)" and assign the account number if the credit amount <>0.

that does not work.

  • 0
    SUGGESTED

    You might want to change tactics a bit.  Add L.Temp006 right after L.Temp003 and make it a calculated field pointing to your amount column (23?), Assign on Line Records.  Then add L.Temp007 numeric calc. field and calculate as Temp007+Temp006. This will add the amounts of each line to Temp007 until completed.  

    I also noted you have L.AccountKey twice, it looks like you want to assign account 220 at the end.  That probably won't work the way you have it.  If you could identify the last record in the source file, you could use that value to put two conditionals in, one for (if the last record value is not there, assign debit L.AccountKey line)  and if the value is there, assign credit L.AccountKey, using the built up Temp007 amount., also with the same conditional.  They both can't be assigned on the same pass.  

    These things tend to lead to more questions, but wanted to get you thinking in a different direction.  Good Luck!

  • 0 in reply to VanMan

    thanks, it makes sense.  I'll work it though.

  • 0

    A VI import job, by default, cannot create two line records off of the same record in the source file.  So although you can use temp fields to create a running total that is reset when the header changes and accumulates totals off the lines, you are still left with only be able to set the line records for each record in the source file.  So if your source file does not contain a line for the credit, the best way to handle this would be with perform logic on the Before Write of the Header.  If you source file does contain a line for all of the debits and a line for the credit but without the amount, then you would just use the temp field to set the credit amount when you get to the line for the credit.

    A very simple form of this perform logic would look like this.  You would need to change "CreditAccountNumber" to equal whatever account you would be using and you would need to change Temp001 to whatever temp field you are using to hold the accumulated credit amount.

    IF Temp001<>0 THEN {coBusiness'Lines'AddLine(); coBusiness'Lines'SetValue("AccountKey$", "CreditAccountNumber", "kAccount"); coBusiness'Lines'SetValue("CreditAmount", Temp001); coBusiness'Lines'Write()} ! '

  • 0 in reply to David Speck

    Also, you could use this perform logic on the same event but a earlier sequence so you don't have to accumulate the debit amount using temp fields.  If you do use this, it would need to use sequence 001 and the perform logic to add the credit account line would need to be sequence 002.  The alternative would be to place all of this perform logic in a text file under MAS90\CM\Script or MAS90\VI and then specify the file in the command of the perform logic.  If possible, I try to use compound statements to avoid having to having a file dependency but the total length of both compound commands exceeded the length allowed for the command so it had to be split.

    Sequence 001.

    TotalDebitAmount=0; IF coBusiness'Lines'GetResultSets("STR(DebitAmount)","$$",TotalDebitAmount$,Null$,"","","")=1 THEN {TotalDebitAmount=EVN("0"+SUB(TotalDebitAmount$,SEP,"+")+"0")} ! '

    Sequence 002.

    IF TotalDebitAmount<>0 THEN {coBusiness'Lines'AddLine(); coBusiness'Lines'SetValue("AccountKey$", "CreditAccountNumber", "kAccount"); coBusiness'Lines'SetValue("CreditAmount", TotalDebitAmount); coBusiness'Lines'Write()} ! '

  • 0 in reply to David Speck

    This issue is for an import from Concur. Concur is going to see if they can sent the total debits and assign the credit account within the import file. For future reference I will attempt the above.