0

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!

7 replies

null
    • Fred
    • 3 yrs ago
    • Reported - view

    Are you talking about the constraint part of a reference field or just using "constrain" as another word for filter.

    Can you post the code you are working with?

    • Fred
    • 3 yrs ago
    • Reported - view

    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.

    • Sam.1
    • 3 yrs ago
    • Reported - view

    Thanks Fred! I’ll give it a try and update here.

    🙂

    • Sam.1
    • 3 yrs ago
    • Reported - view

    Yes, in The Item table is a reference Item—>owners . I call it Owner. Since, the collection of customers Own the item, I called it that. It holds the list of customers owning the item and their % of ownership.

    • Sam.1
    • 3 yrs ago
    • Reported - view

    Any suggestions considering the owner

    membership is a reference? 
    The customer is chosen first. Then a reference to the items is used next. 
    All of the codes so far have shown all the items to  or none.

    • Fred
    • 3 yrs ago
    • Reported - view

    Well of course. Owners is. m:M between customers and items. Just drop the ownermembership reference. 

    if you are still having issues post your code. 

      • Sam.1
      • 3 yrs ago
      • Reported - view

      Fred thanks, will do

Login to reply

Content aside

  • 3 yrs agoLast active
  • 7Replies
  • 189Views
  • 2 Following