I have two tables:
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.
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.