2 Replies Latest reply on Jul 28, 2014 10:32 AM by TomHays

    Noob ExecuteSQL question using Get(AccountName)

    DDoughtie

      I'm just learning SQL and using the ExecutiveSQL function. The problem I'm running into is I need to get info from an "Accounts" table that contains some of the user's info so the variable can be passed to other scripts. There is an "accountName" field that contains the users account name. The problem I'm running into is the Get(AccountName) doesn't return the literal account name (setup under security etc) but the account name the user entered. FMP's account names are case insensitive. You can log in using all caps or all upper case or a mix. The function returns what the user entered at login, not what the account's name might be.

       

      So, when I use get(accountName) and I pass it into a the WHERE account_name ="get(accountName) I get nothing unless the user logs in using the exact user name in the table...... which never happens.

       

      So, how to I pass the WHERE accountname='get(accountName)' where the query is not case sensitive?

       

      (I was using the single quotes around the get(accountName) but that only works if the name is exact match. When I use no single quotes I get nothing but the question mark.


      Dan

      Augusta, GA

       

       

      (what I'm actually passing is WHERE accountname=' "&get(accountName)&" ' " I've added some spaces to show clarity)

        • 1. Re: Noob ExecuteSQL question using Get(AccountName)
          DDoughtie

          Update. I did a hack where I created a new field that forced the user name into lower case then did

           

          ExecuteSQL ( "SELECT salesRep FROM Accounts WHERE accountName_Lowercase='" & lower(Get ( AccountName ))&"'"  ;""  ; "").

           

          That works but I would still like to know if I can pass the WHERE= Something without it being case sensitive.


          Dan

          1 of 1 people found this helpful
          • 2. Re: Noob ExecuteSQL question using Get(AccountName)
            TomHays

            Daniel Doughtie wrote:

             

             

            ExecuteSQL ( "SELECT salesRep FROM Accounts WHERE accountName_Lowercase='" & lower(Get ( AccountName ))&"'"  ;""  ; "").

             

             

             

            You can go further and eliminate the extra lowercase field.

             

            ExecuteSQL ( "SELECT salesRep FROM Accounts WHERE lower(accountname)='" & lower(Get ( AccountName ))&"'"  ;""  ; "")

             

            -Tom

            1 of 1 people found this helpful