Making a check register from summaries

[KYC]

I use summaries to print checks. Thanks to Bob Weaver, I can now number the checks sequentially. My problem is I need to put a checkmark beside each check number when I reconcile, and of course you can't enter a checkmark when in Preview. I don't want to export to Excel. Anyone have any ideas?

[LiveOak]

I guess I fundamentally disagree with the approach of using summaries to print checks. You have just found one of the problems with this approach. If checks amounts are comprised of many records in a transactions file, I'd use a check file with one record per check with a one to many relationship to the transaction file.

As you prior posts have indicated, you've already had to do a lot of work-arounds to maintain this approach. I'd cut my losses and use the more conventional solution. -bd

[This message has been edited by LiveOak (edited December 27, 2000).]

[PinnWal]

Why do you need to be in Preview Mode?

[LiveOak]

The need to be in Preview mode is a result of a check being comprised of multiple records in the file. The check value is a total of these records displayed in a summary part of a layout. As such it is only visible in Preview mode (subsummarized by whatever divides one check from another). In browse mode the summary field would show the total of all records. -bd

[PinnWal]

Thanx. I wasn't aware that this was how the checks were being printed. I can see why you suggested printing checks in a related file of 1 check per record. Much cleaner & easier to manage!

Later :-)

[KYC]

I guess I can't see the forest cause of the trees. Lets review: This is for a non-profit shop where they pay the craftsman each month for what is sold. I have a sales file that includes the craftsman's number and item number. There can be up to 1500 (records) or sales in a months time. There are about 100 craftsman, who can have multiple sales each month. The summary worked because I need to summarize the sales by Craftsman's number, and assign a check number for each summary, and number the checks with a starting number that changes each month, and have it automatically number each check sequentially. I don't want them to have to number individually, and since they can sometimes handwrite a check or mess up a check, each time they run checks, it will be a different

starting number. Now I am at the check register problem. I guess I can't comprehend the conventional solution with the above problems. I'd appreciate any suggestions.

[BobWeaver]

In my original post, I mentioned that you should have a checks file that is related to your sales file by the check number. The script that puts check numbers on the items sold should also create a check record in the check file. Then with a portal from the check file to the sales file you can see all the items being paid by a particular check. The check file can have a field to indicate that the check is reconciled. But, more importantly, you can use the sum() function to total the items being paid and print the checks from the checks file. I guess I wasn't clear earlier on doing the total amount calculation through the portal.

The work you have done is not wasted; you have to apply check numbers to the sales records in any event. You just need another file to handle the actual checks.

There are a couple of ways to create the check records. The simplest (but not necessarily the most efficient) is to add a 'perform script' step in the check numbering script that executes a subscript in the check file which creates a new check record and sets the check number. To do this, make a new check file that has a CheckNumber field in it. Make a script (call it NewCheck) that creates a new record and pastes the clipboard contents into that field. Then in your main check numbering script in the sales file, add these two steps immediately after the step that increments the LastCheck number.

Copy [ select all, LastCheckNo]

Perform script [external: NewCheck]

You need to set up a relationship between the check and sales files using the checkNumber field in each file as the link. Then you can get the total amount per check with the following calculation:

ChkTotal=Sum(Sales::SaleAmount)

where SaleAmount is the field in the Sales file that you are totalling.

This is very brief. I hope it's not too confusing.

[This message has been edited by BobWeaver (edited December 29, 2000).]

[LiveOak]

I would make one slight change to what Bob has said. I'd make the check total a number field in the check file and use a Set field to set it to the Sum() of the records in the sales file at the time the check is created. This way, if items are altered in the sales file, the value of the check in the check file is not altered. It's important to protect the value of the check from corruption (yes, it will happen!). -bd