Dynamicaly find fields

[2 letter pi]

I have 21 separate fields in one table....each of which is either blank or contains "yes". I need to go through these fields one at a time to find if it contains a "yes". When I get a 'yes" I need to then go to a specific field in another table and fill it in (and that depends on what is already in that field). The two tables are currently related via a 6-digit school ID number....unique in both tables.

Is there a way NOT to have to write 21 separate scripts referring to specific fields by name but rather write one script that dynamically finds the correct field in the 2nd table:

I am thinking something like the following (knowing of course it is not now set up this way)

SetField (Get(ActiveFieldname)): "Value".

Any help is gratefuly appreciated....save me a TON of time.

THANKS!!

[LaRetta]

I have 21 separate fields in one table....each of which is either blank or contains "yes".

Even if we solve this current problem for you, you will run into it over and over - I guarantee it! Your structure is incorrect. Any time you have several 'like' fields, it indicates a problem. If these fields were instead records, a simple Find would do the job. As it is, you will ALWAYS be tripping over all of these fields in an attempt to get what you need. Please reconsider your design here.

LaRetta

[Fenton]

LaRetta is probably right, that your structure is incorrect. But I'm not sure we could tell that from what you said. I can't tell what's going on -]

No, there's no direct way to set a field by name. But there's no need for 21 scripts either. The most direct method, since you have 8.5 is to name each field as an Object, on the target layout anyway. This is done in a new area at the top of the Object Info box (used to be Object Size). Then you can go to an Object by name.

After which you can use Set Field [ ; "value"]; that is, do NOT specify a target field and FileMaker will set the active field.

*So, in your first layout you can make a Loop. Start off with Commit Records, to start at the first field, then use Go To Next Field to go thru the fields. Be sure to tell it when to Exit Loop. (Read on, better idea at bottom.)

When you hit a "Yes" as the Get (ActiveFieldContents), you can use Get (ActiveFieldName) to see which it is. Then go to your other layout, go to a field object by name, and set it to something.

It would likely be better to not use a Loop. Take the time to name the fields as Objects on both layouts. Then you can go to each by name. The script is going to need a lot of If, Else If steps. But at least it's all 1 script.

*If this has to run on machines with <8.5 you're going to need 2 loops, one for each layout. And either perfect programing, or a lot of patience.

[Fenton]

Another way would be to define the target fields as auto-enter, by calculation, with [ ] "Do not replace" unchecked, dependent on a trigger field. The auto-enter calculation specifies that if their corresponding related field(s) in the other table is "Yes," then enter "xyz".

Then all you need to do is Go To Related Record [ ID; found set ]* to that table and do a Replace on the trigger field.

* Go To Related Record of all the found set of the source table, to the target table, an option of the dialog

That is, if there is a 1-to-1 correspondence between the 2 sets of fields; or something reasonable which you can accommodate in the calculation.

All and all the "dual" table structure sounds suspiciously like "redundant" data; this is the price you must pay, to keep it synchronized.

[Fenton]

A third method, which I what I think I'd do myself, is to make the "Yes" choice a radio button with an invisible script button on top of it, with a script parameter to identify it. So that you can make this happen at data entry time, one field at a time. Though the script would still need lots of Ifs, Else Ifs, to see which/what to do. If the parameter matches the object name of the fields you've got the battle half won. (The object names could be the same on both layouts, if that makes sense. They do not have to be unique in this case, as they're different layouts/tables.)

[P.S. I would likely not use "Yes". Unless there is a meaningful "No" choice, and there seldom is, I prefer to use a single number checkbox, with a Boolean value; 1 or nothing.]

Edited November 19, 2006 by Guest
PS