
Constraint for multiple customers
Hello Brain Trust,
I’ve worked at a constraint without success.
I have several tables in the database and the tables of concern are as listed: Customer
Owners
Item
A customer is an individual that fully or partially owns an item.
An owner is a group of customers that owns 100% of a given item. Many items are owned by several customers.
No customer has ownership of all the items.
Some items are owned by one customer.
How can a constraint be built considering the below example?
Some items are owned by customer 1, 2 and 3.
Other items are owned by customer 2 and 4.
How can a constraint be set up so that I can filter ( out customer 1,3 and 4) items owned in whole and in part by customer 2?
Results will display only items owned by customer 2.
thank All!
-
Since you are actually trying to use the constraints for a reference field, I've come up with:
let xCon := concat(b.Owners.OwnerMembership.Customers.Id); contains(xCon, text(a.Id))
The first line concatenates the list of Customers record Ids that belong to each record in Item through the relationship to Owners. You will see another table OwnerMembership in the middle because that was the only I know that 1 record in Owner can have multiple Customers and have possibly have the same customer in different Owner records.
Then the tricky part is I put the big list of Customers first then compare it to the single Id of the current record. I did it the other way cause usually you have the small list compare to the larger list, but that didn't work for me. Once I switched it it worked.