Ok I Give , a Simple Find against a value list

[WSaxton]

Ok I thought this was simple and I might be making it harder then what it seems , I have searched the web and the forums and I guess I just don't get it

So I have a value list driven by a related table I want to search against that value list and if the field matches anything in the value list I want it to give me a 1 if no match don't do anything

I've tried the filter values , value lists etc  functions but to no avail its always a 1 no matter if it matches or not

 

So Field = LAt

 

Value list = LAT_List

 

I thought is would be as simple as

 

If LAt = (Any Value inside the list LAT_List) Then show  1

If LAT (Does not equal Any Value inside the list LAT_List) then 0

 

 

 Help me oh great Gurus

[bruceR]

FilterValues. Read the help again.

[LaRetta]

In addition to suggestion of reading Help again to gather a deeper understanding, providing an answer would have been nice since it is not obvious.  

 

Calculation (unstored number) =

not IsEmpty ( FilterValues ( ValueListItems ( Get ( FileName ) ; "LAT_List" ; LAt ) )

[LaRetta]

But one thing puzzles me ... why not just attach a value list of Lat_list to a global field and let the User select what they wish to find and then use Script to find it.  It would be an indexed search.  Otherwise, you will be searching for the 1 which is unstored and more costly.

[comment]

So I have a value list driven by a related table I want to search against that value list and if the field matches anything in the value list I want it to give me a 1 if no match don't do anything

 

Why don't you eliminate the middleman (i.e. the value list), and use a relationship to "find" matching records directly in the related table?

[WSaxton]

LaRetta -

 

Ill give that a try , Thank you for explaining deeper for me , I Was using --

 

IF ( FilterValues ( ValueListItems ( Get ( FileName ) ; "Lat" ; LAt_List )="" ) 

 

I guess I really didn't under stand the filtervalues combined with Valuelist.

 

Ill give that a try right now ,

 

To answer your other questions I will be using the 1 inside a script to locate the records and make adjustments to them. I have been working on a Google cluster map which works brilliantly but when the map is zoomed all the way in the clusters stay clustered since they all have the close to exact same gps coordinate. I'm writing a script where after I filter the records by the Variables selected by the user , then the other script will take over prior to the map being shown to compare the current gps value against the filtered value list created by the table relationship and if a coordinate matches exactly then it will add a small adjustment to the coordinate so that when the map is zoomed all the way in the final cluster is broke apart.

[bruceR]

"I guess I really didn't under stand the filtervalues combined with Valuelist."

 

Hence the first suggestion: read up about it.

 

It's basically very simple. Show which items from list A are in list B.

 

​Where either list may be a list of one or more values.

[WSaxton]

LaRetta

 

not IsEmpty( FilterValues (ValueListItems( Get (FileName) ; "Lat_List" ;Lat))

 

Give me a too many parameters error

 

So I tried

 

not IsEmpty( FilterValues (ValueListItems( Get (FileName) ; "Lat_List") ;Lat))

 

And it returned all zeros even for the ones I could see matched an item in the value list

[bruceR]

Perhaps simpler to understand:

Let([

ListA = ValueListItems( Get (FileName) ; "Lat_List");

ListB = Lat

Result = FilterValues( ListA; ListB)

] ;

not isEmpty( Result )

)

"And it returned all zeros even for the ones I could see matched an item in the value list"

 

Then there was no match.

 

Maybe the items in either list have leading or trailing space or other problems?

[WSaxton]

Bruce -

 

I did read the filemaker functions help for those to functions and thought I did understand it , which obviously I did not but you example  you just gave does help clear it up  I think I know understand the List a and be variables but I still do not understand the calculation end

 

Result = FilterValues( ListA; ListB)

 

So If List A has a value of 1234

 

And lat list has values of

1

12

123

1234

 

What does Result = FilterValues( ListA; ListB) Return

 

and if not isEmpty( Result ) has the value returned  it will show the value returned other wise it will show nothing?

[bruceR]

You tell us first.

What does it return?

[WSaxton]

Based on the function help I would hazard a guess that it would return the values that match ListA - ListB So from the example above it would return 1234? otherwise it would return 0?

[bruceR]

Not isEmpty( something )

 

only returns 1 or 0

 

But again: why are you not TRYING these things in your data viewer and proving things to yourself?

[WSaxton]

Usually I do but since no combination I tried will give me any response it does not show me anything so I cant prove or disprove , for example I can see in my data viewer the variable Lat its exactly what its supposed to be , I can see my Lat list and it match's what its supposed to be why it does not work and always give me a zero hence the reason im am here to learn where my mistake is and how to remedy it. 

[bruceR]

For example.

If it doesn't give you a match then there is not match.

[WSaxton]

Ok I see Now

 

Result equals the Value

 

Where as Not isempty (What ever field you choose) will always equal a 1 or a 0

 

That's makes sense but I also see your using carriage returns in-between you values for ListB

 

My Value list I am comparing against is a list that is being populated against a related table field , would this list contain the carriage return when evaluated or is it considering the entire value list as the number to compare against?

[bruceR]

In the data viewer you can also choose to return any component of your test; such as ListA or ListB.

Note the space problem in test 5.

Yes, a FileMaker value list is a list of items separated by returns.

This is one of the advantages of using "Let" statements.

It lets you use the data viewer to examine any particular chunk you have declared.

[bruceR]

"Ok I see Now.

Result equals the Value"

 

Well, no, that is not exactly right.

As stated before: result is the SET of values from ListA that exist in ListB.

If either list is only one item then the result is either empty or one item.

See Test3 above, for example.

[WSaxton]

Ok now really confused when I evaluate I get a one now but under watch I get a zero

 

 

Ok I just retyped the entire Let statement and boom it started working I have no idea how or why it was not working before but it is now

So hopefully my final question , I really liked how that Let statement worked and how you were able to break it down which makes sense. So which is the better or more efficient calculation a Let or and If statement?

 

Just for further knowledge

[bruceR]

That question doesn't make any sense to me.

In the end, you must make some kind of conditional logic statement (If, Case, getasBoolean, whatever)

The logic statement must have pieces to work with.

 

Using a Let statement to declare what pieces you are working with is very common and helpful for exactly the reasons of clarity that you see here.

 

Lorretta's corrected direct calc is probably marginally faster, but the difference is probably not measurable and you will have to figure out the value of clarity.

[WSaxton]

Ok ,

 

Thank you BruceR

[LaRetta]

LaRetta

 

not IsEmpty( FilterValues (ValueListItems( Get (FileName) ; "Lat_List" ;Lat))

 

Give me a too many parameters error

 

So I tried

 

not IsEmpty( FilterValues (ValueListItems( Get (FileName) ; "Lat_List") ;Lat))

 

And it returned all zeros even for the ones I could see matched an item in the value list

 

I missed the parenthesis, sorry, so what you show will work.  Did you make sure the calculation was unstored?  Look at the calculation's storage options.

 

Using example of ListA and ListB makes no sense here.  You have a list - Lat_List.  And you are comparing it to a FIELD ... Lat.  That is all.

 

If a record has 123 and your Lat_List is:

 

1

12

123

1234

... it will only match the 123 because each entry in the list is a VALUE.  Also make sure the value list Lat_List matches perfectly; if not, it will not work.  Otherwise please provide a copy of your calculation for us.

[bruceR]

Obviously use ListA and ListB did make sense.

It makes for an easily used calc that can be used in other cases.

 

And the field in question could hold a list, in the more general case, though in this example it is supposed to contain a single value.

[LaRetta]

That's makes sense but I also see your using carriage returns in-between you values for ListB

 

My Value list I am comparing against is a list that is being populated against a related table field ...

 

... though in this example it is supposed to contain a single value.

 

Oh it caused confusion for sure.  Thank you for proving my point, Bruze.  :-)

 

ADDED:  Helping someone solve THEIR problem provides the best example.