Please explain what results you want for this. What list of values do you want to produce in order to compare the value of field A to it to deterimine if that value is in the list?
Just guessing here, but if you want the value of Field A returned if that value is in Field B of any record in the table or in Field C of any record in the table, you could do this:
Define a value list of all values in Field B. Define a value list of all values in Field C.
Use this expression:
Filtervalues ( List ( ValueListItems ( get ( FileName ) ; "RieldBValueList" ) ; ValueListItems ( Get ( FileName ) ; "FieldCValueList" ) ) ; Field A )
ValueListItems is what I was using before but in the main database that i am creating what represents Field B and Field C are calculation fields that can not be indexed and When I try to make them into a value list, Filemaker tells me that it will not work. So, using Value List is not an option.
So I want to know is there a way to make the expression
FilterValue (Field A; Field B) and FilterValue (Field A; Field C) work, or simply use the list Field C not as a value list for FilterValue (Field A; Field C) (Note the first image for Field C is wrong as a list it only shows 3's ) Because when I try these calculations all it returns is empty cells.
However, I do know of an alternative as I can reverse the formula and make Field A into the list as it is unchanging and can be indexed but I believe that that may make things even more complex for my table. So if there is another way please let me know.
I help a lot of people at the same time so while I sort of remember your earlier posts it leaves me with an imperfect understanding of what you have set up ith Fields B and C. Without knowing how the values in B and C are derived, I can't really suggest anything.
Hmmm, on second thought.
If it is a list of all values in the table for these two fields, you could define a new calculationf field, BC as List ( B ; C ).
Then a self join:
Table::anyfield X Table 2::anyfield
Where table and table 2 are occurrences of the same table with X specified as the cartesian join operator.
Then you can use List ( Table 2::BC ) to produce a list of all values in B and C in a single list for use with filtervalues.
That works great, and I dont have to index the list. Thanks