Need help with summary type data

[jnmorrison]

Let me see if I can explain this clearly...

I'm building a punch clock app.

The two files in question are "timecard" and "punches".

Pay periods could be of any length of days, and are identified by a pay period ID (number).

I'm trying to account for daily overtime hours.

An employee punches in and out...which creates a record in the "punches" file.

They will most likely create multiple punches in a single day.

I have an overtime field in the "timecard" file which holds a time value. (number at which time begins to be overtime. It will most likely stay at 8:00:00, but the user can change to any value.)

So, here's what I need to be able to do:

For each day in the pay period I need to determine if the total time of all punches has exceeded the value in the overtime field (8:00:00). If it has, I need a total of just the amount which has gone over.

Now, within the "Punches" file, I created a field which, using a self relationship to the same day, returns the total over the overtime field. However, every punch record for that day now has that value. Because of this, I can't simply get a sum of that field for all related records in the pay period.

Example:

Overtime field is set to 8:00:00

Punch one on day one is 6:00:00

Punch two on day one is 1:00:00

Punch three on day one is 4:00:00

Daily overtime calculation field reads: 3:00:00

The above data is all for "day one".

So, I have three records with the value: 3:00:00 in the daily overtime field.

I can't get a sum from the timecard since it would total: 9:00:00 when it should be 3:00:00

Forgive me if I've gotten too wordy or over-explained this.

Any suggestions anyone?

[Ugo DI LUCA]

What if your SelfJoin was built upon the record instead of the date field ?

Or keep both of them if needed but use the record basis SJ for your Statistical Report.

BTW, by referencing Relationship this way, you'd end up with a slow solution, depending on the number of records you'd be entering.

One year with 1000 employees would be 365,000 records, with cross-calculations.... Better have it scripted.

[jnmorrison]

Not sure that I understand. If the SelfJoin was built upon the record, it would only look at itself instead of all records for that day. Then I couldn't get a total for all punches for that day? Explain, please.

Jeff

[Ugo DI LUCA]

Right. Sorry, dumb suggestion.

Better have it divided by Count(YourSelfJoin::Record_ID)

Or am I still off-track ?

[jnmorrison]

Actually, I think that might get me there. Then from my timecard file I can get a sum of this new calculation field.

AverageField = dailyOT / Count(SelfJoin::Record_ID)

Then from the timecard I can get the sum of the Average Fields for my total daily overtimes.

Sound right?

[John Caballero]

If you created a constant calculation, cConstant, with a value of 1, then you could divide the sum or your overtime calc by Sum(relationship::cConstant).

Ugo is right, though, that you probably want to look at a scripting solution due to the sluggishness you'll soon observe.

[Ugo DI LUCA]

Yes I think it should work, except if there's some ponderation which I don't believe.

Now, think about a loop or something else to have it indexed, rather than calculated.

SetField[DailyRecordOverTime, RecordPunch/Count(SelfJoinbyDay::Record_ID)

[jnmorrison]

How much sluggishness are we talking about, in your opinion. Here's why I ask...if I make it a script, it gets much, much harder to manage. What happens when the manager goes back and alters the time on a particular punch record or deletes a punch record. Wouldn't I then need to loop through records re-applying the script?

Jeff

[Ugo DI LUCA]

A constant by period John.

I've seen you suggesting it many times on these Forums.

I like this approach too. Sum is quicker than Count

It allows then to move quickly from one list to another, if it's unstored calc, by just going to the last record with 1 and Omit the rest (or the reverse), or just a search on it if indexed.

It becomes some very useful calc when referencing boolean.

[jnmorrison]

I usually create a "RelateAll" field in each file I make, of type Number, and have it auto-enter on creation the number "1". Using this instead of the cConstant calculation field works as well. Any advantage to going with the calculation constant rather than the number constant?

[Ugo DI LUCA]

Actually Jeff, there are several options to consider.

First, if this new calc doesn't provide any useful information as a record by record basis, which I actually think is the case, better remove it and just use a script to get the overall Daily Total with another SetField.

SetField[DailyOveral = (Sum(:SJ:PuchHour)-YourParameter)/Count(::SJ:Record_ID)

Or, just have those fields statisticals in the Footer as they come with your report, as a Total Hours - Your Parameter / Total Records

You could have your Summary Layout sorting by Day, and Period, and still your totals would match, without any reference to some relationships.

I love relationships, really, but sometime, they just won't be necessary.

They would be if you needed to have them used for some other statistical reports, then exported or kept in some arrays (repeating), but this is another story.

HTH

[Ugo DI LUCA]

Well, somehow out of topic, but I may be responsible for this, so...

The n_Constant approach is better option in my opinion than the c_Constant.

It allows for example to create related records through a portal with a Constant Relationship and "Allow creation of related records" checked, which wouldn't be possible if the match key on Right side was a calculation.

[John Caballero]

It's hard to quantify exactly how much more sluggish it will be, but suffice to say, from a qualitative point of view, the word I would use is "frustratingly" sluggish! Finding records that aren't indexed in a db with more than a few hundred records will cause most users to think something is wrong because the find takes so long. Ugo's suggestion of scripting everything has the advantage that when you set values you can index the fields so searching happens much more quickly.

Yes, scripting does require a bit more development time and attention - for instance, you'd want to script changes/deletions, but again, conventional wisdom says the advantages outweigh the inconvenience.

[Ugo DI LUCA]

Agreed. Time for developing and time for Parties..

Week-end starts guys...See you on Monday.

Bye

[jnmorrison]

Thanks for your help, all.