Script use a variable to select a field

[Rfox]

I am an advanced beginner.

I have considered several ways of doing this but they all come to challenges.

An attendance database. To record Sunday attendance of children. I would like to be able to have a script to return who was attending on a Sunday provided by a quary. I can use Sunday numbers from weeks of the year in a repeating field (like the 25th repetition = the 25th Sunday of the year) or I could have a relational database and it would be the 25th field in the database. But all these require a way of telling the script just where to find the informaiton in a way I have not discovered. An alternative is to have a separate field for each Sunday's attendance (I already have that set up) but I have not found a way in a script to go to a field based on a variable. (i.e. "if week of the year=25 then get field May3")

In fact, there is probably a much better way of doing it that what I have even thought of. Any ideas would be appreciated. (If they are complex, please explain thoroughly or give me a way to contact you for more help. Some of this is hard to understand.)

I'm using Filemaker Pro 6.0. Thanks. The database is small - about 500 records.

[Rfox]

Well, I think I discovered one simple solution. Tell me if there is something better.

A field cGetAttendance calculated -

Case( WeekNO = 1, Jan1,WeekNO = 2, Jan2, WeekNO = 3,Jan3,WeekNO = 4,Jan4,WeekNO = 5,Feb1,WeekNO = 6,Feb2,WeekNO = 7,Feb3,WeekNO = 8,Feb4, WeekNO = 9,Feb5,"No Record")

etc. all the way through the 52 weeks. That returns the attendance for that week and I can then use it in scripts for all kinds of reports.

[LaRetta]

Hi RFox,

If the field will only be used within a Find script, it would be most efficient to consider one of the two following calculations (number) with: DayName(AttendanceDate) = "Sunday" which returns a boolean of 1 if yes...or...DayofWeek(AttendanceDate) in which 1 is Sunday.

...and then just script a find on that calculation field for a 1. If you use the second calculation, you can then search on any day of the week also because it won't be hard-coded in your calculation.

If you want it to be User Friendly, make your calculation (text) instead with:

DayName(AttendanceDate)

Then create a Custom Value List and enter each day name Sunday - Saturday. Go to the above calculation field and attach this Value List (right-click Field Format) as a pop-up.

Your script could be:

Set Error Capture [On]

Allow User Abort [Off]

Go To Field [YourCalculation]

Enter Find Mode [Pause]

Perform Find [Replace Found Set]

If [not Status(CurrentFoundCount]

Show Message [OK, "No records found. Try again."]

Halt Script

End If

Go To Layout [YourReportLayout]

Attach this script to the calc field (right-click field, Specify Button, Perform Script). Instead of creating a Custom Value List, you can also just create a Value based upon values from field (this last calculation), but you must change it to Index ON in Storage Options to use it in a Value List. In this way, only Day Names matching your record data would appear (and you could leave off the test for found records).

LaRetta

[bruceR]

Rfox said:

I am an advanced beginner.

I have considered several ways of doing this but they all come to challenges.

An attendance database. To record Sunday attendance of children. I would like to be able to have a script to return who was attending on a Sunday provided by a quary. I can use Sunday numbers from weeks of the year in a repeating field (like the 25th repetition = the 25th Sunday of the year) or I could have a relational database and it would be the 25th field in the database....

25th RECORD, not field. There would be two tables, the child table with ChildID, name, etc. And attendance, with 2 or perhaps 3 fields. In the two field case, it would be childID and date. No record for the date means the child didn't attend. Optionally you could have an additional field, AttendanceYesNo. In this case you would have one record for every child for every date but attendanceYesNo would indicate whether the child attended on that date.

[Rfox]

Here is the woking answer to this problem.

I finally replaced 52 scripts with one.

http://www.fmforums.com/threads/showflat.php?Cat=&Board=UBB15&Number=7127&Forum=,f15,&Words="variable%20field"&Searchpage=0&Limit=25&Main=7121&Search=true&where=bodysub&Name=&daterange=0&newerval=&newertype=&olderval=&oldertype=&bodyprev=#Post7127