How to create an AdHoc Grid that Filters based on Current User

Example using Bill Details

In this example, the Bill Details grid will have an AdHoc Grid generated off of it, to display only Bill Details the current User is the Contact of.

Click here to view what the Query for Bill Details could look like

^{SELECT
`BD`.`RECID`,
`BD`.`TENANTS_RECID`,
`BD`.`COST`,
`BD`.`DESCRIPTION` AS `BD_DESCRIPTION`,
`BD`.`EXPENSE_OBJECT_CODE`,
`BD`.`REVENUE_OBJECT_CODE`,
`BD`.`BILLING_GROUPS_NAME`,
`BD`.`CHRG_BILLED_DATES`,
`B`.`TEST_BILL`,
`B`.`BILL_DATE`,
`BILLTYPE`.`VALUE` AS `BILLDETAIL_TYPE`,
`INV`.`INVOICE_NUMBER`
FROM `BILL_DETAILS` AS `BD`
INNER JOIN `BILLS` AS `B` ON BD.BILLS_RECID = B.RECID
INNER JOIN `LISTS` AS `BILLTYPE` ON BD.BILLDETAIL_TYPE_LISTS_RECID = BILLTYPE.RECID
LEFT JOIN `INVOICES` AS `INV` ON BD.INVOICES_RECID = INV.RECID
LEFT JOIN `CONTACTS` AS `C` ON BD.CONTACTS_RECID = C.RECID
WHERE (B.TEST_BILL IS NOT NULL)}

The important detail of the query that will be modified is the WHERE portion, where the grid is determining what should be displayed. Here is the additional filter needed:

SQL

AND (BD.CONTACTS_RECID = :USERS_CONTACTS_RECID)

This will filter the Bill Details based on who the Contact of the Billed Item is for. If the current User is the Contact, then the record will be displayed.

Here is a list of useful filters for USER identifiers:

USERS_RECID
- This variable contains the current User's RECID, a unique identifier for a User, generated internally within PCR-360.
USERS_USERID
- This variable contains the current User's USER ID, a unique identifier for a User determined by the System Administrators.
USERS_CONTACTS_RECID
- This variable contains the current User's Contact's RECID, a unique identifier for a User as defined as a Contact, generated internally within PCR-360.