AnsweredAssumed Answered

ExecuteSQL and LookUp field errors

Question asked by tvrv8s on Feb 2, 2018
Latest reply on Feb 2, 2018 by philmodjunk

I have two tables:


XML_Table  (pk_ImportID,fk_AgentID,fk_XMLLookUpID)

Agents_Table    (pk_AgentID,OverrideAgentID,CalcXMLAgentID)

Relationship: Agents_Table::pk_AgentID = XML_Table::fk_AgentID


XML_Table is a table where we import XML files from agents. Each XML file has X number of records; each record has an fk_AgentID number corresponding with a pk_AgentID in the Agent table.


Some agents have multiple feed files for different branches, so we give them a separate XML record in Agents_Table with a unique pk_AgentID number in order to group the branches under one umbrella Agent record. We denote this by using the NUMBER field OverrideAgentID.


Agents_Table::CalcXMLAgentID is a NUMBER calculation (not stored) that checks if there is a number in Agents_Table::OverrideAgentID. If so, it uses that value, otherwise it uses Agents_Table::pk_AgentID.


If(not IsEmpty(OverrideAgentID);OverrideAgentID;pk_AgentID)


So an agent with a single branch has data like this:


Agents_Table::pk_AgentID = 12345

Agents_Table::CalcXMLAgentID = null

Agents_Table::CalcXMLAgentID = 12345


An agent with multiple branches may look like this:


Agents_Table::pk_AgentID = 67890

Agents_Table::OverrideAgentID = 09876

Agents_Table::CalcXMLAgentID = 09876


In XML_Table there is a field fk_XMLLookUpID that is a strict number Lookup of the Agents_Table::CalcXMLAgentID so when feeds are imported, each record has the correct ID recorded for various uses thereafter.



Now, the problem:


ExecuteSQL does not work even though everything is valid, and everything is a number field all the way down the line. I can get it to work in data viewer but when calculated within a script (checking every variable is correct and the right type) does not work. I just get a ?. I feel this may be a bug in Filemaker.


I want to count how many records have been imported for each agent:


$AgentID = 146018

ExecuteSQL("SELECT Count(pk_ImportID) FROM XML WHERE fk_XMLLookupID = ?";"";"";$AgentID)


I get ?. Putting the same in dataviewer works if I specify the agent id:


ExecuteSQL("SELECT Count(pk_ImportID) FROM XML WHERE fk_XMLLookupID = ?";"";"";146018)


Yes, I've checked the variable before using it in the ExecuteSQL, and I've also tried GetAsNumber around the variable. It seems to be an issue with the fact it's a LookedUp field. When I made XML_Table::fk_XMLLookupID into a number calculation that same as Agents_Table::CalcXMLAgentID the ExecuteSQL worked.


I really cannot figure out why I get the ? when it's a lookup. I've been doing ExecuteSQL for a long time and I've never seen this behaviour before. I've even restarted our server, thinking there is a glitch.


Any ideas?