PSOS ExecuteSQL query of FileMaker_Fields not returning related file fields

[Daniel Berkman]

At some point the results of this query has stopped returning any fields from related files. The FMS version is 19.5.2.201. The related files are hosted on the same server, defined in External Data Sources, and have multiple table occurrences on the relationship graph.

SELECT TableName,FieldName,FieldType FROM FileMaker_Fields 
WHERE FieldClass='Normal' AND 
TableName IN (SELECT TableName FROM FileMaker_Tables WHERE BaseFileName NOT LIKE '"& Get(FileName) &" %' )

This returns an empty result. If I just query the FileMaker_Fields table without a WHERE clause, it returns lines only for fields in the local file. 

Here's another query for investigation and result...

SELECT TableName,BaseFileName FROM FileMaker_Tables WHERE BaseFileName NOT LIKE '"& Get(FileName) &" %'

Result:

Activity,
Person,
Registration,

The three TOs from the related file are listed, but notice there is no BaseFileName returned. Queries like the one below return no results.

SELECT TableName,FieldName,FieldType FROM FileMaker_Fields 
WHERE FieldClass='Normal' AND 
TableName = 'Person'

These queries DO return field data as expected for the related file when executed in the local client (19.5.201). This was working in earlier versions of FMS.

Any thoughts about the change in behavior over time and now between the local client and PSOS?

 

[rivet]

might be the wild card '%', try adding an OR to the WHERE;

Quote

(BaseFileName NOT LIKE '"& Get(FileName) &" %' OR BaseFileName NOT LIKE '%"& Get(FileName) &" ' )

 

[Daniel Berkman]

Unfortunately as I mentioned above, even if I just query the FileMaker_Fields table without a WHERE clause, it returns lines only for fields in the local file…so the records are missing!

[bcooney]

Are the credentials cascading to the second file? Is the second file open on the client when you call the psos script?

[Daniel Berkman]

@bcooney You are 100% on the right track. I forgot I was logged into the local file with a full access admin account, that does not exist in the related file. It was working locally because I had the related file open already before testing. I didn't think it through to realize the credentials for the related file on the client was not carried through.

I'm slapping my forehead. Thanks.

Edited August 31, 2022 by Daniel Berkman