Select Range of Repititions?

[ScoobyDoobyDude]

I am currently developing a timesheet database that allows employees to time in and out twice each day, as well as manually enter holiday, sick/personal, vacation, and other time off.

To make the rows necessary for a six-day week [Monday through Saturday--office is closed Sunday], I have set up a table with repeating fields:

TimeIn1

TimeOut1

TimeIn2

TimeOut2

... as well as other calculation fields designed to take a total for that day's hours.

My question is: How do I select a particular range of repetitions in order to calculate overtime? The way my office prints its timesheets, overtime needs to be listed for each day it is earned. For example, if I work 9 hours per day Monday through Friday, then Friday will contain 4 hours of regular time and 5 hours of overtime. In order to get the overtime column/field to display "5" on Friday [5th repetition] and display 0 or nothing on the preceding days, I need to find a way to take a running sum of the current repitition's total work hours, along with all previous work hours that week. I don't think the SUM function will work, because it seems to take a sum of ALL repetitions, not just from the beginning up to the current one. Think of what I need as a running subtotal on a cash register receipt. Even if there are 50 items on the receipt, the subtotal will not be the full amount by the time the 5th item prints out on the receipt.

Clear as mud? Thanks in advance for any advice you can provide.

[Lee Smith]

Let me guess, you are using the file that came with FileMaker?

Lee

[ScoobyDoobyDude]

Actually I'm making this one from scratch. I reviewed the template file to get some ideas, but I was disappointed by the fact that it uses separate fields for Day1, Day2, Day3, etc. I think the repetition method is much more effective... I just can't find a way to get the sum of a particular range of repetitions. It's so easy in Excel... SUM(A1:E1). It seems that in FM, using the SUM function only yields the sum of all repetitions, not just up to the current repetition. Any ideas on how I can get a "running subtotal" of hours? Thanks.

[bruceR]

You really do NOT want to use repeats, and this is just one of many reasons why. They are just as bad as Day1, Day2 etc. Use a proper design with related child records. You can then establish a relationship by day or employee or employee+week or whatever to get the sums you're looking for.

[ScoobyDoobyDude]

Thanks for the heads-up, Bruce. I'm new to database design in general, and the repeating fields seemed like a good idea. Can you think of a situation in which they would actually be useful? I would like to know if they actually serve any useful purpose. Thanks!

[mr_vodka]

There are tons of threads on here regarding when people feel repeating fields should be necessary and definately why not to use repeating fields.

I personally use repeating fields sometimes for user interfaces, temp data, and storing global images. Repeating calculations are sometimes used as well.

However, I never let my users do any sort of data entry into repeating fields.

My 2 Cents.

[Genx]

I'm with John -- Possibly back end storage of images, Temporary storage of variables, or use in calculations, NEVER to try and replicate a standard one - many relationship by using a repeating field -- bad idea.

With the related method, because each child record is actually a record, you can manipulate all the data into ways that suit you very easily, where as with repeating fields, once it's in... good luck trying to do anything useful with it.

[dazlunn]

The only thing I use them for is on a Settings layout to determine the dimensions/placement of new windows. I have a global repeating field with 4 repetitions e.g.

Settings::A4Landscape[1] Window Height

Settings::A4Landscape[2] Window Width

Settings::A4Landscape[3] Window Top

Settings::A4Landscape[4] Window Left

When a new window opens it grabs the relevant set of dims..

HTH

[ScoobyDoobyDude]

Admittedly, I have asked this question before. However, since then, I have found what should be a solution to my problem. Unfortunately, my solution is not working.

My problem: I need to calculate the pont at which a person entering time into a timecard layout starts to accrue overtime. In other words, if a person accrued 36 hours of time Monday through Thursday, Friday's row should display 4 hours of regular time and 5 hours of overtime, assuming 9 hours are worked in the day. I have chosen to do this using a repeating field with six repetitions, for Monday thru Saturday. I have created calculations to evaluate whether or not overtime is due, based on a running total of all actual hours worked that week.

I know people have advised me not to use repeating fields due to their sometimes limited features, but I feel this is the best way to organize my timecard data, because if payroll is done weekly, each week's data will use one record in the table. I have found that creating a Summary field, evaluating all repetitions individually, for a running total of the ActualHoursWorkedThatDay field, should allow me to easily calculate overtime.

My problem is that for whatever reason, every repetition of the running total Summary field displays the same number, rather than a running total. Am I doing something wrong? I have checked my options carefully and it seems I'm doing everything "by the book." Thanks in advance for any help you all can provide.

[Genx]

.. I was just replying to your other post..

You are doing everything by the book... by the FileMaker 3 or 4 book. Shouldn't have chosen to use a repeating field... I'm so glad i didn't even know these existed when i started out.

You need to organise your data into a related structure, then you can pull whatever you want, whenever you want, however you want.

2 Tables

1) Person

Person_PK

Random Person Details

2) TimeCard

Person_FK

TimeCard_PK

StartTime

EndTime

Date

With this type of structure, not only is what you mention easily accomplished, it's much more dynamic in terms of the way you can present your data, the way you can filter it, summarize it etc. You could even have different definitions of overtime for each person.

[comment]

That's what summary fields do: they summarize the found set. So, if you have a repeating field, and the first repetition is for Monday, the repeating summary field will summarize all Mondays of all found records in its first repetition - and so on.

To summarize a repeating field within the record, you need to use a calculation field with one of the aggregate functions. Unfortunately, running total is not one of them.

I tend to think you don't need a running total at all, but I didn't understand from your description what the rules for overtime are, so I cannot be sure.

[comment]

You are doing everything by the book... by the FileMaker 3 or 4 book.

The book - that is the rules for normalization - is the same for all relational versions, from v.3 and up. However, there's no law that says that data has to be atomized by day. If one wants to make week the smallest data unit, I can see nothing wrong with that - provided one is aware of the consequences.

[kassab mahareeq]

I want to figure out why my portals scrolls from the top-to-bottom when creating new record?

[Genx]

Do you think one is likely to be aware of all the possible consequences as a novice?

[ScoobyDoobyDude]

I appreciate everyone's help with this. I know a lot of people are advising me not to use repeating fields, and I'll definitely keept that in mind for more complicated projects. At this point, I think the repeating fields are the most logical solution for this case, but obviously this running total concept presents me with some limitations.

comment, you said that there is no way for me to keep a running total, across repetitions but within one record only? For example, a running total from repetitions 1 through 6? When I chose the option to evaluate all repetitions together, the field correctly displayed the total of all repetitions in that record. Unfortunately, because the Summary field was not a repeating field [based on selecting the "evaluate all repetitions together" option], it would not keep the running total as I wanted it to. My theory was that by selecting the option to evaluate repetitions indivdidually, FileMaker would provide the kind of data set I am looking for.

Comments welcome. Thanks!

[comment]

you said that there is no way for me to keep a running total, across repetitions but within one record only?

No, I did not say that. I only said that it cannot be done by a summary field, or by an aggregate function. But I see no point in figuring out how to do it, before I understand why it's needed.

When I chose the option to evaluate all repetitions together, the field correctly displayed the total of all repetitions in that record.

I don't think so - unless that was the first record in the found set.

[comment]

Do you think one is likely to be aware of all the possible consequences as a novice?

Given all these warnings, I'd have to presume yes. The question is - are you? As I am reading this thread again, nowhere do I see an explanation of how using a related table will help in calculating the overtime.

[Genx]

Well the OP actually posted an entirely new topic first -- I wrote my reply, then he deleted his topic and reposted his post here word for word... and so I assumed it was the same issue.

Anywho, not arguing, especially with you, you have this thing where you're always right, I might as well quit before I dig this hole any deeper.

[Genx]

Anyway, take a look at this, I put it together but i'm not sure if it's what you want.

repTest.zip