Query variables
You can embed variables into your query to dynamically fetch the logged in user’s user name, linked contact, or a list of contacts or portfolios the logged in user can see with limited visibility or extended limited visibility. This allows you to easily build queries that aggregate data from contacts or portfolios the logged in user represents, for example for more extensive Dashboards.
List of variables
- $V(currentUserId), $V(currentUserLinkedContactId), $V(currentUserContactIds) and $V(currentUserPortfolioIds)
Allows you run the query against the information the system can dig out from the logged in user: who is the logged in user, which contact is the user linked to, and what contacts and portfolios the user can see based on representative / asset manager structures used for limited visibility and extended limited visibility.
"My customers" for logged in user:
SELECT name, contact_id FROM pm2_contact c WHERE c.id IN $V(currentUserContactIdsExtended) AND type = 1
Newest entries in user audit for logged in user:
SELECT * FROM AUDIT_USER WHERE user_name = $V(currentUserId) ORDER BY log_time DESC LIMIT 10
Open trade orders in portfolios the logged in user is allowed to see:
SELECT * FROM pm2_transaction WHERE pf_id IN $V(currentUserContactIdsExtended) AND status = 'ORDER' AND order_status = 4
Query variables throughout the application
Regardless on where you use your query from, the variable values get passed to your query based on the logged in user's information. This is applied in Dashboard and Front (Datagrid + Custom content).
On the Queries view you can "test" the variables against different users in the system through through "Run as" - select the user you want to run the query as, and the result is shown according to the user you select. By default, the field is filled in with your user name, but you can change it to run the query as any other user in the application.
