5 Replies Latest reply on Dec 20, 2015 6:35 PM by beretta819

    Match Account Name to UserID

    beretta819

      Hello All...
      I have two related tables, AUDIT and USER. On the AUDIT table I have a userFK field that I need to have populated with the USERID number, from the USER table, that matches the person who logged in.

       

      I am using Get(AccountName) to identify the user and then I am setting a variable to that name ($$username). So I am trying to match $$username with USERNAME from the USER table and return and populate userFK with the ID #.

       

      Someone told me SQL is the easiest way, (which I have no experience). I changed the userFK field to a stored calculation field, and got this far:

       

      ExecuteSQL (

      "SELECT UserID

      FROM users

      WHERE UserName = $$username"; "" ; "" )

       

      The result in the userFK field is only a "?".

       

      Any help or direction is greatly appreciated.

        • 1. Re: Match Account Name to UserID
          BillisSaved

          Good afternoon beretta819,

           

          I hope your day is going well. Try using the following format for your ExecuteSQL statement:


          ExecuteSQL (

          "SELECT \"UserID\"

          FROM \"users\"

          WHERE \"UserName\"=?";

          ""; ""; $$username)

           

          Hopefully this helps. Have a great day!

           

          God bless,

          Bill

          • 2. Re: Match Account Name to UserID
            Extensitech

            You could calculate a field to be equal to $$username, and use that as a field on the "left" or "local" side of a relationship. You'll need to leave it unstored, though.

             

            Chris Cain

            Extensitech

            • 3. Re: Match Account Name to UserID
              wimdecorte

              beretta819 wrote:

               

              The result in the userFK field is only a "?".

               

               

              The "?" always means that there is a syntax error in your SQL statement.  Bill has given you a fix for that.

               

              Note that in these SQL statements you tend to hard code TO and field names.  There are ways around that and make the SQL statement safe for any changes you will make to the schema in the future.

              • 4. Re: Match Account Name to UserID
                dtcgnet

                The way to do this using "strictly" FM ways:

                 

                Set $$Username = Get ( AccountName )

                Go to a layout based on the USERS table.

                Enter Find Mode

                Set Field Username = $$Username

                Perform Find

                If ( Get ( FoundCount ) = 1)

                Set Field USERS::userFK = $$Username

                Else

                Show Custom Dialog: "The username " & $$Username & " is not in the Users table. Notify someone."

                Go to Layout for users that don't exist or something like that.

                Endif

                 

                This assumes that userFK is a global field.

                 

                The ExecuteSQL method is wonderfully handy and very powerful. You should add some error checking with either method (in the above, the example makes sure that the proper username does exist in the users table and warns the user when it doesn't.)

                • 5. Re: Match Account Name to UserID
                  beretta819

                  Perfect!! Thank you and God bless you.