How to make a field's value dependent upon a radio-button field

[Barry F-H]

Hi!

I have three databases related to a fourth ("main"), each of which contains information about participation in a variety of workshops.

Participation data are entered into the databases via radio-button fields ("attend?"). Each of these fields is associated with a list value comprised of "yes" and "no show."

Some of the workshops lasted six hours, others seven.

The goal is to track the number of participation hours for each participant. So I have created an "hours" field for each of the workshops (e.g., "Day 1 hours," "Day 2 hours," etc.).

How can I program the "hours" field so that the appropriate number of hours (either 6 or 7) automatically will be entered into that field contingent upon the status of the corresponding radio-button field? For example, if the "yes" radio button for Day 1 is clicked, the "Day 1 hours" field automatically would adopt the value "6" (or "7," as appropriate)? Then I will need to generate a grand total for the values of all of the "hours" fields containing values for each participant.

I hope the above is clearer than mud. ;-)

Many thanks in advance!

[Chuck]

Assuming that you have a field somewhere that tracks the appropriate number of hours, such as Appropriate_Hours, you can set your Hours field to be a number calculation with the following:

Case(

[Barry F-H]

Chuck:

Many thanks for taking time to reply.

Here's my modification of your suggested script:

Case(DB Gr 3-8 July 17-21::ATTND Day 1 = "Yes", Gr 3 to 8 Math Solns July 17 hrs,

[Chuck]

It's more of a funciton of the relationship name than the file name. Although a relationship is named by default the same name as the file the relationship is to, it doesn't have to be named that way. I usually name my relationships File|Related_Field so that I know by looking at its name what field it is based on. Usually the field name is the same on both sides of the relationship for me, but if there is a case where it isn't, I name the relationship File|Local_Field~Foreign_Field.

Your file name has a dash (-) in it, which FileMaker uses for calculations. When you created your relationship, you were alerted to the fact that although it would accept that name, it could not be used in a calculation because it had a mathematical symbol in it.

You can keep the file named the same, but you'll need to rename the relationship so that it doesn't have any mathematical symbols in it. Then you'll be able to use it in your calculation.

Chuck

[Barry F-H]

Chuck:

Many thanks for taking time to reply again. Your feedback has helped me in making progress, but something still needs fixing.

You're quite correct, I disregarded FMP's warning when I created the relationships. Bad decision. I'll know better for the future.

I have now renamed all four database files and all relationships, removing the offensive characters.

I have changed one of the HOURS fields in "MASTER" (the main database) to calculation, and then entered the following script:

Case(ATTND Gr 3 to 8 July 17 to 21::ATTND Day 1? ="yes", "6")

In this script, "ATTND Gr 3 to 8 July 17 to 21" is the name of the relationship between MASTER and one of the subdatabases, "::ATTND Day 1?" is the name of a radio button field in that subdatabase (this field is based upon a Value List comprised of "yes" and "no show"), and "6" represents the value that should appear in the HOURS calculation field when the radio button is set to "yes."

FMP accepted the above script without any alerts.

Unfortunately, however, nothing appears in the calculation field of records in which the radio button is set to "yes." I even tried clicking the "no show" button, and then reclicking the "yes" button in one record. Nothing happened in the calculation field.

I have tried setting the calculation result to "number" and to "text" and the outcome is the same; the calculation field remains blank even when the radio button is set to "yes."

What am I still doing improperly?

Barry

[Chuck]

Could there be more than one record related to your master database and you're not editing the first one? If you have an equation like that, the relationship can only look at one record, and if there are many record related, then it will work with the first one created (or the first one in the sort order, if the relationship is set to be sorted).

Chuck

[Barry F-H]

Chuck:

Many thanks again for your time and help.

Your last posting used the word "record," and I'm wondering whether you actually meant "field."

Assuming you did mean "record," the main database has almost 700 records, one subdatabase has about 25 records, another 125, and the third 250. So the answer to your question is "yes," my related database does have more than one record. When I scroll through the records in the main database, the data from the related database *do* appear correctly in the appropriate fields of the main database's records. So I think the relational functionality is working properly.

Assuming you meant "field," the fields in the main database are different from those in the related one except for the matching field (which currently is the "full name" field, but soon will be the "social security number" field after I've gotten all of those numbers entered into the main database).

So I'm still stumped as to why the CASE command is not functioning properly as described in my previous message.

Is there some additional information/details about this scenario I can provide that might help figure out a fix?

Also, out of curiosity, I'm wondering if you could please explain why the CASE command is appropriate for my needs rather than the IF command?

Many thanks!

[Barry F-H]

Good news - it works!

The problem was with the liveware; I mistakenly had been looking at records in the main database for people who did *not* have records in the subdatabase (the former has ~690 records, the latter ~250).

When I queried for records in the main database for people who also had a record in the subdatabase, the hours field contained a "6."

Thanks again for your time, help, and patience!

Barry