No announcement yet.

Pulling current user in Valence

  • Filter
  • Time
  • Show
Clear All
new posts

  • Pulling current user in Valence

    For an app in Valence, I need certain users to see different details on each app. Is this possible? For example,

    User: Michael
    Michael only needs info in a grid widget for stores where the store ID = 1

    User: James
    James only needs info in a grid widget for stores where the store ID = 2

    In the app, there are stores 1 & 2 in the data source. When either Michael or James is logged in, we only want them to see their stores (ID = 1 or 2).

    We have 12 reps who all need to see different stores (each store has an ID). Is there a field where we can pull the current user logged in to implement in SQL?


  • #2
    Hi Andrew,

    You can use current_user in your SQL statement that will have the current IBM i user logged in.

    Quick Example

    (owner = current_user)


    • #3
      Thanks for the response. I tried with current_user and it returns QSECOFR as the user when building the app and seeing the preview.

      SELECT *
      FROM stores.table
      WHERE storeid IN (
      SELECT DISTINCT storeid
      FROM employees.table employee
      JOIN employeesstoreid.table stores ON
      WHERE upper(trim(email)) = 'current_user' )

      When run under the user's log in, it does not work because no matching data is returned.


      • #4

        How ever you're logging in your Id must be mapped to QSECOFR so when the user logs in it would use the Id "IBM i User" they are mapped to. You can see this in the Portal Admin application from within the users section.

        So based on the SQL example you posted you're testing email equal to the current user id.

        For Example
        Screen Shot 2020-02-12 at 3.18.36 PM.png


        • #5
          In Andrew's environment the users are logging in using the Valence login method. That probably explains why QSECOFR is is being returned.

          Is there a way in Nitro App Builder to obtain the vvLoginID when the app launches without any back-end coding? I've tinkered with it but have yet to figure it out.


          • #6
            Are you cross-referencing your non-IBM i Valence users to QSECOFR? That seems a little risky...

            In any case, you should be able to get to the appropriate vvLoginID value inside a NAB data source by pulling in the "sid" and then linking to VVSESSDATA.

            For example, the following SQL-based data source would create a single-record temporary table called "CUR" that contains the vvLoginId value (CUR.VVLOGINID) for the current session, which you can then join to any other table(s) such as DEMOCMAST:

            WITH cur AS (SELECT char(vvloginid) AS vvloginid
                           FROM vvsessdata
                          WHERE vvsessid = vvin_char('sid'))
            SELECT cusno, cname, cur.vvloginid
              FROM democmast
                   CROSS JOIN cur


            • #7
              Hi Andrew,

              Just wanted to follow up and see if Rob’s suggestion worked for you.



              • #8
                I just tried creating a NAB data source using robert.swanson 's VVSESSDATA example above and I receive the following error:

                Invalid SQL Statement - zero columns derived

                I have the need to know the user running my apps so that I can, for example, limit the records retrieved to just the location they should see. I tried making a similar statement as above but got this same error, so I tried Robert's code verbatim and still get that error.

                Any guidance would be appreciated. Thanks.


                • #9
                  You may need to upgrade your Valence installation. The statement works fine in 6.1, 6.0 and 5.2 as far back as 5.2.20201209.0.

                  That said, for your case it sounds like you'd want to put the user constraint in your Where clause. That might be best achieved using app variables. But you'll definitely need to be on 6.0 or greater for that.
                  Last edited by robert.swanson; 01-03-2023, 06:34 PM.


                  • #10
                    Looks like we are on 5.2.20200819.0. So I guess this is too old for that code to work...?

                    We do have plans to move to 6.x, hopefully this month. I will pursue that. Thanks.