0

Applying filters on a lookup table by choosen field

Hi! I have a problem applying filters on a lookup table.

I have a customer table with a multiple-select Services field that allows me to understand which services customers have subscribed to.

choose

Then for each service table want see only the customers who have subscribed to that service but I can't set constraint formula to apply the filter.

selected services

In other case i use this formula but it doesn't work in the case just explained.

chosen(b.Customers, "Pool")

How can I do?

Thanks for your invaluable help.

Mauro

4replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • If I understand correctly what you are trying to do, you need to access the "Service" choice field of the currently open record in the "Services" table from the  search panel that opens when choosing a record to link to in the "Customers" table. I have never found a way to this, as well as from a formula column in a view element.

    If somebody knows a way to do this, I would be pleased to learn a new trick…

    Like
    • Fred
    • Fred
    • 11 mths ago
    • Reported - view

    Hi Mauro

     

    Your code is almost there. Just modify it a bit:

     

    text(b.Customers.Services) = a.Service

     

    From the looks of it the Service field in Services (a) is a simple choice field. I don't know what kind of field Services in Customers (b) is, so the above is if it is a choice field.

     

    If it is a Multiple Choice Field:

     

    contains(text(b.Services), a.Service)

     

    We have to change it to a contains because now a single record can have multiple services, so string of text is created that Ninox has to then match so we can't use an equal sign.

     

    I hope this helps.

    Like
  • Fred you are the best!

    Like
  • So, my understanding was not correct.

    The original way of solving the problem, that is, using the "chosen" function with two arguments, is indeed valid, and can be done in two different ways:

    chosen(b.Services, text(a.Service)) if the comparison is to be performed between the text values of both fields, or

    chosen(b.Services, number(a.Service)) if the comparison is to be performed between the indexes of both fields.

    Both approaches have their utlity in different use cases.

    Like
Like Follow
  • 11 mths agoLast active
  • 4Replies
  • 172Views