2 Replies Latest reply on Mar 30, 2015 5:27 AM by philmodjunk

    ExecSQL does not (seem to) recognise foreign details



      ExecSQL does not (seem to) recognise foreign details


      Hi there

      I have several ExecSQL calculations on a table (Let's call it Table Two as I set it up second) which reference a single detail which is brought in via relationship and calculated field from a foreign table (addresses).  Table Two is the problem.  

      I have another table (let's call it Table One as I set it up first), set up in a similar way, which references the same foreign table above in the same way.  Again, the same single detail is pulled through onto this table.  

      The code is pretty simple - I just want to count clients per county who have information on this table:

      ExecuteSQL ("
      Select COUNT(ClientID)from TableTwo where 

      County=? <<<<< This is a calculated field brought in via the relationship to the table I have the SQL on






      This code works perfectly on Table One, but Table Two, it doesn't distinguish between counties - instead of say, a count of 3, I get a count of 0.

      1) Why would this work perfectly for table one, but not table two?

      2) I cannot see that ExecSQL has an issue using calculated fields in its own syntax because it's worked perfectly on Table One?

      I'm pretty stuck on this as the two tables are virtually identical in make up and are identical in relationship to the address table.




        • 1. Re: ExecSQL does not (seem to) recognise foreign details

          OK, found my own solution.

          My Table One has a second report attached to it which had sorted the data.

          Table Two, being new, had not sorted the data, so despite SQL being SQL, it still needed a sort based on my required fields to make it work....

          Sort, sort, sort...First rule of Filemaker, right?

          • 2. Re: ExecSQL does not (seem to) recognise foreign details

            That doesn't sound right unless your calculated field uses GetSummary, or you mean "found" instead of "sorted".

            Address::County will access a value from the County table occurrence. Whether or not you can access a value from the table identified by that table occurrence will depend on the "context" in which your calculation evaluates. If this is a script step this is determined by the layout that is current and the values in match fields of the current record on that layout. If this is a calculation field, the "context" table occurrence that you select for your calculation determines the starting point for determining what, if any, value is accessible via that reference.