text calculation from value list

[MariaS]

Hi everyone,

 

I have a calculation field in my table y that is set to display (in an edit box) all the values from several radio button fields and a checkbox field in table x. The calculation is working correctly, as far as I can tell; the problem is that after I set up the calculation, I altered the name of one of the values in the checkbox field and the old name for the value is still displayed in the calculation field, along with the new name for the value.

 

I've tried recalculating the field, unchecking all the values in the checkbox field and rechecking them, and even deleting all the values from the checkbox field. None of it has worked. I have the box checked to not perform the calculation when all the referenced fields are empty, and that doesn't have any effect, even when all the referenced fields are empty.

 

The old name still appears in the calculation field.

 

I'm baffled. Any help would be much appreciated!

 

MariaS

[David Head]

It would help if you posted the calculation you are using.

 

It sounds to me like you have changed the value list but that the old selected values are still displaying. This is expected behaviour.

 

Let's say you have a value list displayed as a checkbox with values of Apple, Berry and Cherry. In one record, you check Berry and Cherry. Then you change the value list to Apple, Banana and Cherry. The record will show that only Cherry is checked. However, Berry is still recorded as a checked value. It is just not available to be displayed, checked or unchecked.

[MariaS]

Hi, thank you. I'm sorry for the slow response, I'm not feeling well today. The calculation in my table y in field "property type" is as follows:

 

shrines properties::Residence? & " " & shrines properties::Shop? & " " & shrines properties::Workshop? & " " & shrines properties::Inn? & " " & shrines properties::Clubhouse? & " " & shrines properties::Public? & " " & shrines properties::Bar? & " " & shrines properties::Street? & " " & shrines properties::Uncertain & " " & shrines properties::Property Details

 

In table x (shrines properties), all of the fields except "property details are individual radio buttons; "property details" is the checkbox list.

 

To use your example, I had in the check box field a value list of "apple", "berry", "cherry", with "berry" and "cherry" checked, and then I changed "berry" to "banana" and checked "banana" and "cherry".

 

The values displayed in my table y "property type" field are "berry", "banana", "cherry". Is there no way to make "berry" disappear from this field? I believe you when you say it is expected behavior, but it doesn't make sense to me.

 

Thank you very much,

MariaS

[David Head]

To use your example, I had in the check box field a value list of "apple", "berry", "cherry", with "berry" and "cherry" checked, and then I changed "berry" to "banana" and checked "banana" and "cherry".

 

The values displayed in my table y "property type" field are "berry", "banana", "cherry". Is there no way to make "berry" disappear from this field? I believe you when you say it is expected behavior, but it doesn't make sense to me.

Hi Maria

Hope you are feeling better.

 

The content of a field and the values of a value list actually have no connection other than that the values are used as a convenient way to enter data into the field.

 

It would be possible to have berry disappear if it was not in the value list but that would need to be a scripted process that was run after the value list was updated. Basically, the script would do the following:

 

1. Find all records

2. Run a Replace using a calculation that checks each value in the field to see whether it exists in the value list - if a value does not exist, delete it.

 

If you are interested in seeing this script, let me know and I can write it for you. Can you confirm that you are using FileMaker Pro 8?

[MariaS]

Dear David,

 

A script like that sounds great! In the future, I will be extra careful to make sure I decide what terms I want to use before I start entering data and/or I uncheck all the terms I want to delete/change.

 

And yes, I am using FileMaker Pro 8.

 

Thank you!!

MariaS

[David Head]

OK, the script is reasonably simple. The Replace Field Contents script step makes use of the FilterValues function. This compares values in the field with those in the updated value list. It removes (filters) any values in the field that are not in the value list (called "fruit").

 

Show All Records
Replace Field Contents [ ValueLists::field; Replace with calculation: FilterValues ( ValueLists::field ; ValueListItems ( Get ( FileName ) ; "fruit" ) ) ] [ No dialog ]

 

See how you go with that.

[MariaS]

The script works great, thank you!!! The 'ghost' value has disappeared from the text calculation field. I just put the script together tonight, and thank you for writing it clearly! I'd never done a script before, so I wasn't sure if I'd be able to figure it out; but, as I said, it works!

 

I apologize for keeping you in suspense so long!!

 

Many thanks,

MariaS