1 2 Previous Next 22 Replies Latest reply on May 8, 2017 8:31 AM by philmodjunk

    ExecuteSQL

    MAKALENG

      with this relationship Claims::Reffering Doctor = DOCTORS::SURNAME i easily get through lookup practicenumber problem is that there are other fields like admitting, treating doctors etc that must be looked up with each requiring new relationship, so i tried execute sql and it returns ?

      i am missing something

       

      ExecuteSQL ( "select DOCTORS::PRACTICENUMBER FROM DOCTORS WHERE Reffering Doctor = DOCTORS::SURNAME " ; "" ; ""  ;"" )

        • 1. Re: ExecuteSQL
          Mike_Mitchell

          ExecuteSQL (

          "SELECT DOCTORS.PRACTICENUMBER FROM DOCTORS WHERE \"Reffering Doctor\" = ? ; "" ; "" ; DOCTORS::SURNAME

          )

           

          Two problems:

           

          1) The separator between table and field name in SQL is the period (.), not the double colon as in FileMaker.

           

          2) SQL doesn't directly support spaces in object names. You have to enclose them in quotes for them to parse properly.

           

          HTH

           

          Mike

          • 2. Re: ExecuteSQL
            wimdecorte

            MAKALENG wrote:

             

            with this relationship Claims::Reffering Doctor = DOCTORS::SURNAME i easily get through lookup practicenumber problem is that there are other fields like admitting, treating doctors etc that must be looked up with each requiring new relationship,

             

            You're probably solving the wrong issue with ExecuteSQL().  This quoted explanation shows the real problem.  I don't think I understand why a new relationship would be needed to look up different fields.  Can you explain this a bit more detailed.  Pretty sure that we can solve this at the root.

            • 3. Re: ExecuteSQL
              MAKALENG

              thanks @mike_Mitchell but it says text constant does not end with quatation mark,

              did i miss something

              • 4. Re: ExecuteSQL
                philmodjunk

                Sounds like you left out a ", a \ or used / in place of \. An extra " could also cause this error.

                • 5. Re: ExecuteSQL
                  MAKALENG

                  wimdecorte that relationship does not exist anymore was just to show how lookup was getting the value, now i delleted it (that relationship) to try execute sql

                  • 6. Re: ExecuteSQL
                    Mike_Mitchell

                    The question mark is a parameter. FileMaker will substitute the value of the field for the ?.

                    • 7. Re: ExecuteSQL
                      MAKALENG

                      sorry it was typo, it say quatation mark (does not end with quatation mark)

                      • 8. Re: ExecuteSQL
                        Mike_Mitchell

                        Yes, you are correct. It should read:

                         

                        ExecuteSQL (

                        "SELECT DOCTORS.PRACTICENUMBER FROM DOCTORS WHERE \"Reffering Doctor\" = ?" ; "" ; "" ; DOCTORS::SURNAME

                        )

                        • 9. Re: ExecuteSQL
                          wimdecorte

                          MAKALENG wrote:

                           

                          wimdecorte that relationship does not exist anymore was just to show how lookup was getting the value, now i delleted it (that relationship) to try execute sql

                           

                          It doesn't matter that it does not exist anymore, I'm more interested in your reasoning why you think multiple relationships are needed.  That can help us establish where you are and help you build an efficient solution.

                           

                          So if you can explain in words why you think it would require multiple relationships then we can discover together if you are on the right path.

                          • 10. Re: ExecuteSQL
                            MAKALENG

                            wimdecorte claims table and doctors table . if i need practice number of Dr A from doctors table , i'll need Claims::Refferring Doctor = DOCTORS::SURNAME that way i ll get the practice number, the problem is that from that table on same record i have a field for admitting doctor with different practice number and assisting dr with practice number which i thought will require new relationship, unless if i am wrong or my data is poorly structured

                            • 11. Re: ExecuteSQL
                              MAKALENG

                              might be missing something still giving me question mark, it initially said field is from unrellated table when i related them same thing ?

                              • 12. Re: ExecuteSQL
                                Mike_Mitchell

                                Are you trying to execute a lookup, or an auto-enter calculation?

                                 

                                (Hint: A lookup will not work.)

                                • 13. Re: ExecuteSQL
                                  MAKALENG

                                  is a calculation field that returns answer as text

                                  • 14. Re: ExecuteSQL
                                    Mike_Mitchell

                                    Is the calculation field unstored? (Check "Do not store calculation results".)

                                     

                                    BTW - This is a really bad idea from a performance standpoint. Because you can't control the commit state of the record, using ExecuteSQL in an unstored calculation will cause severe delays in certain cases. (Props to Wim for this discovery.)

                                     

                                    I suggest you go back to what you were doing before: Use a lookup. It will be infinitely easier to manage and won't kill performance.

                                    1 of 1 people found this helpful
                                    1 2 Previous Next