calculation for 6-up name badges

[iMarcW]

I wonder if anyone else has had to resort to this...

I wrote before about the easiest way to print thousands of name badges through either repeating or related fields. This is the second part of the problem. The badges come out on 8.5 x 11 sheets with six badges per sheet. If you print out, say, 300 badges, you get 50 sheets with 1-6 on the first sheet, 7-12 on the second, etc., so when you get the pages out of the printer, you separate the badges into six piles and then spend a lot of time playing what I call "solitaire in reverse": picking up one badge from each pile until you have one big pile in order.

So I set about doing a calculation that would make the badges print with 1, 51, 101, 151, 201, 251 on the first sheet, and so on, so that all of the first badges on each sheet would be 1-50, the second would be 51-100, etc. You could separate the badges and put the piles together, and just like that, they'd be in order.

After studying the numbers for about a half hour, I came up with this: find your badges, sort them the way you want them to be ordered when it's all done, and replace a field "badgesort1" with an incremental serial number, in this example 1 to 300. Then I made a badgesort2 field with the following calculation:

badgesort2=(6 * ((Mod(badgesort1, (Status(CurrentFoundCount)/6)) - 1)) + Int(badgesort1/(Status(CurrentFoundCount)/6)) + 1

If I sort by this field, then print, I should have gotten the results I wanted. There's just one glitch. Anytime the badgesort1 number is a multiple of the found set/6 (meaning the mod or remainder between those two numbers is zero), in this case 50, 100, 150, 200, 250, 300, I wind up with a negative number or zero in the badgesort2 field, when they should be on the last sheet of badges (295-300). I realized that since I'm using -1's and +1's in the calculation, what I really want is not zero but to bring it back to the top of the number set, so the pages come out 1-50 instead of 0-49. So that resulted in this change (to make the calculation less complex, I created an interim badgesort2 field equalling the current found count/6, basically the number of pages, and changed this calculation's name to badgesort3):

badgesort3 = (6 * (If(Mod(badgesort1, badgesort2) = 0, badgesort2, Mod(badgesort1, badgesort2)) - 1)) + Int(badgesort1/badgesort2) + If(Mod(badgesort1, badgesort2) = 0, 0, 1)

That actually works. The catch is that the found set has to be a multiple of 6, so you might have to add 1-5 blank records to the end of your set before adding the badgesort1 serial number. Eventually I should be able to script the whole darn thing.

And one little problem I'm having is that badgesort2 calculation - Status(currentfoundcount)/6 - isn't updating when the found count changes like it should. It just stays whatever it was when the field was defined. Any ideas there? Maybe make it a global field and do the calculation during the script?

I hope someone else can make use of this, since I know it will save us countless hours. Any simplifications/improvements are welcome as well.

[danjacoby]

You could start by creating a script step that checks the Mod and adds records as necessary, sorting and naming the correct fields, etc.

Also, check your calc fields to make sure they're unstored; that should solve the updating problem.

[iMarcW]

I just did it in the script. It creates new records in a loop equal to the mod (actually 6-mod because if the mod is four, you need two more to make it an even multiple of 6) and flags those blank records so you can delete them with a separate script after you print everything. It works so transparently I can have anyone in the office do it: find, sort the way you want them to come out, run the script, print and run the blank-field delete script.

[danjacoby]

I just love creating blank records behind the scenes, having done it several times so that you can print labels from a partially used sheet.

[iMarcW]

Here's the whole kit and kaboodle, which can be added to any database as-is to provide 6-up badge sorting. I hope someone finds it useful:

Field Definitions

--------------

badgesortDeleteflag - Number

badgesortModloop - Global (Number)

badgesortOrig - Number

badgesortPages - Global (Number)

badgesort6up - Calculation (Number)

= (6 * (If(Mod(badgesortOrig, badgesortPages) = 0, badgesortPages,

Mod(badgesortOrig, badgesortPages)) - 1))

+ Int(badgesortOrig/badgesortPages)

+ If(Mod(badgesortOrig,badgesortPages) = 0, 0, 1)

Sort Script

----------

Set Field [ badgesortModloop, If(Mod(Status(CurrentFoundCount), 6) = 0, 0, 6 - Mod(Status(CurrentFoundCount), 6)) ]

Loop

Exit Loop If [ badgesortModloop = 0 ]

New Record/Request

Set Field [ badgesortModloop, badgesortModloop - 1 ]

Set Field [ badgesortDeleteflag, 1 ]

End Loop

Replace [ badgesortOrig, Replace data: Serial numbers, Custom values, Initial serial number value: 1, Increment value: 1 ]

[ No dialog ]

Set Field [ badgesortPages, Status(CurrentFoundCount) / 6 ]

Sort [ Sort Order: badgesort6up (Ascending) ]

[ Restore sort order, No dialog ]

Go to Layout [ Name Badges ]

Delete script

------------

Perform Find [ Request 1: badgesortDeleteflag 1 ]

[ Restore find requests ]

Delete All Records