1 2 Previous Next 18 Replies Latest reply on Aug 2, 2016 6:06 AM by dtcgnet

    ExecuteSQL in Set Variable issue

    dtcgnet

      I have a statement in my startup script that sets a global variable called $$AccountID.

       

      The formula is:

       

      Let (

      [ UserAcctName = $$UserAccountName ] ;

       

       

      ExecuteSQL

        ("SELECT XP_ID FROM Personnel P

      WHERE P.SoftwareUserName = ?" ; "" ; "" ; UserAcctName )

      )

       

      This step works perfectly well when I access my solution via:

      1) FileMaker Pro

      2) FileMaker Go on an iPhone

      3) FileMaker Go on an iPad

      4) WebDirect in Safari

      5) WebDirect in Google Chrome

       

      BUT...it does not work at all when I access my solution through Safari using WebDirect on my iPad or iPhone.

       

      It works fine in WebDirect on a computer. It does not work in WebDirect on my iPad.

       

      Any suggestions?

        • 1. Re: ExecuteSQL in Set Variable issue
          user19752

          Have you confirmed the $$UserAccountName is set correctly?

          • 2. Re: ExecuteSQL in Set Variable issue
            carlosilvia0

            Hi,

            are you sure that $$UserAccountName is equal to Get(AccountName)?

            View https://www.filemaker.com/help/15/fmp/en/index.html#page/FMP_Help/get-accountname.html

            • 3. Re: ExecuteSQL in Set Variable issue
              coherentkris

              what are the contents of $$AccountName when run from WD on IPad?

              Does it return Empty String ?

              Question Mark ?

              Some other wrong value?

              Does it return more than one value?

              If it finds more than one match it will return them all.

              Perhaps...

              Let (

              [ UserAcctName = $$UserAccountName ] ;

               

               

              ExecuteSQL

                ("SELECT DISTINCT ( XP_ID ) FROM Personnel P

              WHERE P.SoftwareUserName = ?" ; "" ; "" ; UserAcctName )

              )

              • 4. Re: ExecuteSQL in Set Variable issue
                dtcgnet

                Note this:

                 

                It works perfectly on:

                FileMaker Pro

                FileMaker Go on iPad

                FileMaker Go on iPhone

                WebDirect on my laptop using Safari

                WebDirect on my laptop using Google Chrome

                 

                It does NOT work in Safari on iPhone or iPad. It fails to evaluate.

                 

                I've done this: I've added the merge field $$UserAccountName to the layout. I log in via FMP. It shows my name. I log in at the same time on my laptop using Safari. It shows my name. Same with the other avenues.

                 

                Additional details. The user initially logs in with generic credentials. The privilege set is set with low privileges. When an authorized user clicks a button, the Relogin step runs. If the user is recognized, he/she is logged in with their own FileMaker Pro accounts. If not, they get another try and then they're kicked out.

                 

                So...the only problem is in Safari on iOS. FM Go on the exact same iOS systems works properly.

                • 5. Re: ExecuteSQL in Set Variable issue
                  dtcgnet

                  Additional note: I'm using FMP 15, FMP Go 15, FMS 15, Safari 9.1.1 for Mac, Google Chrome Version 51.0.

                  • 6. Re: ExecuteSQL in Set Variable issue
                    dtcgnet

                    I added a Show Custom Dialog step.

                     

                    When run in WD in Safari on iPad (updated earlier today so it's up-to-date), I get:

                     

                    $$AccountFullName is

                     

                     

                    That's all it says. I also had earlier added a Commit Records step right after the login in order to try to make sure that the server had been communicated with.

                     

                    One other detail. Right before the script step that is supposed to perform the ExecuteSQL, I set a variable equal to the AccountPrivilegeSetName. It is (as it should be) "[Full Access]".

                     

                    Everything runs as it should everywhere else. I'm at a loss.

                    • 7. Re: ExecuteSQL in Set Variable issue
                      philmodjunk

                      I suggest sharing how you assign the account name to the global variable.

                      • 8. Re: ExecuteSQL in Set Variable issue
                        dtcgnet

                        Update...I suspect this may be a bug, but I'd like someone to help verify it please.

                         

                        On my regular browsers, the login window doesn't automatically capitalize the username or password fields that are displayed. However, on (my) iOS, those fields DO auto-capitalize the Username field (but not the password field), and I'd been just not worrying since usernames aren't case sensitive for FMP authentication: djohnson with a proper password will get me in just like DjOhNsOn with a proper password will.

                         

                        I expected that Get ( AccountName ) would return the Account Name as it appears in the Security section of the database. It doesn't. It returns exactly what is entered, and ExecuteSQL is case sensitive.

                         

                        So...that's behavior I didn't really expect. If I login to the account known as "djohnson", I think Get ( AccountName ) should return "djohnson". I also did this. I added {{AccountName}} to the layout. When I login as Djohnson, that's what is shown. When I login as DjOhNsOn, that's what is shown by {{AccountName}}.

                        • 9. Re: ExecuteSQL in Set Variable issue
                          dtcgnet

                          I set the variables as:

                           

                          Set Variable [ $$UserAccountName ; Value: Get ( AccountName ) ]

                          Set Variable [ $$AccountFullName ; Value:

                          Let (

                               [ UserAcctName = $$UserAccountName ] ;

                           

                                ExecuteSQL ("SELECT XP_ID FROM Personnel P WHERE P.SoftwareUserName = ?" ; "" ; "" ; UserAcctName )

                          )

                          ]

                           

                          I guess I need to just make sure to wrap that with "Lower()", as in Lower ( Get ( AccountName ) ). Then it'll work properly.

                          • 10. Re: ExecuteSQL in Set Variable issue
                            planteg

                            Hi dtcgnet,

                             

                            no bug here, Get (AccountName) returns exactly what the user types, unfortunately . See Auto-enter: Create = Account Name, odd results

                            2 of 2 people found this helpful
                            • 11. Re: ExecuteSQL in Set Variable issue
                              dtcgnet

                              Thanks, planteg. I hadn't seen that, but it does look like that's exactly what happened in this case, too. Get ( AccountName ) returns ExAcTlY what the user types at the time they login.

                               

                              I've fixed the script by wrapping it in Lower ( ), and it works fine now.

                               

                              I do not like that behavior much. Seems like something I can't imagine anyone would actually WANT.

                              • 12. Re: ExecuteSQL in Set Variable issue
                                user19752

                                Interesting.

                                 

                                I guess when authenticated externally , FM can't get how the account is registered. And it is same on using non-full access internal account. User can know only that he is authenticated.

                                • 13. Re: ExecuteSQL in Set Variable issue
                                  beverly

                                  You said:

                                  It returns exactly what is entered, and ExecuteSQL is case sensitive.

                                   

                                  ONLY in the criteria searches. It is not like FileMaker 'native' which finds "Joe Brown" or "joe brown".

                                   

                                  here you say:

                                  I do not like that behavior much. Seems like something I can't imagine anyone would actually WANT.

                                   

                                  It's SQL and "=" means equal in case as well. There is a "LIKE" and using "%" wildcards, but that can be slower. And you may still need the case!

                                   

                                   

                                  And remember that ExecuteSQL() is not fully like MySQL or MS SQL or Oracle, so using some of the functions of those may not help.

                                   

                                  Wrapping in Lower() or Upper() are correct.

                                   

                                  beverly

                                  1 of 1 people found this helpful
                                  • 14. Re: ExecuteSQL in Set Variable issue
                                    bigtom

                                    Decide what you want to use, upper or lower. I use Lower() on the FM data and LOWER in the SQL query and this works to eliminate the case issues.

                                    1 of 1 people found this helpful
                                    1 2 Previous Next