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).

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).

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 ?

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. 

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.

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 :)

Thanks JJD, I appreciate it. And thanks for searching before asking. Sometimes the same question is asked multiple times in one week.