How to filter contact lookup to show administrators only.

Dear Mohamed,

You have chosen a correct article to implement such functionality. You need to apply filtration for the Contact lookup field. The filter for filter group should be referring SysAdminUnit table and check whether contact has system administrator role. You can evaluate by system administrator role Id. 

Regrads,

Anastasia

Anastasia Botezat writes:

You need to apply filtration for the Contact lookup field. The filter for filter group should be referring SysAdminUnit table and check whether contact has system administrator role. You can evaluate by system administrator role Id. 

That's exactly what I did. But the SysAdminUnit table doesn't have RoleId column.

I looked into SysUserInRole table that contains SysRoleId and SysUserId columns, but I couldn't use it in the filter group.

Do you have any idea how to implement it?

Thank you

Mohamed

Mohamed,

Thank you for the explanation, now I see what you are referring to. The table, which contains the pair contact - role is SysAdminUnitInRole. There are stored pairs SysAdminUnitId and SysAdminUnitRoleId. Both are Ids from SysAdminUnit table.

Regards,

Anastasia

Anastasia Botezat,

Yes. I have to join the Contact, SysAdminUnit, and SysAdminUnitInRole to get the list of Administrators.

In SQL it looks like this:

select * from Contact 
left join SysAdminUnit on Contact.Id = SysAdminUnit.ContactId
left join SysAdminUnitInRole on SysAdminUnit.Id = SysAdminUnitInRole.SysAdminUnitId
where SysAdminUnitRoleId = 
'83A43EBC-F36B-1410-298D-001E8C82BCAD' 
-- sys administrators role id

 How can I use it in filters?

Thank you

Mohamed

Mohamed Ouederni,

Please check the following article on how to build path to the columns:

https://academy.bpmonline.com/documents/technic-sdk/7-14/root-schema-building-paths-columns

The path should be somewhat like this, and be evaluated to the role Id.

Contact.[SysAdminUnit:Contact].[SysAdminUnitInRole:SysAdminUnit].Role

Please use the article and example to build needed path.

Anastasia

Anastasia Botezat,

Thank you. That helps a lot.

For anyone interested in the solution.

filterGroup.add("IsAdmin",
Terrasoft.createColumnFilterWithParameter(
	Terrasoft.ComparisonType.EQUAL,
	"[SysAdminUnit:Contact].[SysAdminUnitInRole:SysAdminUnit].SysAdminUnitRoleId",
	"83A43EBC-F36B-1410-298D-001E8C82BCAD"));