Filtering field update

[panchristo]

Hi there!

I have a filemaker solution where a Cases table is related to a Persons table through a portal, to generate a dynamic value list for persons of the same case. From the Cases table also, several tables of Characteristics can be accessed by selecting the corresponding person that applies from a field with the dynamic value list mentioned before. What I need is that the field with the selected person gets updated in such a way that if a person is deleted from the Persons portal the filtering field will be cleared so no characteristics are shown from related tables.

I tried to do this by an Auto-enter calculation of the match field checking whether the value stored exists in the value list but it seems that it doesn't evaluate in the background.

Any ideas?

[LaRetta]

Can you provide a example file of your situation?

[panchristo]

Thanks for your reply.

Unfortunately everything is in greek so it won't be easy to translate.

But let me simplify by saying this:

Once a value is stored in a field using a value list, how can you erase the value entered if the list is changed and doesn't include the value anymore? I would like to be done automatically.

What I've been thinking of is using an Evaluate function in the AutoEnter tab of the field, for example:

Evaluate(

Case(

PatternCount(ValueListItems ( Get ( FileName ); "CustomValueList");Value)=0;

"";

Self

)

)

Which is supposed to check if the value in the field Value exists in the list CustomValueList and if it doesn't set the field to "", otherwise leave the field as is.

But it doesn't work. Can you tell me why?

[LaRetta]

"Once a value is stored in a field using a value list, how can you erase the value entered if the list is changed and doesn't include the value anymore? I would like to be done automatically."

Well, I asked to see your setup because if you delete the value from a field (and it is the only record with that value) then it will disappear from your value list automatically (if you have your value list set up as 'Use values from field.' If there is more than one record with the value, that value must be removed from all the records. Your question sat here for 1 1/2 days which indicates that others who respond here were probably scratching their heads as well.

I cannot tell from your original description how you have your relationships set up. A sample file of the structure and existing value list (or your original file, saved as copy with no records even) would answer the questions just as well (although you might have to translate those key fields if I can't translate them using babelfish.

:smile2:

Edited March 17, 2010 by Guest

[comment]

I think you want the FilterValues() function. But it is a very strange request: value lists are used as an aid in data entry. Suppose your value list is "Apple¶Orange" and you have selected "Apple" for a record. If you delete "Apple" from your value list, is the record no longer an apple?

[panchristo]

Thanks to all!

I'll try to keep this simple.

Here is a sample of the puzzle. (just rename to fp7)

The interface needs to function as follows:

A new case is created, which involves one or more persons entered in the portal of the first tab.

The other two tabs (many more in the real file) contain characteristics (about 100 in each real table) which can be entered, upon selecting a person from the value list (radio buttons).

What I need to achieve is that if you delete a person from the portal, not only the related record in the relevant table of characteristics is deleted, but also the "filtering" field is cleared. Because otherwise, the value remains (although the list is updated) and a new un-related record can be created by mistake.

Is it clear now?

Once again thanks for your help...

P.S. Please don't suggest I use portal in the other tabs as well because I have around 100 fields per characteristics table which also work with other related records, practically impossible!

CASES.zip

[LaRetta]

Hi panchristo,

... I have around 100 fields per characteristics table which also work with other related records, practically impossible!

This sentence concerns me. Normally it is wise to have each characteristic as a record instead of a field - related records (vs. fields) provides the flexibility you seem to lack.

Truth is, if you listen to Michael (Comment) then you'll probably be fine in all regards. But I will nonetheless devote some time this weekend to review your situation in further detail; I simply can't right now. Trust that we'll help you through it. :wink2: