Calculation on a repeating field

[Tonyh29]

Hi all ,

hope someone can help ..I've inherited a filemaker database at my place of work due to the previous person leaving

 

I'm slowly finding my way through it but need some help

 

I have a simple Staff Contact DB that also records Holiday /sickness

 

Fields are

 

Leave Type (you enter Holiday , sickness ,Unpaid etc via a pop down list) it's a repeating field with 25 repeats ... I've been reading this forum and seen people don't like this method but I'm 3 days into FM 9 so one step at a time ...

 

The actual dates taken (easy enough ,wasn't going to do anything with these )

 

Days taken ..which is another repeating field with 25 repeats

I.e One for each leave type instance

 

I want the database to add all the holiday dates and then subtract it from their annual entitlement

 

I've managed to Very simply use :

 

If(Leave Type 07="Holiday" ;+ Total Days 07)

 

if I show total days 07 in its full 25 reps I can see it has worked ,i.e. it ignores them if they are sick days

 

But

What I can't get it to do is actually total all the numbers from "Total Days 07" and deduct them from their entitlement

has to be something to do with this repeating field ...but what

 

 

Sorry it's so obvious but as I've said I'm on day 3 of my FM education

[AHunter3]

You're in for a rougher learning curve if you try to work with those ^#$@! repeating fields than if you follow instructions for splitting them off into a table of their own.

 

But OK, create a calc field with as many repetitions as you've got on these other repeaters, defined as Case (Type = "Holiday", Days Taken), call that field Holiday Taken.

 

Then create a nonrepeating calc field TotalHolidaysTaken, = Sum (Holiday Taken).

 

Finally, subtract TotalHolidaysTaken from your field of # of holidays to which employee is entitled.

[Tonyh29]

Easy when you know how

 

Thanks for reply ..appreciated

 

no doubt I'll be back when I feel brave enough to remove the repeating fields and go for tables :-)

[AHunter3]

Sooner or later someone's gonna ask for a report on ALL time off, split into sections for each employee then within each employee showing Sick (line items with totals at bottom), Vacation (line items with totals at bottom), unpaid (etc), Organizational Holiday (etc), and then subtotal of all time off for that employee, then next employee, and so forth.

 

That's when we'll be seeing you, if not sooner

[tmark75]

Could you explain how to split a repeating field off into tables? I currently have a database that I use for my grad students to record their plan of study courses (repeating field) as well as the actual courses they've taken (repeating field). Also do you have a suggestion for me to compare the two (aside from listing them side by side? God forbid I have to do anything manually smiley-laughing )

 

Thank you!

 

 

 

You're in for a rougher learning curve if you try to work with those ^#$@! repeating fields than if you follow instructions for splitting them off into a table of their own.

 

But OK, create a calc field with as many repetitions as you've got on these other repeaters, defined as Case (Type = "Holiday", Days Taken), call that field Holiday Taken.

 

Then create a nonrepeating calc field TotalHolidaysTaken, = Sum (Holiday Taken).

 

Finally, subtract TotalHolidaysTaken from your field of # of holidays to which employee is entitled.

[AHunter3]

If you don't trust the script to correctly split your reps off into a new related table, you probably won't trust a different script to tell you that it did it properly. I'd just look at a handful of them side by side. Not all of them.

 

Anyway, how to do it:

 

Loop

..Comment ["This is your outside loop, it loops through RECORDS"]

..Set Variable [$Rep, 1]

..Set Variable [$SerialNo, Your'Table::Serial Number]

..Comment ["Assuming YourTable is to be related to NewTable via serial number"]

..Loop

....Comment ["This is your inner loop, it loops through REPETITIONS"]

....Go to Field [NewTable::Field A]

....Go to Portal Row [by calculation, $Rep]

....Set Variable [$Field A, YourTable::RepeatingField A [$Rep] ]

....Set Variable [$Field B, YourTable::RepeatingField B [$Rep] ]

....Set Variable [$Field C, YourTable::RepeatingField C [$Rep] ]

....Set Field [NewTable::Field, A, $Field A]

....Set Field [NewTable::Field B, $Field B]

....Set Field[NewTable::Field C, $Field C]

....Set Variable [$Rep, $Rep + 1]

....Exit Loop If [isEmpty (YourTable::RepeatingField A [$Rep] & YourTable::RepeatingField A [$Rep] & YourTable::RepeatingField C [$Rep] & YourTable::RepeatingField A [$Rep + 1] & YourTable::RepeatingField B [$Rep + 1] & RepeatingField C [$Rep + 1] ]

.... Comment ["This is the end of the inner loop"]

..End Loop

..Go to Record [Next, Exit After Last]

..Comment ["This is the end of the outer loop"]

End Loop

 

 

Other assumptions not mentioned: that NewTable is where you're moving the repeating-field data; that your relationship betwen YourTable and NewTable allows for creation of related records in NewTable via the relationship; that you have three affiliated repeating fields, RepeatingFields A, B, and C, and that Repetiton 2 of RepeatingField A "goes with" Repetition 2 of RepeatingField B, etc; and that no more than two consecutive totally blank "lines" (repetitions of the various repeating fields") will be encountered above valid "lines' (repetitions) containing actual data.

 

OH, and that yeah you did put the portal on the layout and put at least Field A actually in the portal, before running the script.