I thought of a way to allow Analysts to manage the contacts at their location, to do this I am trying to use a data partition that restricts Analysts from updating contacts that are not in the same site. However I'm not able to get the data partition to work. Is it possible to use dotted notation with the @root attributes?
For example, this is what I'm trying to do. Add a pre-update constraint on the ca_contact record with the constraint, location.site = @root.location.site. This condition will fail with the error AHD05800:Bad where clause. invalid constraint. Check data types of referenced attributes
However, this condition works fine, location = @root.location, but once I add .site to both location attributes the constraint fails.
If it is not possible to use dotted notation with the @root attributes then I would have to create multiple roles/data partitions for each region. With constraints similar to, location.site.name IN('America%').
Edit: Here's some additional context. The ideal solution would be to data partition the ca_contact table with a constraint on the site list because we have regional admins that are in charge of multiple locations.