Repeating Field or Multiple Fields?

[Roller]

I'm setting up a database to record attendance at board meetings. I've created two tables for this:

1. A table with fields for each person's name and whether they were present or not (attendanceflag), as well as a global field for the date of the meeting that I'm recording attendance for.

2. Another table with one record per meeting with fields for the meeting date and a list of attendees.

The list of attendees will change every time. In the second table, would it be better to store the attendees in a single repeating field or in separate fields (A1, A2, and so on)?

I've also writing a script that copies the names of attendees from table 1 to table 2. Basically, this script loops through table 1 looking for people with attendanceflag = Y and copies those names to table 2. However, as far as I can tell, there's no way to automate storing those names in successive repetitions (if I use a repeating field) or in the next available attendee field (A2, A3, etc.)

Is there a way to make the target of a Set Field script command a variable or calculation?

TIA for any ideas.

[Søren Dyhr]

This is a classic many2many sans join table...

http://www.dwdataconcepts.com/dl/tm/Many2Many.sit

(...need to caution here has some clipboard jeopadizing issues - which can be solved)

ftp://ftp.fmfiles.com/download/devhaven/tipstricks/relate/ManytoMany.zip

...all in all is the point here to make the processing on the key not real data.

--sd

[SlimJim]

I am trying to understand the structure of your solution. Correct me if I am wrong but it seems that what you are doing is this. You create a new meeting (new record in the second table) you then set the global date in the first table as the date of the meeting and go through all the records setting an attendance flag for all those who attend the meeting (I am not too sure why you need the date field). Finally you copy the list of people attending into the second table. Reset all the attendance flags to non-attending and wait for the next meeting.

If I am correct then your final question makes sense - how do you transfer the data from the attendees table to the meeting table.

It depends on how many possible attendees there are! If it is a relatively small number then you can set up the attendees field in the Meeting layout as a checkbox field showing all the name values from the attendees table. Then you simply check off the ones that are there and the field will be populated with the names separated by carriage returns. If there are too many to fit comfortably in a checkbox list then you will have to do a pop-up list and shift-click the names that attend the meeting, with the same net effect. Either way you do not have to do anything to the attendees table other than keep the list of names up-to-date. No need for flag or date.

However, maybe I have misunderstood...

[David Kachel]

>>Is there a way to make the target of a Set Field script command a variable or calculation?

No, there should be, but there isn't.

The only way around this puzzlingly absent feature is to use a script with nested IF functions;

if x, Set Field 1

elseif y, Set Field 2, etc.

Re: repeating fields; don't use them, never use them, forget they exist, they will lead to nothing but heartache and pain...

That's the rule. Here's the exception: If you have an overwhelming compelling reason that repeating fields will work better than a relationship to another table, then use them. Our product (AutoNavigator for FileMaker Pro) uses a lot of repeating fields, but only because they were the only thing that would do the job. Generally, if fields will contain client-generated and viewed data, repeating fields are a mistake. They are primarily useful for storing behind the scenes info, and not much of that.

[-Queue-]

Regarding a dynamic Set Field target, another option is to use a loop on the current layout's fields.

If [Position(

[Søren Dyhr]

Generally, if fields will contain client-generated and viewed data, repeating fields are a mistake. They are primarily useful for storing behind the scenes info, and not much of that.

I evangelize THIS as often I can, but it gets stucked in the way of Hobbesian discourse and practice of the anglosaxon world, marvelously condensed in few words by the Van Zants on their new album - Listen to tune 4 "Help Somebody" on this URL: http://www.thevanzants.com/

I dissagree largely, everything should be questioned, so just because someone stumbles over repeaters at first sight - doesn't it mean you should refrain from disuading at once ...just because you're a nation of shopkeepers!

--sd

[Roller]

You create a new meeting (new record in the second table) you then set the global date in the first table as the date of the meeting and go through all the records setting an attendance flag for all those who attend the meeting (I am not too sure why you need the date field). Finally you copy the list of people attending into the second table. Reset all the attendance flags to non-attending and wait for the next meeting.

Yes, that's correct. The only reason for the date field was to let me go back and edit the roster of attendees for a past meeting.

It depends on how many possible attendees there are! If it is a relatively small number then you can set up the attendees field in the Meeting layout as a checkbox field showing all the name values from the attendees table. Then you simply check off the ones that are there and the field will be populated with the names separated by carriage returns. If there are too many to fit comfortably in a checkbox list then you will have to do a pop-up list and shift-click the names that attend the meeting, with the same net effect. Either way you do not have to do anything to the attendees table other than keep the list of names up-to-date. No need for flag or date.

OK, I just tried the following: First, I defined a Value List based on the field that stores the names of potential attendees, and then formatted the field as a checkbox set. That's similar to what I'd done when I started, but I specified custom values (i.e., names) for the Value List, which makes editing difficult. I didn't realize that Value Lists can be based on fields. (Of course, it was staring me right in the face in the Edit Value List dialog, which makes me feel even more stupid.)

Thanks so much to you and everyone else who replied. At least my somewhat roundabout solution got me into attempting scripting!

(And I promise not to use repeating fields unless absolutely necessary. )