Carrier Top Sheets calculation

[shorty]

I am writing a database to handle the circulation department of our company. We are needing to figure the calculation for printing carrier top sheets.

Our fields are:

Standard Size (Global)

Maximum Size (Global)

Minimum Size (Global)

Draw (Number)

Standard Bundles (Calc)

Loose Papers(Calc)

Need to get the calculations for Standard Bundles and Loose Papers.

Example: Standard Size=100; Maximum Size=125; Minimum Size=20; Draw=116

Answer should come up:

Standard Bundles= 0

Loose Ppaers=116

2nd Example: Standard Size=100; Maximum Size=125; Minimum Size=20; Draw=156

Answer should come up:

Standard Bundles=1

Loose Papers=56

I tried making the a calculation field for Standard Bundles of Draw/Standard Size that gives me on the first example: 1.16

On the second example: 1.56

I know that we have make a calculation that will examine the three global fields and choose which one is appropriate first then give answer.

I wouldn't even mind if we had to remove the maximum size, I just really need to get the whole number in the standard and the remaining number in the loose.

[ August 24, 2001: Message edited by: shorty ]

[ August 24, 2001: Message edited by: shorty ]

[tomlepk]

I am not sure what you are asking.

Example: Standard Size=100; Maximum Size=125; Minimum Size=20; Draw=116

Answer should come up:

Standard Bundles= 0

Loose Ppaers=116

Shouldn't that be Standard Bundles = 1, Loose Papers = 16?

like this example??

2nd Example: Standard Size=100; Maximum Size=125; Minimum Size=20; Draw=156

Answer should come up:

Standard Bundles=1

Loose Papers=56

If you can proffer a little more about how you calculate the bundle I may be able to help.

Thomas Lepkowski

[Vaughan]

Let me have a crack at it...

Loose papers = draw - (standard bundles * standard size)

You'll have to put checks into the system to handle erroneous values, like if (standard bundles * standard size) > Draw you'll have a negative number of loose papers.

I have no idea where the minimum or maximum size values fit into the scheme of things, or how the standard bundles value is calculated.

What you've got to do is determine how you'd work it out yourself. Then program FMP to do it the same way.

[shorty]

Vaughn,

Thank you for the help with the loose papers calculation.

The standard bundle =Int(Draw-Standard Size)

Loose Paper= draw-(standard bundle* standard size)

The only problem I still have is that the if the Draw=116; that results in 16 loose papers. Which that number is lower than the minimum size. Our electronic bundling machine can't handle that. Right now we would just put the whole 116 to loose papers and then the machine will wrap a plastic tie around it.

Not sure how to fine tune the loose paper calculation so that it looks at that outcome and just puts the total draw to loose paper.

Any help is appreciated. The learning is wonderful.

[Rigsby]

Hey! I just read this through like 20 times and finally figured out what he wants……. I think! -)

He bundles paper (I think) into packages. These packages have a STANDARD size (100) (number of sheets), a MAXIMUM size (125), and a MINIMUM size (20). Then he prints a top sheet (lets say with address and or name where the stuff should be sent for each package.

So, for example, company X orders 225 sheets, this would be done with:

1 bundle (Standard) = 100

1 bundle (Maximum) = 125

i.e. He needs to print 2 top sheets (delivery notes)

Or if they order an odd amount (i.e. 128) then he needs to calculate the packages and lose sheets. E.g.

1 bundle (Standard) = 100

1 bundle (Maximum) = 125

lose sheets = 2

So, 2 top sheets to print plus (I think) 2 for the lose sheets so 4 in total.

If this is his problem, it would take about 10 mins to solve, but hey! I’m going on holiday right now…. So maybe someone else can help him here? :-)

Rigsby

[Vaughan]

Shorty wrote: "Right now we would just put the whole 116 to loose papers and then the machine will wrap a plastic tie around it."

Well that's part of the solution then.

What you are doing is building an algorithm, a set of rules that determine the process. You've got to work the rules out because you know them -- we don't. Once the rules are determined they can be programmed into whatever language needed.

Part of the algorithm so far is:

IF "Loose papers" < "minimum size" THEN *bundle the whole lot*

Keep going, you'll eventually get the whole lot if you think it through. But you've gotta think it through, like you are explaining the process to a newbie (you are in fact).

[shorty]

I had help getting my calculation, but It works wonderfully. It is this:

Draw (Number)

Standard Size(Calc,number)=100

MinSize(Calc,number)=20

bundlecount(calc,number)=

Case(

Mod(Draw,Standard Size)=0,draw/Standard Size,

Mod(Draw,Standard Size)<MinSize,Int(Draw/Standard Size)-1,

Int(Draw/Standard Size)

)

Loose Papers(calc,number)=

If(bundlecount=0,Draw,Draw-(bundlecount*Standard Size))

Works great.

Now I am trying to print for each route(record) the correct number of sheets:

Example=

If route #1003 has a draw of 250

First sheet to read

Top Right Hand Corner= Route #1003

Top Left Hand Corner would be 100

Center Bottom would be 1 of 3

2nd top sheet would be:

Top Right Hand Corner=Route #1003

Top Left Hand Corner would be 100

Center Bottom would be 2 of 3

3rd Top sheet would be:

Top Right Hand Corner= Route #1003

Top Left Hand Corner would be 50

Center Bottom would be 3 of 3

Then it move on to the next route number and print the correct number of top sheets for that route.

Is this possible?

[Charlie]

Shorty -

If I'm following this correctly, you've solved how to break up the draw into the correct standard bundles/loose sheet bundles. You could now spend some considerable time trying to do some very bizarre summary fields (which might even be dummies in a way) to get it to print out your order on three sheets (or more) with the appropriate information.

My suggestion, for what it's worth, is to write a script that creates new records in a separate file with the appropriate (static) information. This would be a bit time-consuming to set up but not really a difficult task.

If you code your records properly, you can relate your original record, with the calculations, to the slave records in the second file. When you're ready to print, you can just have it go to related records in the second file and print them out. Task is done. If you change the values in the first file, you can always write a script that goes to the related records, deletes them, then returns to the original file and then creates a new set of slave records. I think this is probably a simple solution for your problem.

Hope this helps.

Charlie

[shorty]

Charlie,

I guess I am little confused. Could you give me an example of the second solution?

I had already started going through and making three separate files: 50 Print Top Sheets, 75 Print Top Sheets, 100 Print Top Sheets.

Then I had thought of each week when we needed to print the appropriate standard bundle we would just pick the correct file to print. I have found that they now want to be able to run payroll from this program. Project is growing by the days. Our are paid once a month. We look at each top sheet print file and then multiply their draw x per piece rate x # of times during the month they delivered.

[Charlie]

Sorry for the long delay. I was in Guinea, not in Sierra Leone, and didn't have any access to the world.

Now, back to the question at hand.

For the moment put the issue of calculating payments for your delivery people to the side.

At present you have an algorithm (calculation) that breaks up your papers according to sets, some of which go out in fixed bundle amounts and others that are loose. This information is contained in one file which for the moment I'll call Basic Info. In effect, your calculation gives you a way of saying exactly how many bundles there are for each "delivery" and the count in each of those bundles (whether they be set or loose).

You now need to print out a cover/topsheet, and only one sheet, for each of your bundle sheets, properly coded so that it goes to the correct delivery person. Theoretically, you could have Basic Info do this, but it might be quite difficult because Filemaker does not make it at all easy to deal with variable reports that need to come out on set pages, etc.

What I'm suggesting is that you establish a second file, called "topsheets" or something like that. From Basic Info, you would write a script that basically breaks up the information for each delivery order into its bundles and creates records in Topsheets reflecting this information. The records in this file are essentially static because you have taken the information from Basic Info and just plugged it into certain fields in Topsheets.

As an example (and don't worry if my bundling algorithm doesn't match yours)

Let's say that Delivery Person 1 needs to deliver 476 papers (or whatever you're distributing). My algorithm says that the 476 should be broken into three sets of 150 (450), and the remaining 26 should be "loose." You have, in effect, four bundles now, so you would have your script create four separate records in Topsheet, one for each bundle. (You could link your original record in Basic Info to the new records in Topsheets with one field, such as "Order Number", so long as it is unique.) Let's call that Script "Make Topsheets."

You will simply pass (set field, probably) information from Basic Info to each of your Topsheet slave records, which will make it quite easy to show:

Order Number

Delivery Person

Date

Bundle Type

Count in Bundle

Page x of y pages (which you should be able to get by fiddling with your original algorithm).

The topsheet pages are, in effect, static, because they are not changing. Once you've made them, the information is there in those fields and will not change if you change the original numbers in Basic info. If the numbers need to change, I suggest that you create an "Update" script that will:

Go to Related Records (topsheet)

Perform Script External "Delete found set" (script in topsheets)

Perform Script "Make Topsheets"

This will replace the old records with new records.

When you're ready to print the topsheets for your order, you would just have a script in Basic Info "Print TopSheets" that will basically

Go to Related Records (topsheet)

Perform Script External "Print" (script in topsheets)

By having a static slave file (Topsheets), you also have a record of exactly what went out on what day, sort of as a backup in case you need to alter your algorithm in Basic Info. (You don't want your original topsheet info to change for past orders just because you later come up with a different bundling system.)

As for your "payroll" information, you will probably be better off creating a payroll file that gets information from Basic Info (or even from Topsheets), linked by Delivery Person or some such.

Hope this makes sense.

Charlie