User lookup - UsrItem relationship

Hi all,

What are we trying to do?

We need to know how to limit a contact user search on a custom field to where the userItem relationship is NOT discontinued and exclude those that are.

The background

Our objective

We are trying to clear down legacy user-item relationship data in our system and avoiding a hard delete where possible.

How we are currently set up

We hold our cost centres as CIs and create relationships between these items and our approver’s contact users records. our forms have two fields working in tandem, which cost centre they want to use and which approver to direct the approval to.

The lookup filters on the budget holder custom field is…

select usr.id from usr as usr inner join usr.userItems as usrItems where usrItems.item.shortCode=(@*@$new.W('COST_CENTRE').shortCode@*@) and usr.shortCode <> @*@$new.affectedUser.shortCode@*@

Note, this also prevents the affected user from approving their own spend.

Why is this not working for us?

Because the contact user can still be active but no longer an approver against a cost centre. We are finding that the user is still presented in the select search results even after the relationship has been discontinued.

I have looked at a similar thread that has been answered and decided that it does not quite match our use case.