How to 'setField' and address the current field

[alexliz]

In case the Subject doesn't mean anything yet, here it goes:

I want to setup a generic script which will toggle a field's value between "" (i.e. nothing) and "yes". If it's 'yes', upon clicking the field it makes it nothing; if it's nothing, click the field and make it 'yes'.

I have a few fields which I want to toggle between these two values. I also prefer to script once for all these instances, and avoid having to script once for toggle_fieldA, then toggle_fieldB, then C, then D etc.

Obviously I can't use the straight-forward SetField script step, because it requires the target field name, and I don't know it now, since I am trying to script for multiple fields.

I therefore need another step, to capture the name of the field where I've clicked (and thus activated the script), so I use a Get(ActiveFieldName) script step;

now I need to pass the value from the previous Get step into a variant of the SetField command.

But how?

Many thanks, as always, people.

PS Around 5 years ago I scripted this very thing in FMP5. For the life of me, I can't remember in which database, and how!!!

[LaRetta]

You can easily do this now in vs. 10 using Set Field By Name[] and script parameter. However, before we go down that path, I must mention that having yes/blank options on several fields, indicates that you probably could have handled the structure more properly by having those questions (currently each field) as records.

As it is, each time you want to add a question, you must add a field. And if you change the wording of the question, you must change the field name (or label) as well. This type of structure also makes reporting, searching, counting etc. a nightmare.

If you would like to consider alternate approach, we would be happy to review it with you. :smile2:

[alexliz]

@LaRetta

Thx for the quick response! I am open to new suggestions. Do enlighten, please!

[LaRetta]

Can you post a sample file of that table so I can see what we are working with? You can import your existing table directly into a new sample file. It will give me a solid starting point for this discussion.

[alexliz]

Let's see if this helps for now, as it's easier for me to do:

simple table for Employee-Task assignments; contains:

pk_id (primary key)

name_last

name_first

suitable_for_task_1 (yes/"")

suitable_for_task_2 (yes/"")

suitable_for_task_3 (yes/"")

etc.

Like I said in the OP, I would prefer to script once for all the 'suitable_for_task_X' fields, so I can short-list employees in drop-down lists.

Thx!

[LaRetta]

I believe your example is enough to provide solid input ...

With your current approach, you cannot generate a report by Task number because there would be no way that Employee #2 could appear in Task 1 and Task 2. If one employee can be available for many tasks and each task can have many available employees, I suggest a join table.

I started to create a demo for you but remembered a sweet example by Comment:

http://fmforums.com/forum/showpost.php?post/340683/

Objects will be your Employees table. Attributes will be your tasks table where you list each task description/name. ObjectAttributes will be your join table between them. This allows ultimate flexibility; just add tasks to the task table or change their descriptions. Generate your reports from the join table of ObjectAttributes. If you have questions on any of this, just let us know.

UPDATE: When you click an attribute, it creates a record automatically in the join table (using Allow Creation of Releated). You will notice attached to the checkbox with script parameter. This is a clean, powerful, and simple method. Notice that the join table only holds IDs and the other fields are simply cross-placed (Object Name and Attribute Description are fields from the related tables) ... no need to duplicate any information.

Edited December 13, 2009 by Guest
Added update then corrected sentence

[alexliz]

I agree that the join table is the way to go, but for a simpler approach, why is it not possible to assign multiple employees to multiple tasks simply by inserting the corresponding fields and then tracking the 'yes' value in them?

Like I said, I much prefer the join table schema; I am just curious to understand the handicap you mention.

Thanks!

[LaRetta]

why is it not possible to assign multiple employees to multiple tasks simply by inserting the corresponding fields and then tracking the 'yes' value in them?

You can. But, aside from the issues of adding a field each time you add a task, or requiring modification to the field name if you change a task, the report issue is the HUGE gotcha:

You would like to see a report sub-totaled by Task number (listing all employees available for each task under the task number). Since each task is a different field, it simply can't be done ... grouping and summarizing is based upon ONE field. So you can't produce:

Task 1

... Bill

... Jane

... David

.......... 3 employees for Task 1

Task 2

... Jane

... Nellie

.......... 2 employees for Task 2

And since the report must be based upon the Employees table, Jane can't possibly appear in the group of Task 1 and Task 2 at same time because each record in the table is an employee. Try playing around with your existing structure and creating a grouped report by task. It'll soon become clear, I think.

There are other reasons that using fields (for tasks) instead of records will cause you grief ... I've only mentioned the more obvious ones. :smile2:

[LaRetta]

BTW, 'simpler approach' is a deceptive puppy. To many who are newer to relational theory, flat file many seem simpler but it is 100-times more complex once you start working in it. The demo by Comment is as simple and flexible as possible.

[comment]

In addition to what LaRetta already said:

If you still prefer to create a many-to-many relationship without a join table, you should use a SINGLE field for all of the object's attributes, i.e. instead of:

suitable_for_task_1 (yes/"")

suitable_for_task_2 (yes/"")

suitable_for_task_3 (yes/"")

you should have a 'suitable_for_tasks' field, containing a return delimited list of TaskID's for which the employee is suitable (a checkbox field does exactly that).

This way, you only need one relationship to the Tasks table, while your method would require three separate ones.

[bruceR]

In case the Subject doesn't mean anything yet, here it goes:

I want to setup a generic script which will toggle a field's value between "" (i.e. nothing) and "yes". If it's 'yes', upon clicking the field it makes it nothing; if it's nothing, click the field and make it 'yes'.

I have a few fields which I want to toggle between these two values. I also prefer to script once for all these instances, and avoid having to script once for toggle_fieldA, then toggle_fieldB, then C, then D etc.

Obviously I can't use the straight-forward SetField script step, because it requires the target field name, and I don't know it now, since I am trying to script for multiple fields.

You CAN use the set field script step; you just have to omit specifying the field.

Set Field [ ; Case(get(activeFieldContents) = "Yes"; ""; "Yes") ]

[alexliz]

Thank you all for the excellent feedback! I will now get to work and implement this insight!

-A