• If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Announcement

Collapse
No announcement yet.

Pulling current user in Valence

Collapse
X
 
  • 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?

    Thanks

  • #2
    Hi Andrew,

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

    Quick Example

    Code:
    SELECT
    cusno,
    cname
    FROM
    democmast
    WHERE
    (owner = current_user)
    ORDER BY
    cusno

    Comment


    • #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.

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


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

      Comment


      • #4
        Andrew,

        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

        Comment


        • #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.

          Comment


          • #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:

            Code:
            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

            Comment


            • #7
              Hi Andrew,

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

              Thanks

              Comment


              • #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.

                Comment


                • #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.

                  Comment


                  • #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.

                    Comment


                    • #11
                      robert.swanson , can you please give a quick example of how to use app variables to do this? Thanks.

                      Comment


                      • #12
                        You could try using vvIn_virtual() to pull in the universal app variable nabUser to derive the valid store for the user.

                        So your data source's SQL statement would look something like this:

                        Code:
                        SELECT *
                          FROM stores.table
                         WHERE storeid IN (
                           SELECT DISTINCT storeid
                             FROM employees.table employee
                                  JOIN employeesstoreid.table stores ON employees.id=stores.id
                            WHERE Upper(Trim(email)) = upper(vvIn_virtual('nabUser','xxx','char',10) )

                        Comment

                        Working...
                        X