Alter receipt to show outstanding -DB query help ?

After all these years the one thing still missing is being able to show customer/remind that there are OTHER invoices still outstanding WHILE entering the receipt which you have to do anyway when funds are received - with NO EXTRA STEP to produce and also send a statement.

Our receipt looks like this (customized in Crystal reports) - [see pic]

If more than one bill is being paid, they are all listed with the payment amount for each. If a bill is not being paid, it does not appear on the receipt.

It seems to me there must be some logic at play that scrutinizes the amount paid field and includes the line for that bill if it's above 0 - must be at least one cent paid for it to appear. Is this in the report generation (And so can be changed in Crystal Rep. ???) - or are the bills weeded out first by Sage and then only those 'paid/part paid' bills get sent to the report ? If this is easily alterable in Crystal Reports - how do I get at that specific query to alter ?

  • The relevant portion of our 'receipt' - note how currently I manually add a note about any outstanding bill in the comments :

  • When you review the contents of the csv files that are created when you print to a Crystal receipt, you will see the rows show only that which is paid or partially paid. In order to get the full statement information on the receipt, you would have to modify the report to go back to the original *.sai file and pull the proper data from the customer transaction history tables (see the datadict.pdf file for the full set of tables needed).
  • Hi,

    JJD said:
    show customer/remind that there are OTHER invoices still outstanding

    If you are using the former 'Simply Accounting' Sage 50 Canadian, you can change the setting in Setup | Settings | Receivables | Options, under Customer Statements, to 'Include invoices paid in the last [_] days on statements'.  All unpaid invoices and balances owing can be printed together with recent payments.

    JJD said:
    WHILE entering the receipt which you have to do anyway when funds are received - with NO EXTRA STEP to produce and also send a statement.

    I can't see a way to automatically configure printing a statement every time a payment is received.  I've seen some fairly amazing things done with AutoHotKey macros, but in Sage 50 Canadian the payment APIs are not part of the SDK.

    JJD said:
    If this is easily alterable in Crystal Reports - how do I get at that specific query to alter ?

    The data isn't part of the Crystal Reports CSV in Sage 50 Canadian.  The raw data is in the two tables tCusTr and tCusTrDT, which share a key, and there is a customer ID that is the primary Key for tCustomr. 

    tCustr has a dPreTaxAmt field that looks like it would be for reports (but doesn't seem to be used).  tCusTr has a 'dAmount' and a 'dAmtOwg' balance field, but it is not the balance owing on the invoice, and is not updated when the invoice is paid.

    tCustomr has statistical amounts, but not a balance owing

    So you have to do a summary query on each active record in tCustomr, joined to the related records in tCusTr, each joined to its related records in tCusTrDT, in order to get the balance of each invoice, if any.

    If you would like to suggest to Sage that they add the capability to automatically generate a statement after posting each receipt, select help | Contact Sage | Give us your Feedback, and put a suggestion in the box.

    If you are using the former 'Peachtree' Sage 50 US software, I'm not familiar enough with it to make suggestions.

    I hope that helps, please post back.

  • I guess I need an understanding of how the report is affiliated with what I see in the journal when I enter a payment received. When entering in the journal, all of the information is there - i.e. all outstanding invoices appear onscreen, along with any payments I enter, so what I see at that point is what I want the client to see on the generated receipt - both what he's paid/partially paid at this time and and outstandings. I gather this ENTIRE set of data *I* see is not actually sent to CR when I hit the email button. It's more like CR only gets what's in the final POSTed transaction; except it's not been posted yet, so this confuses me. Is SA sending a simple command to CR to generate its report then CR is querying back to some kind of temporary table in the DB? Or is SA sending the subset of data when it triggers CR to generate a report ?
  • "The data isn't part of the Crystal Reports CSV in Sage 50 Canadian. "
    Oh, is it that a .csv is generated on the fly by SA for whatever report engine as part of the command to email or print ?

    We are using Sage 50 Pro Cdn; I happen to like Crystal Reports and so am still using it from when it was included in a prior version (when it was called Simply Accounting).

    Will using the Sage 50 Form engine instead (or for this form only) do what I want or be easier to modify for this ?