Author Topic: Multiple Journal Entries  (Read 3439 times)

Daniel

  • Member
  • *
  • Posts: 11
Multiple Journal Entries
« on: June 28, 2013, 05:45:24 AM »
This is my second post on the same topic - apologies if I've double posted but my first one did not seem to go through.

I'm having trouble scripting multiple journal entries.

Here is the data I'm pushing:

|DEBIT|
100063360
10002248
|CREDIT|
40100:40101992
40100:40102496
40100:401032120
2320065.72


The script is as follows:
Set Variable [$r; Value:PCQB_RqNew("JournalEntryAdd")]
Set Variable [$r; Value:PCQB_RqAddFieldWithValue("TxnDate" ; $date )]
#
Set Variable [$cVtp; Value: ValueCount ( $valuesToPush)]
#
Set Variable [$i; Value;1]
Loop
  Exit Loop If [$i > $cVtp]
  SetVariable[$v; Value:GetValue ( $valuesToPush ; $i )]
#
  If [PatternCount ( $v ; "|" ) > 0]
    Set Variable [$lineType; Value: Substitute ( $v ; "|" ; "" )]
#
  Else
#
# create journal debit line
    If[$lineType="Debit"]
   Set Variable [$r; Value:PCQB_RqAddFieldWithValue("JournalDebitLine::AccountRef::FullName" ; GetValue ( Substitute ($v ; "" ; ) ; 1 ) ) // account number, e.g. 10006]
   Set Variable [$r; Value:PCQB_RqAddFieldWithValue("JournalDebitLine::Amount" ; GetValue ( Substitute ($v ; "" ; ) ; 2 ) )]
#
# create journal credit line
    Else
   Set Variable [$r; Value:PCQB_RqAddFieldWithValue("JournalCreditLine::AccountRef::FullName" ; GetValue ( Substitute ($v ; "" ; ) ; 1 ) ) // account number, e.g. 40100:40101]
   Set Variable [$r; Value:PCQB_RqAddFieldWithValue("JournalCreditLine::Amount" ; GetValue ( Substitute ($v ; "" ; ) ; 2 ) )]
    End If
#
  End If
#
  Set Variable [$i; Value: $i + 1]
End Loop


I then push it to QB, closing and opening a session, which seems to work fine.  However I receive the following error:

The following error occurred: CODE: -1000
SEVERITY: failed validation
MESSAGE: Error at FullName
Reason: Element 'FullName' is unexpected according to content model of parent element 'AcountRef'.
Field value: 10002


Since the field value in the error message is the second debit line, can I presume that the first one was structured properly?

In the Data Viewer I called the PCQB_SGetXML( "Request" ) function which returned the following:


<?xml version="1.0" encoding="ISO-8859-1" ?>
<?qbxml version="12.0" ?>
<QBXML>
   <QBXMLMsgsRq onError="stopOnError">
      <JournalEntryAddRq>
         <JournalEntryAdd>
            <TxnDate>2013-06-03</TxnDate>
            <JournalDebitLine>
               <AccountRef><FullName>10006</FullName><FullName>10002</FullName>
               </AccountRef><Amount>3360.00</Amount><Amount>248.00</Amount>
            </JournalDebitLine>
            <JournalCreditLine>
               <AccountRef><FullName>40100:40101</FullName><FullName>40100:40102</FullName><FullName>40100:40103</FullName><FullName>23200</FullName>
               </AccountRef><Amount>992.00</Amount><Amount>496.00</Amount><Amount>2120.00</Amount><Amount>65.72</Amount>
            </JournalCreditLine>
         </JournalEntryAdd>
      </JournalEntryAddRq>
   </QBXMLMsgsRq>
</QBXML>



My questions:
Why am I getting this error message?
How do I push multiple debit and credit records?
Is it a problem that the number of debit records may not equal the number of credit records even though their sums will always balance?

FWIW, in my original post I attached several screenshots but they didn't go through, so I've written everything out here.

Thanks.



Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Multiple Journal Entries
« Reply #1 on: June 28, 2013, 06:52:42 AM »
I don't use the native functions, preferring to create the qbXML myself and then use PCQB_ZExecute to push it to QB. That said, I'm fairly sure that the problem stems from failing to use PCQB_RqAddRelatedRecord ( "JournalDebitLine" ; "" ; "" ) and/or PCQB_RqAddRelatedRecord ( "JournalCreditLine" ; "" ; "" ) when you have more than one either type.

I believe you can preface each JournalDebitLine or JournalCreditLine with that function even if there's only one of each, and in doing so you won't need to include that element in the QBFieldName parameter of your RqAddFieldWithValue arguments. Like this...

   Set Variable [$r; Value:PCQB_RqAddFieldWithValue("AccountRef::FullName" ; GetValue ( Substitute ($v ; "" ; ) ; 1 ) ) // account number, e.g. 10006]
   Set Variable [$r; Value:PCQB_RqAddFieldWithValue("Amount" ; GetValue ( Substitute ($v ; "" ; ) ; 2 ) )]

   Set Variable [$r; Value:PCQB_RqAddFieldWithValue("AccountRef::FullName" ; GetValue ( Substitute ($v ; "" ; ) ; 1 ) ) // account number, e.g. 40100:40101]
   Set Variable [$r; Value:PCQB_RqAddFieldWithValue("Amount" ; GetValue ( Substitute ($v ; "" ; ) ; 2 ) )]
    End If

HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852

Daniel

  • Member
  • *
  • Posts: 11
Re: Multiple Journal Entries
« Reply #2 on: June 28, 2013, 07:44:07 AM »
Geoffrey,

Thank you for your prompt reply.

Quote
I believe you can preface each JournalDebitLine or JournalCreditLine with that function even if there's only one of each, and in doing so you won't need to include that element in the QBFieldName parameter of your RqAddFieldWithValue arguments. Like this...

I don't quite understand your example as it looks the same as what I'm currently doing (see attachment "ScriptOrig").  Based on what you wrote and the FMBooks Connector document, I added PCQB_RqAddRelatedRecord ( "JournalDebitLine" ) and PCQB_RqCloseRelatedRecord to the debit push and the same (JournalCreditLine) to the credit push (see attachment "ScriptNew").  That returned a new error (see attachement "ResultError").

Quote
I don't use the native functions, preferring to create the qbXML myself and then use PCQB_ZExecute to push it to QB.
I'm intrigued by this.  What should the qbXML look like when pushing multiple records?  I wonder if it would be easier for me to build it myself in a variable and push it with PCQB_ZExecute as you do.


P.S. - Does Simple Machine Forums not accept .png files?  I attached them, then get an error message, then try to submit my post/replay without the attachments and another error message tells me I've already posted.  But it is not posted and doesn't show in the thread.  Pushing to QB is maddening enough for me; inability to post w/attachments is beyond frustrating.

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Multiple Journal Entries
« Reply #3 on: June 28, 2013, 10:23:29 AM »
Was the new Error due to Debits <> Credits, or something else? I can't speculate on the problem without the error and the attachments or a text representation of the revised script. Can you post the result of the PCQB_SGetXML( "Request" ) function?

As to what it would look like using the ZExecute, I've corrected the problematic request you posted earlier as follows...

PCQB_ZExecute(
"" ;
"      <JournalEntryAddRq>
         <JournalEntryAdd>
            <TxnDate>2013-06-03</TxnDate>
            <JournalDebitLine>
               <AccountRef><FullName>10006</FullName>
               </AccountRef><Amount>3360.00</Amount>
            </JournalDebitLine>
            <JournalDebitLine>
               <FullName>10002</FullName>
               <Amount>248.00</Amount>
            </JournalDebitLine>
            <JournalCreditLine>
               <AccountRef><FullName>40100:40101</FullName></AccountRef>
               <Amount>992.00</Amount>
            </JournalCreditLine>
            <JournalCreditLine>
               <AccountRef><FullName>40100:40102</FullName></AccountRef>
               <Amount>496.00</Amount>
            </JournalCreditLine>
            <JournalCreditLine>
               <FullName>40100:40103</FullName></AccountRef>
               <Amount>2120.00</Amount>
            </JournalCreditLine>
            <JournalCreditLine>
               <AccountRef><FullName>23200</FullName></AccountRef>
              <Amount>65.72</Amount>
            </JournalCreditLine>
         </JournalEntryAdd>
      </JournalEntryAddRq>" ;
""
)

If you're using FileMaker Advanced, you could paste this in your Data Viewer and hit the "Evaluate Now" button. If the result is !!ERROR!! (from something other than Debits <> Credits) you can append " & PCQB_SGetStatus " after the closing ")" and hit Evaluate Now again to see where the Request is wrong.

HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852

johnlewisdesign

  • Member
  • *
  • Posts: 81
Re: Multiple Journal Entries
« Reply #4 on: June 29, 2013, 10:20:55 AM »
Wow nice. I think I will look into this function myself.

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Multiple Journal Entries
« Reply #5 on: June 30, 2013, 10:24:28 AM »
There's not really any magic happening with PCQB_ZExecute--it does the same thing as PCQB_RqExecute but does it with a qbXML parameter that you supply instead of the qbXML that was built in memory space by the various PCQB_RqXxx functions. You can always see the current value of your Request's qbXML by using the PCQB_SGetXML( "Request" ; "" ) function.

I build my qbXML Request string in a script variable ($xmlBuilder) that shows in my Data Viewer as the script executes. It's really handy when waking through related records or converting FMP values into their QB counterparts.


Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852