Search code examples
curlfilemaker

Filemaker 17 API "AND" query

I am having a difficult time formulating an "AND" query with the FileMaker 17 API.

Reference: https://fmhelp.filemaker.com/docs/17/en/dataapi/index.html#perform-a-find-request

This is being passed into CURLOPT_POSTFIELDS. 

"{"query":[{
    "uniqueID":"fooBar",
    "anotherField":1
}],"limit":"1"}"

What I'd like to have happen is that I retrieve 1 record for which the uniqueID is "fooBar" and the anotherField is 1.

However, I get the same record if I pass:

"{"query":[{
    "uniqueID":"",
    "anotherField":1
}],"limit":"1"}"

So this is doing an "OR" lookup.

How can I structure this request so that it performs an "AND" lookup. In the case of my second example, the query should return no records, as every record in my table has a uniqueID?

Updated post-answer:

In my analysis, it seems that the second query in my question simply is an AND for any uniqueID which contains literally nothing, which I guess Filemaker somehow interprets as being true for any record.

For someone coming from MySQL, this is very confusing behavior. And the documentation, while demonstrating this as a global property of FileMaker, is not apparent when looking at the Filemaker API documentation and examples.

Solution

  • You are doing an "AND" find. If a field is empty in a find then it will not be searched on. In your counter example you were only searching for fields with anotherField:1. If you want to search on an empty field use "=" as it will only find empty words.

    Not enough Rep to comment but for your previous answer:

    For Filemaker finds on a uniqueID you would want to use "==" . "=" will give you a word match while "==" will give you an exact value match. See https://fmhelp.filemaker.com/help/12/fmp/en/html/find_sort.5.6.html