Jump to content

Need help with summary type data

This topic is 7502 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.


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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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?


Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.


Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 7502 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.