Need Max for Case Function

[mtk]

I am having problems with a calculation and don't know where to go from here.

Volunteers get points for bringing in empty boxes.

You get 1 point for each empty box up to a max of 10 points. (The max is the problem)

I've written this as both IF or Case (not sure if there is a difference)

When a new record is added it doesn't know to sum up all the records for a total of empty boxes. The calculation is just doing it (correctly) for each record.

Here's what I'm using.

Case ( Empty Boxes SUM ≤ 10;Empty Double Boxes;Empty Boxes SUM>10;10)

Person brings in 6 boxes on Mon - receives 6 pts

Person brings in 4 boxes on Tus - receives 4 pts

Person brings in 12 boxes on Wed - should receive 0 pts because he/she already got 10 pts from Mon and Tues, but it gives them another 10 pts for Wed.

Empty boxes is a field in 1 TO of Points earned.

What am I doing wrong?

Thanks in advance. Sorry this is so elementary but will appreciate any help you can give.

[dansmith65]

I think we need a little more info on how the database is setup; what tables hold what data?

For example: If you have a table of Volunteers and a related table of Boxes... you would have a record in the Volunteers table for each volunteer, then when they bring in an empty box, you would add a related record in the Boxes table saying the date and # of boxes. In this case, you could create a summary field in the Boxes table, then when viewed from the Volunteer's record, it would show the total boxes brought in by that Volunteer, ever.

If you don't have the tables setup like I mentioned, then the method would differ.

[comment]

There is also the question of a time frame: does the quota of 10 ever reset (e.g. up to 10 boxes per week), or is it a lifetime limit?

[mtk]

I think we need a little more info on how the database is setup; what tables hold what data?

For example: If you have a table of Volunteers and a related table of Boxes... you would have a record in the Volunteers table for each volunteer, then when they bring in an empty box, you would add a related record in the Boxes table saying the date and # of boxes. In this case, you could create a summary field in the Boxes table, then when viewed from the Volunteer's record, it would show the total boxes brought in by that Volunteer, ever.

If you don't have the tables setup like I mentioned, then the method would differ.

Yes Dan, you read my mind. That is exactly how the DB is set up.

Primary Table of volunteers

Secondary Table of Points (incl boxes)

Do I just use that summary field in the calculation? How does it know that 10 is the limit?

(Time frame of limit is yearly.)

Thanks in advance for your assistance.

mtk

[mtk]

Hi Comment,

Primary Table of volunteers

Secondary Table of Points (incl boxes)

Time frame of limit is yearly.

Thanks in advance for your help.

mtk

[dansmith65]

how are the fields setup for points and boxes? Is bringing in boxes the only way to get points? Or are other points involved? If there are other points, are they kept track of in the same field/table? or different?

Basically, you have two options: scripted or calculated...

An example of scripted: when you enter the boxes brought in by a volunteer, you could run a script which calculates how may boxes they have brought in this year, then calculated the points accordingly and add it to a numeric field (or something to that effect).

Example of calculated: The calculation would need to be able to determine the total boxes (or points) for the current year, so you might need a relationship based on >= Jan 1st this year.

I tend to like scripted solutions: less funky relationships and unstored calculations. Again, it really depends on how the fields are setup; these are just two examples based on my assumptions.

[mtk]

how are the fields setup for points and boxes? Is bringing in boxes the only way to get points? Or are other points involved? If there are other points, are they kept track of in the same field/table? or different?

Basically, you have two options: scripted or calculated...

An example of scripted: when you enter the boxes brought in by a volunteer, you could run a script which calculates how may boxes they have brought in this year, then calculated the points accordingly and add it to a numeric field (or something to that effect).

Example of calculated: The calculation would need to be able to determine the total boxes (or points) for the current year, so you might need a relationship based on >= Jan 1st this year.

I tend to like scripted solutions: less funky relationships and unstored calculations. Again, it really depends on how the fields are setup; these are just two examples based on my assumptions.

Hi Dan,

Thanks for hanging in there with me. Sorry I'm so slow at catching on to this.

There are two tables. Volunteers and Time Sheet. I've attached a screen print of both the layout I'm using and the table relationships. Yes, I am tracking time for points as well as other jobs. I've created those calcs with some help. This one though is causing the problem and it's because of the max boxes per person. You can only get 10 pts max per year for boxes. As you can see in the screen print layout, person #1 has brought in 13 boxes. Everything was ok until they brought in 5 and went over the limit of

10. It just starting giving ALL 0's even though this person should have been given 4 pts for bringing in 5 boxes. From then on the number should read 0. That is correct.

Here's the calc I'm using.

Case (Empty Boxes SUMMARY ≤ 10;Empty Double Boxes;Empty Boxes SUMMARY>10;0;10-Empty Boxes SUMMARY)

Notice also that person #2 is not getting any credit for bringing in 20 boxes. Yikes this is confusing. Vol should have been given 10 pts then 0s from then out.

If scripted is easier, what would it look like?

Thanks in advance.

Marie

PointProblem_FMP.pdf

[comment]

It all depends on the context: if you are doing this from the point-of-view of Volunteers, you need to:

(a) limit the relationship to the selected year;

(: calculate the points for the selected year as:

Min ( Sum ( Time Sheet::Boxes ) ; 10 )

If you are doing this in a report produced from the Time Sheet table, then you need:

(a) a summary field sTotalBoxes = Total of Boxes;

( a calculation field =

Min ( GetSummary ( sTotalBoxes ; VolunteerID ) ; 10 )

This will return the number of points for each volunteer when records are sorted by VolunteerID (assuming that the found set includes only records from a selected year, or that records are sorted by year first).