Table::Field Variables (Is is possible?)

[shyhavoc]

First time FileMaker Pro developer so pardon the newbie-ness. Bashed my head up against this for five hours yesterday and searched forums but couldn't find the solution. Basically, I'm trying to set up "Table::Field" variables so my script can be used on multiple fields. As it currently stands, I would need to re-create this script 96 times with slight variations (tough for updating and testing). Getting Issue 1 fixed would drop that to two scripts and fixing issue 2 as well would drop that to one. I've added comments to the code below.

Any help would be appreiciated.

Thank you,

shyhavoc

-----------------

ISSUES

ISSUE 1

Attempting

Would like to replace "Students::W0101In" with a "Table::Field" variable.

Purpose

Have a single script that will work for all In Times.

Tried

Thought "Get(ActiveFieldTableName)" would be the solution but can't make it work.

ISSUE 2

Attempting

Would like to replace "Preferences::RoundIn01" with a "Table::Field" variable.

Determined by "In" or "Out" of the active field name and number (1-4) of Rounding.

Purpose

Have a single script that will work for both (and all) In and Out Times.

Tried

I can't figure out how to assign "Table::Field" variables (Issue 1). I'm stumped.

-----------------

NOTES

W0101In (Week 01 Day 01 In Time)

W0304Out (Week 03 Day 04 Out Time)

Tracking 12 weeks, 4 days per week, In and Out Times for each day

RoundIn01 (Round In Time, First, in Minutes)

RoundOut04 (Round Out Time, Last, in Minutes)

RoundIn01-04 values are 10, 25, 40, and 55

RoundOut01-04 values are 5, 20, 35, and 50

Using FMP 7.0v3, Windows XP Pro

Code attached to a Time field via "Specify Button..."

-----------------

CODE

-- Insert cursor if W0101In (Week 01 Day 01 In Time) contains content

If [students::W0101In /= ""]

Go to Field [students::W0101In]

End If

-- Insert current time in Temp Time field

If [students::W0101In = ""]

Set Field [students::Temp_Time]

Insert Current Time [select; Students::Temp_Time);0;0)]

-- Round current time to nearest quarter hour

If [Minute (Students::Temp_Time) <= Preferences::RoundIn01]

Insert Calculated Result [select; Time (Hour(Students::Temp_Time);0;0)]

End If

If [(Minute (Students::Temp_Time) > Preferences::RoundIn01) and (Minute (Students::Temp_Time) <= Preferences::RoundIn02)]

Insert Calculated Result [select; Time (Hour(Students::Temp_Time);15;0)]

End If

If [(Minute (Students::Temp_Time) > Preferences::RoundIn02) and (Minute (Students::Temp_Time) <= Preferences::RoundIn03)]

Insert Calculated Result [select; Time (Hour(Students::Temp_Time);30;0)]

End If

If [(Minute (Students::Temp_Time) > Preferences::RoundIn03) and (Minute (Students::Temp_Time) <= Preferences::RoundIn04)]

Insert Calculated Result [select; Time (Hour(Students::Temp_Time);45;0)]

End If

If [Minute (Students::Temp_Time) > Preferences::RoundIn04)]

Insert Calculated Result [select; Time (Hour(Students::Temp_Time) + 1;0;0)]

End If

-- Change 13-24 hours to 1-12 hours

If [Hour (Students::Temp_Time > 12]

Insert Calculated Result [select; Time (Hour(Students::Temp_Time) - 12, Minute(Students::Temp_Time);0)]

End If

-- Change XX:XX:XX format to XX:XX

If [Length (Students::Temp_Time) > 8]

Insert Calculated Result [select; Left (GetAsTime(Students::Temp_Time);5)]

End If

If [Length (Students::Temp_Time) > 7]

Insert Calculated Result [select; Left (GetAsTime(Students::Temp_Time);4)]

End If

-- Copy final adjusted time from Temp_Time to W0101In field

Insert Calculated Result [select; Students::W0101In; Students::Temp_Time]

-- Deselect field

Go to Related Record [From table: "Students"; Using layout: ]

End If

[Ender]

Welcome shyhavoc,

Just from your field names, I'm guessing the main problem is that your structure is not normalized. By utilizing related tables to hold data for those things that you have multiple versions of the same field for (Students::W0101In, etc.), you should be able to simplfy the scripting for editing and getting those values.

That said, you might see if the GetField() function can assist with your current dilemma (it can be used to dynamically get the value of a field, where the field name is constructed.)

[shyhavoc]

Ender,

It seems like certain parts of the code (like "Go to Field [students::W0101In]" and "Set Field [etc.]" do not provide you the opportunity of defining the field by calculation anyway, rather, only lets you choose from a drop-down menu of actual fields.

Is there a workaround for this?

Otherwise, might keeping the main chunck of automatable code in one script and making 96 snippet codes that reference the automatable code while providing the hardcoded Field information work?

[shyhavoc]

Ender,

Here is the code with the automated/probably updated elements broken out from the individual buttons. It helps but still not the one-button solution. Futzing around with the GetField() function but still can't see how to get around the hardcoded "Go to Field []" and "Set Field []" functions.

shyhavoc

--------------------------

"W0101In" (96 instances - 48 each In and Out Times)

If [students::W0101In /= ""]

Perform Script ["InTime_Full"]

End If

If [students::W0101In = ""]

Perform Script ["InTime_Empty_Part1"]

Insert Calculated Result [select; Students::W0101In; Students::Temp_Time]

Perform Script ["InTime_Empty_Part2"]

End If

--------------------------

"InTime_Full" (2 instances - In and Out Times)

Go to Field [students::W0101In]

--------------------------

"InTime_Empty_Part1" (2 instances - In and Out Times)

Set Field [students::Temp_Time]

Insert Current Time [select; Students::Temp_Time);0;0)]

If [Minute (Students::Temp_Time) <= Preferences::RoundIn01]

Insert Calculated Result [select; Time (Hour(Students::Temp_Time);0;0)]

End If

If [(Minute (Students::Temp_Time) > Preferences::RoundIn01) and (Minute (Students::Temp_Time) <= Preferences::RoundIn02)]

Insert Calculated Result [select; Time (Hour(Students::Temp_Time);15;0)]

End If

If [(Minute (Students::Temp_Time) > Preferences::RoundIn02) and (Minute (Students::Temp_Time) <= Preferences::RoundIn03)]

Insert Calculated Result [select; Time (Hour(Students::Temp_Time);30;0)]

End If

If [(Minute (Students::Temp_Time) > Preferences::RoundIn03) and (Minute (Students::Temp_Time) <= Preferences::RoundIn04)]

Insert Calculated Result [select; Time (Hour(Students::Temp_Time);45;0)]

End If

If [Minute (Students::Temp_Time) > Preferences::RoundIn04)]

Insert Calculated Result [select; Time (Hour(Students::Temp_Time) + 1;0;0)]

End If

If [Hour (Students::Temp_Time > 12]

Insert Calculated Result [select; Time (Hour(Students::Temp_Time) - 12, Minute(Students::Temp_Time);0)]

End If

If [Length (Students::Temp_Time) > 8]

Insert Calculated Result [select; Left (GetAsTime(Students::Temp_Time);5)]

End If

If [Length (Students::Temp_Time) > 7]

Insert Calculated Result [select; Left (GetAsTime(Students::Temp_Time);4)]

End If

--------------------------

"InTime_Empty_Part2" (2 instances - In and Out Times)

Go to Related Record [From table: "Students"; Using layout: ]