3 Replies Latest reply on Feb 11, 2015 6:57 AM by philmodjunk

    Execute SQL: Summing

    tays01s

      Title

      Execute SQL: Summing

      Post

      From a post that must have got lost in the pile: http://forums.filemaker.com/posts/c7029d0d92?page=2

      My current equation is:

      ExecuteSQL("SELECT SUM (in_Kcal) FROM FeedsUsed 2 WHERE (Route='G' or Route= 'I') AND __ID=?";"";"";IO 2::__ID)

      'IO 2' & 'FeedsUsed 2' are the portals showing the fields on the Patient layout.

      As mentioned the primary relationships between tables are: Patient < Calc < IO < FeedsUsed.

      Fields from the latter 3 tables are shown in portals with TOs 'IO 2' and 'FeedsUsed 2' being used to call values in the correct context. When an 'upstream' record is activated, it sets the downstream field using a Patient::upstream_n = Downstream::_upstreamFKID relationship.

      I've made sure the calc field in the IO parent table is wide enough; it definitely works fine if I don't try to introduce the relationship using 'AND'.

        • 1. Re: Execute SQL: Summing
          philmodjunk

          SQL doesn't like fields that start with an underscore. They need to be enclosed in double quotes.

          WHERE (Route='G' or Route= 'I') AND \"__ID\"=?"

          • 2. Re: Execute SQL: Summing
            tays01s

            Spot on! Thank you.

            2 queries:

            a. Albeit when I have better SQL understanding, but is it generally better to use this rather than TOs to achieve desired relationships, just keeping the primary relationships?

            b. If I need several or nested ANDs / ORs following a WHERE, i) does SQL follow normal mathematical hierarchy and ii) if there say 2 ANDs would you write them like this: ExecuteSQL("SELECT etc. ......WHERE field1='K' AND fieldX=? AND fieldY=?";"";"";Table2::fieldX;Table3::fieldY)

             

            • 3. Re: Execute SQL: Summing
              philmodjunk

              a) ExecuteSQL cannot replace all the relationships in Manage | Database, there are database functions that depend on those relationships that cannot be implemented without them, but you can use ExecuteSQL to greatly reduce the complexity of that relationship chart. I have mixed opinions as to whether that is a good thing or not. True, reducing the complexity of the relationship graph is generally a good thing, but your executeSQL calculations "hide" relationship details in small scattered packets throughout your database and that isn't necessarily a good thing...

              b) yes and if you have constructed such multi-term Boolean expressions in FileMaker calculations before, you should have a good idea how to set them up. While the symbols used are slightly different, the order of operations is the same.