Numbering Summaries

[KYC]

I use summaries to generate checks. I have the summaries already done, but I need to assign check numbers to each summary by entering the starting check number and have Filemaker number each summary. Does anyone have any ideas short of exporting and importing the summaries? I would really like to export summary fields to another Filemaker file, which would result in a record created for each summary. According to the books, I can't do that. Thanks for your time.

[BobWeaver]

It would likely be better to create a separate related file for checks and link it to the pay items file. The key field would be whatever field you are basing your summaries on.

If you would rather keep it in the same file, though, you can number the individual records that are part of the summaries. For example, if this were a payroll system and you were summarizing by employee number and pay period, you could have a check number field ('CheckNo'), and assign the same check number to each pay item record for the employee for that pay period. A script could be made to do this:

1. Ask the user to enter a starting check number, and put it in a global field 'CheckNoGbl'.

2. Find all records for the current pay period.

3. Sort the file by the summary criteria (employee number in this example).

4. Loop through the file, and check the summary criteria. If it has changed from the previous record, increment the global 'CheckNoGbl' field.

5. As you are looping through, set the 'CheckNo' number field equal the the current 'CheckNoGbl'.

Now, all the pay item records for a given employee will have the same check number. Then you can display the check number field in the summary part of the layout.

[BobWeaver]

The method I described should work, but I was fairly sketchy about it. I wouldn't export these records, just leave them in your original invoice file. You can create a check file that's related to the invoice file. You can assign check numbers to the invoices right in the invoice file and then print them from there using your summary layout, or from a related check file.

Following is a script that should work for putting check numbers on your invoices. I'm assuming that you have a VendorID field for identifying the vendor, and a CheckNo field for the check number. You also need to create three global fields:

CurrentVendor

FirstCheckNo

LastCheckNo

Set the FirstCheckNo field to the starting check number, and then run the following script:

Find[unpaid invoices etc.]

Sort[sort Order: VendorID]

Go to Record/Request [first]

Set Field [CurrentVendor,Calculation: VendorID]

Set Field [LastCheckNo,Calculation: FirstCheckNo]

Loop

If [CurrentVendor <> VendorID]

Set Field [LastCheckNo, Calculation: LastCheckNo + 1]

Set Field [CurrentVendor, Calculation: VendorID] <----Missed this step

End if

Set Field [CheckNo, Calculation: LastCheckNo]

Go to Record/Request [Next, exit after last]

End Loop

Assuming that you start with check number 100, this script will set the check number field to 100 in all invoice records for the first vendor, 101 for all invoice records for the second vendor, 102 for the third etc.

At this point, the LastCheckNo will be set to the last check number assigned. Since you know the first and last check, you can run a script in a related check database to create that many new check records with the same range of numbers. The files can be related by the check number field. In the check file, you can make a layout with a portal to the invoice file which will then display all the invoices that were paid by a particular check.

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

[LiveOak]

The general danger of generating checks as summaries is that the components that are summarized to create the checks are records that could be accidently changed and alter the value of a check at a later date. Checks, invoices and the like are historical documents and must be protected from change. Creating checks as individual records makes protection easier. -bd

[KYC]

Thanks for your idea. I have been trying out different scenario's to make this work. Your idea works fine if I was only generating one check at a time. I had not mentioned that I am taking about 400 invoices (records) and generating checks to about 100 vendors (sorted by vendor ID #) at one time. I had planned on importing these records into a checking file, sorting by vendor ID #, and using the summaries to generate the checks. I need to enter the starting check number, and have it generate the sequential check numbers for each vendor. Is there a way when I import, to assign different check numbers to the different vendor ID #'s?

I appreciate your help. I have been using Filemaker for many years, but have never come up against this problem. Thanks Again

[KYC]

Bob, This appears to be exactly what I am looking for. However, there is a problem with the numbering of the invoices.. It numbers the invoices (records) correctly for the first set of vendor records only. I have tried different things, but I think it is the Set Field [Current vendor <>Vendor ID]. It doesn't seem to be finding the unique vendors; it gives each vendor more than one check number.

Thanks for the thoughts about the integrity of the check numbers. I had thought about using lookups in the check file. All menu bar commands will be disabled anyway.

[BobWeaver]

Oops, I missed a step. I have edited my earlier post (see above). Note the missed Set Field step. The problem was that when it detected a new vendorID, it should have updated CurrentVendor, but it didn't. So, after the first vendor it thought every record was a new one, and kept incrementing the check number. That should fix the problem.

This is the danger of manually transcribing a script. Does anyone know of any way of copying and pasting (or the equivalent) a filemaker script?

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

[KYC]

Thanks Bob, that is just the ticket. I sure appreciate your work.

I use a very old Macintosh desk accessory called "Image Grabber". I use it all the time to extract just a certain area, or the entire screen. It saves text as a graphic, but still works wonderful. It is not the present day Neotech Image Grabber, which is a card.I don't have any address or company name, I have transferred it from one mac to another throughout the last 5 years. It won't let me paste into this area however.

Again thanks for the help. I owe you one.