1 2 Previous Next 22 Replies Latest reply on Oct 11, 2016 3:58 PM by beverly

    Omit item in ExecuteSQL statment

    MikeWile

      I have a field with the calculation below. What I'd like to do is omit an item when ISBN_JBP FROM tbl_CisPub_ProductMaster_cache = origisbn. Right now, it's included in the list. I've tried adding an additional SELECT statement in parentheses after the ? but it showed an error. Any suggestions? Thanks.

       

      Let ([pgrp=tbl_CisPub_ProductMaster_cache::PGROUP;origisbn=ISBN13]

      ;

      ExecuteSQL ( "SELECT \"ISBN13_JBP\" FROM \"tbl_CisPub_ProductMaster_cache\" WHERE PGROUP=?"; "" ; "";pgrp ))

        • 1. Re: Omit item in ExecuteSQL statment
          fmpdude

          To omit an item from the list, can't you just add an "AND" piece to your WHERE clause like this:

           

          ExecuteSQL ( "SELECT \"ISBN13_JBP\" FROM \"tbl_CisPub_ProductMaster_cache\" WHERE PGROUP=? AND ISBN_JBP FROM tbl_CisPub_ProductMaster_cache <> origisbn"; "" ; "";pgrp ))


          So, if you want to omit an item when they're equal, just have the AND be not equal and you'll only get those records.

           

          Not exactly sure from your posting if this is what you meant.

           

          HOPE THIS HELPS.

          • 2. Re: Omit item in ExecuteSQL statment
            MikeWile

            Thanks fmpdude,

             

            I had tried something similar to what you have but I couldn't figure out how to say not equal. I was trying NOT and NOT IN. <> makes more sense. but it still isn't working.

             

            Before (I want to remove the second circled item):

            Before.png

            After this code:

             

            Let ([pgrp=tbl_CisPub_ProductMaster_cache::PGROUP;origisbn=ISBN13]

            ;

            ExecuteSQL ( "SELECT \"ISBN13_JBP\" FROM \"tbl_CisPub_ProductMaster_cache\" WHERE PGROUP=? AND \"ISBN13_JBP\" FROM \"tbl_CisPub_ProductMaster_cache\" <> origisbn"; "" ; "";pgrp ))

             

            I get:

            after.png

            • 3. Re: Omit item in ExecuteSQL statment
              coherentkris

              if the contents of origisbn is a list then consider:

               

              ExecuteSQL ( "SELECT \"ISBN13_JBP\" FROM \"tbl_CisPub_ProductMaster_cache\" WHERE PGROUP=? AND WHERE ISBN_JBP NOT IN ( origisbn )"; "" ; "";pgrp ))

               

              This should work IF orgisbn is formatted as csv.

              If orgisbn is a single value then the <> previously mentioned should work.

              Here is an example of NOT IN with a sub query in case orgisbn is a list and you want to let SQL handle the conversion from FM list to SQL list.

              Subqueries with NOT IN

               

              The sub query should be altered to get the appropriate contents of orgisbn with sql.

              • 4. Re: Omit item in ExecuteSQL statment
                MikeWile

                origisbn is a field (the first circled item in the first screen shot above). I tried your suggestion but still get an error.

                • 5. Re: Omit item in ExecuteSQL statment
                  coherentkris

                  so orgisbn is single value then this should work

                   

                  ExecuteSQL ( "SELECT \"ISBN13_JBP\" FROM \"tbl_CisPub_ProductMaster_cache\" WHERE PGROUP=? AND \"ISBN13_JBP\" <> origisbn"; "" ; "";pgrp ))

                   

                  if you are going to dive into using sql with fm then it is invaluable to understand the SQL order of operations

                  SQL Query Order of Operations

                   

                  and understand FM's implementation of SQL.

                  https://fmhelp.filemaker.com/docs/15/en/fm15_sql_reference.pdf

                  The Missing FM 12 ExecuteSQL Reference | FileMakerHacks

                  • 6. Re: Omit item in ExecuteSQL statment
                    fmpdude

                    Could you post an image of your data model? It's hard to craft SQL without (me) understanding exactly which data in which tables,etc.?

                     

                    -----

                     

                    In any case, I would SERIOUSLY recommend that you use an external tool to craft your SQL so you get actual error messages (not the brain-dead "?" nothingness you get from FMP's "Data Viewer"), coding assist for the query (imagine that in 2016) like other tools, and full screen views of your data.

                     

                    RazorSQL is a good tool for that. I use a Java IDE. RazorSQL will work with any JDBC-compliant (read: ALMOST ALL OF THEM) database.

                     

                    In any case, these external tools connect to your LIVE FMP database and in my experience cut the time to craft SQL by 90%. Until FileMaker, a multi-hundred dollar "database tool", gives us actual SQL error messages, coding assist, and the ability to see all your data (you know, as in "Data Viewer"), IMHO, you're better off using another tool.

                     

                    Once you get your SQL working, you can then move it to FMP, add replaceable parameters and refine.

                     

                    I don't have time for "?" or remembering that there is some "cool way" I can sort of get error messages in FMP (while still not doing any of the other stuff tools out there already have, and have had, for many, many years).

                     

                    HOPE THIS HELPS.

                    • 7. Re: Omit item in ExecuteSQL statment
                      MikeWile

                      Thanks coherentkris. I still get an error.

                      • 8. Re: Omit item in ExecuteSQL statment
                        MikeWile

                        Hi fmpdude,

                         

                        Table ASSESS_CORR contains an field ASSESS_CORR::ISBN13. tbl_CisPub_ProductMaster_cache is an external FileMaker file that contains fields for ISBN13_JBP and PGROUP. There are multiple ISBNs in the external table that have the same PGROUP. I want to show all the ISBNs from the external table that are associated with the ISBN in ASSESS_CORR and share the same PGROUP but I don't want the one from ASSESS_CORR. Hope this explains it more.

                         

                         

                        Screen Shot 2016-10-11 at 7.30.07 AM.png

                        • 9. Re: Omit item in ExecuteSQL statment
                          fmpdude

                          This is hard to do without the seeing and playing with the actual data, but something like this might work better...

                           

                          SELECT  tbl_CisPub_ProductMaster_cacheWHERE PGROUP =  ?

                          FROM

                          ACCESS_CORR A, tbl_CisPub_ProductMaster T

                          WHERE

                          A.isbn13=T.isbn13

                          AND

                          tbl_CisPub_ProductMaster_cache <> ?

                           

                          (Another reason to use an external SQL tool is that you just write "SQL", not worry about ExecuteSQL syntax until ready. In the external tool use actual values in the WHERE clause to make sure you're getting the results you want. Then use ? and get your FMP query working.)

                           

                          HOPE THIS HELPS.

                          • 10. Re: Omit item in ExecuteSQL statment
                            beverly

                            I think you are close.

                             

                            ExecuteSQL (

                            " SELECT \"ISBN13_JBP\"

                            FROM \"tbl_CisPub_ProductMaster_cache\"

                            WHERE PGROUP=?

                                 AND ISBN_JBP <> ? "

                                 ; "" ; ""

                                 ; tbl_CisPub_ProductMaster_cache::pgrp

                                    ; tbl_CisPub_ProductMaster_cache::origisbn

                                 )

                             

                            if the field searched & the values passed are in the same table (as FROM).

                            • 11. Re: Omit item in ExecuteSQL statment
                              MikeWile

                              Hi Beverly,

                               

                              This is what I tried:

                               

                              ExecuteSQL (

                              " SELECT \"ISBN13_JBP\"

                              FROM \"tbl_CisPub_ProductMaster_cache\"

                              WHERE PGROUP=?

                                   AND ISBN13 <> ? " <---this is the isbn field from the layout that I want to exclude

                                   ; "" ; ""

                                   ; tbl_CisPub_ProductMaster_cache::PGROUP  <----this is the external pgroup

                                      ; tbl_CisPub_ProductMaster_cache::ISBN13_JBP  <---this is the external isbn

                                   )

                               

                              I agree that it's close but still no cigar.

                              • 12. Re: Omit item in ExecuteSQL statment
                                MikeWile

                                Hi Beverly,

                                 

                                By the way if I take out the AND part and its associated ?, it runs but includes the ISBN

                                 

                                ExecuteSQL (

                                " SELECT \"ISBN13_JBP\"

                                FROM \"tbl_CisPub_ProductMaster_cache\"

                                WHERE PGROUP=?"

                                     ; "" ; ""

                                     ; tbl_CisPub_ProductMaster_cache::PGROUP  <----this is the external pgroup

                                     )

                                • 13. Re: Omit item in ExecuteSQL statment
                                  OCDilla

                                  I would double check that how ISBN13 is stored in both databases. ISBN-13 has a legacy of being stored as text in some databases (blame ISBN-10) if you have it stored as a number in one and text in the other you can get the notorious "?". Also, try looking at the calculated SQL statement.

                                  • 14. Re: Omit item in ExecuteSQL statment
                                    beverly

                                    It might be your context. ExecuteSQL does not care what relationships you have set up, so perhaps your 'cache' table may not be the correct location?

                                     

                                    As I look at your screenshots, it appears you want the related, but not the 'self'. And that is where I thought perhaps you are calling the wrong context. and/or the "origisbn" is the incorrect value to pass.

                                     

                                    Where is the calculation being called? In "ASSESS_CORR"?

                                     

                                    beverly

                                    1 2 Previous Next