Request Form filter entitlements based on query from custom table

[shaktinayak]

We have a custom table created in the aveksa database.

the purpose is to use the table in the entitlement filter in request forms. but the query is not working for the custom table.

"x."Business Source Name"='ABC'
and x."Entitlement Name" in ('AC-SERVICES-CHECKER')"  ------------- This works fine

but

x."Business Source Name"='ABC'
and x."Entitlement Name" in (select role from custom_table where department = 'HR') ----- This doesn't work

and the entitlement field doesn't fetch any value.

custom_table contains 2 columns role and department.

select role from custom_table where department = 'HR' returns "AC-SERVICES-CHECKER" when run in db.

Any suggestions how to filter data based on a custom created table in AVUSER schema.

[MHelmy · Accepted solution]

I've seen this problem two time (not sure if they will fix your problem):

1. Your entitlement could have an alternative name (especially when using business descriptions). If the entitlement you are targeting has a different display name than its actual name, try using both and see if one of them works.

2. Sometimes specific words in advanced filters SQL statements get changed to something else after you save. Try giving the inner statement table an alias as follows:

x."Business Source Name"='ABC'
and x."Entitlement Name" in (select ct.role from custom_table ct where ct.department = 'HR')

3. Also which version/patch are you on? Might be a defect fixed in some later version.