Searching with a referenced field

[cwbsl]

Hi there

First off, thanks a lot for this awesome library! We have a case where we would like to search all records that match field A (database A) and field B (database B). The databases are connected via a regular relationship in Filemaker. Receiving those records from a result works fine, searching just doesn't seem to work and returns:

BlueFeather\EloquentFileMaker\Exceptions\FileMakerDataApiException: Parameter is invalid in file C:\dev\myapplication\vendor\bluefeather\eloquent-filemaker\src\Services\FileMakerConnection.php on line 139

This is the query we're using:

$entries = FM::table($layoutName) ->where('fieldA', 'valueA') ->where('DatabaseB::fieldB', '1'); ->get();

The field "fieldB" is a textfield but has a 1 or some other text inside.

[Smef]

I did some testing on this and it appears to be working correctly. Please make sure that your data api user has the correct permissions in your other file, and that you're using the correct table occurrence name for your related field.

It should be entered exactly as the field name is shown in the "display data from" field in the inspector. Your example uses "DatabaseB" as the first part of the field name, but it should be a related TO name, not a database name.

[Smef]

Here's an example query of mine which queries to an external data source and works fine:

$users = User::where('name', 'david')->where('user_JSFMUSER::name', 'joe')->get();

[Smef]

Ah! I think you're using the raw FM facade instead of a model. Maybe that's the difference. I'll do some additional testing on there.

I definitely recommend using a model instead of the FM Facade, though. Can you give that a try?

[Smef]

Ah, actually, I tested with the FM Facade and it worked as well. Here's my test:

$users = FM::layout('users')->where('name', 'david')->where('user_JSFMUSER::name', 'joe')->get();

This sounds like you might have a TO or permission issue.

[cwbsl]

Thanks a lot for all your responses! I can't use an FM Facade at the moment because I have over 200 fields that I have to query and adding them all manually would take quite some time I think. Currently I can loop through many of the fields with a simple for loop which makes it a lot simpler for the moment.

As for permissions: If I only query for a single value I can see all the correct values also from the related databases. Do I need to add the same API user inside the other database as well to make queries?

Can you try to make the 'user_JSFMUSER::name" field a type text and query for the value 1 to see if it's not the field but the value?

[Smef]

I can't use an FM Facade at the moment because I have over 200 fields that I have to query and adding them all manually would take quite some time I think.

You are currently using the 'FM' Facade right now FM::xx. I recommend against doing this. You would probably be better served using an eloquent model. I'm not sure what you mean about having to add fields manually.

As for permissions: If I only query for a single value I can see all the correct values also from the related databases. Do I need to add the same API user inside the other database as well to make queries?.

If you can see the fields you probably have the correct permissions.

Can you try to make the 'user_JSFMUSER::name" field a type text and query for the value 1 to see if it's not the field but the value?

That worked fine for me. This might be a validation thing on your field.

[cwbsl]

It seems that the dot inside the fieldname is the problem. Underlines seem to work but the dot seems to throw it off. Escaping or url_encoding the dot didn't work either.

[Smef]

Dots in keys are a "known issue" with the Data API, but are also considered a bad idea in general when dealing with JSON and databases in general due to the number of issues they cause, including issues like this. I don't think Claris is going to update this any time soon as using dots is considered a bad idea specifically for this reason, so I think the best solution is to rename your TO to not include a dot in the name.

https://community.claris.com/en/s/question/0D50H00007irC2QSAU/data-api-error-when-dots-in-field-names

[cwbsl]

Thanks a lot for your help and the explanation! I have renamed the field and now it works fine!