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 ?

  • 0

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

  • 0 in reply to JJD
    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).
  • 0

    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.

  • 0 in reply to Richard S. Ridings
    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 ?
  • 0 in reply to RandyW
    "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 ?
  • 0 in reply to JJD

    JJD said:
    is it that a .csv is generated on the fly by SA

    Yes, and as far as I can see, the content can't be modified except by Sage, in development.   You can view it in the schema.ini file, but the exported content can't be changed from our end.

    JJD said:
    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

    We held off on installing all updates after 2014.2 in that year as we had made a sales order into a stock pick sheet that printed a field from Additional Info we use for warehouse bin location.  Fortunately the 2015.1 release could print the 'Additional' field so we were able to stay with Sage 50.

    The current invoice balance may be added to the CSV export as well.

    JJD said:
    Will using the Sage 50 Form engine instead (or for this form only) do what I want

    Up to the latest version 2016.3, no. 

    Printing the unpaid balance on an invoice can't be done with the current Sage forms, we have to print a statement to get that information out.  

    Sage has indicated that they will provide this as part of their forms in a future release. 

  • 0 in reply to JJD

    JJD said:
    I need an understanding of how the report is affiliated with what I see in the journal when I enter a payment received

    I don't know how Richard learned about the inner workings of Simply Accounting / Sage 50, most of what I know came from poking around looking for data.

    For a number of reasons (control, the ability to purge, security, separation of modules, etc), the Sage 50 data is stored in a number of places, mainly:

    tJourent / tJentAct - current year Journal table - Contains all financial transaction detail.  There's a new set of tables for each year

    tRcptHdr - Customer receipt header information.

    tBrTr - Bank Reconciliation table.

    tCusTr - All sales invoice transactions are stored here.  Reversed items are not kept and invoice + customer is a key.  Invoice xxxxx for customer ID xxx is the only record there is, period.  Customer IDs are in tCustomr.  

    tCusTrDt - All sales and payment detail is stored there.   If there is one invoice with two payments, there are three records, all keyed to the tCusTr table.

    tItRec / tItRLine - all invoices (sales and purchase) inventory details.  Contains invoice number

    titLU / titLuLi - all invoice screen text and some Order Entry info.  Contains order number

    tSOLine - Sales Order and prepayments received for orders

    For working out how much is the original invoice was, and how much has been paid (if any) the tCusTr and tCusTrDT tables should have all the detail you need.   You should be able to work back from the customer name in tCustomr to get the ID, and with that and the invoice number to tCusTr.lCusID to get the A/R module invoice ID, then to the tCusTrDT table with that ID to get the sale and all related payments. 

    I would have expected that there would be some place where the invoice balance due was stored.  There is no such place, that number is calculated each time you open the invoice screen. 

  • 0 in reply to RandyW

    RandyW said:
    I don't know how Richard learned about the inner workings of Simply Accounting / Sage 50, most of what I know came from poking around looking for data.

    Twenty years of looking around to solve reporting problems or creating other utilities for end-users and consultants, discussions with other consultants who are digging around inside (doing data repair or Excel programming) and as you did, a lot of trial and error.

    RandyW said:
    I would have expected that there would be some place where the invoice balance due was stored.  There is no such place, that number is calculated each time you open the invoice screen.

    Technically I think storing calculated values violates one of the rules of database normalisation and in this case is easier and faster to calculate on-the-fly because it is a simple SQL statement.

    JJD,

    As I mentioned in an earlier post and Randy has mentioned, the csv files are used to print the information by Crystal, and that's because you can print before posting.  The method of programming by ACCPAC was to use data stored on the hard drive.  So 20 years ago, the programmers decided to export the data required, to csv files, point the applicable Crystal template to them and letting the programming of the report display the data in a design needed by the user.  They came up with the canned reports (eg. receipt.rpt) that are available to modify.

    The modifications that can be made are extensive, even with the 15 year old version of Crystal we must use if you are using the embedded version of the Crystal viewer in Simply/Sage 50.  Just because they are built on the csv files doesn't mean you can't pull the data from the database as needed.  Crystal is used by many millions of people all over the world to report on databases like the one used by Sage 50.  It looks like Randy has given you all the table information needed to modify your stub to get what you need.

  • 0 in reply to Richard S. Ridings
    Fantastic Richard,
    I have truly appreciated your input in SA forums over the years. Many times I have searched my question and your reply to someone else with same issue has been spot on.

    And thank you also Randy, very helpful :)
  • 0 in reply to JJD
    Thanks JJD, I appreciate it. And thanks for searching before asking. Sometimes the same question is asked multiple times in one week.