0

Using a Dynamic multiple choice field to filter a dynamic choice list

I'm searching the forum but there is no correct answer to my question (or maybe is nested in a very crowded forum).

I have a Dynamic multiple choice field (Country) with a list of countries. The user can select one or more countries. Then, there is a Dynamic choice list (Hotels) that needs to show ALL the hotels from the tHotels table that are located in the selected countries.

I'm using the "contains" method in the select (inside the Dynamic values option), like this:

let xContry := 'Country';
(select tHotels)[contains(text('tContry'), text(xCountry))]

 

But the problem is, it only shows the hotels that contains ALL the selected countries; so, this works when there is only one country selected or when the same Hotel Company has hotels in more than one country. But I want to show ALL the hotels in country A and ALL the hotels in country B, and ALL the hotels in country X, where A, B and X are the selected countries in the Dynamic multiple choice field Country.

 

Any clue or some ray of light?

9 replies

null
    • Fred
    • 1 yr ago
    • Reported - view

    Yeah, when you tell Ninox to check a contains of an array in another array it will not iterate through the choice array. It will just look to see if all of the numbers in the array appear in the first array.

    Just to be clear, tHotels.tContry is a multiple choice field as well?

    Is tContry also a dynamic multichoice field that points to the same table as Country?

    You can view this post to see two examples of dynamic multi choice.

      • Consultant and developer
      • Javier
      • 1 yr ago
      • Reported - view

       That's it! the second database example contains the solution!

    • Alain_Fontaine
    • 1 yr ago
    • Reported - view

    In this formula:

    let c := numbers(Country);
    select tHotels where cnt(numbers(tCountry)[contains(c, this)]) > 0
    

    I supposed that the table “tHotels” contains a DMC field “tCountry” indicating in which countries the hotel is present. 

    • Consultant and developer
    • Javier
    • 1 yr ago
    • Reported - view

    Thanks for your reply. 

    Sorry for the poor feedback. Here there is more about the data fields and tables:

    1. Country: Dynamic multiple choice field. It shows a list of ALL the countries from the tCountry table. Its a popup that allows you to select one or more countries.
    2. tCountry: Table containing ALL the countries.
    3. Hotels: Dynamic choice list. It shows a list of values from the the table tHotels.
    4. tHotels: Table containing ALL the hotels info. It has a field, Country, that I want to use for filtering the Dynamic choice list.

    My goal:

    1. Select one or more values (countries) from the Country field.
    2. Select one Hotel from the Hotels field. This list will show ONLY the hotels from the table tHotels whose country is one of the values selected in step 1.

    Example:

    1. If Country value = "Spain", then the Hotels list will show only Hotels from the tHotels table whose country = Spain
    2. If Country value = "Spain", "UK", the Hotels list will show only Hotels from the tHotels table whose country = "Spain" AND Hotels from the tHotels table whose country = "UK"
      • Fred
      • 1 yr ago
      • Reported - view

      Are you creating these dMC fields in a third table, most likely a dashboard?

      Is country in tHotels a dC field?

      • Consultant and developer
      • Javier
      • 1 yr ago
      • Reported - view

       yes to all. Finally, the formula I was using was "nearly" ok. I simply needed to exchange the arguments order, like this:

      Old (wrong) formula:

      (select tHotels)[contains(text('tContry'), text(xCountry))]

      New, working formula

      (select tHotels)[contains(text('xContry'), text(tCountry))]

      Thanks again for your help!

      • Fred
      • 1 yr ago
      • Reported - view

      Great to hear. Please remember to mark the post answered when you get a chance.

    • Alain_Fontaine
    • 1 yr ago
    • Reported - view

    While the formula works in most cases, it can lead to false positives in certain cases. For example, if the list of countries includes “Guinea” and “New Guinea”, and “New Guinea” is present among the selected countries, but not “Guinea”, hotels situated in both countries will be (incorrectly) displayed, because the string “New Guinea” does indeed contain the string “Guinea”.

    It is easy to avoid this issue by comparing the record numbers of the countries instead of their names. This has even become very easy since Ninox updated the “contains()” function to operate on arrays.

    let xContry := numbers(Country);
    (select tHotels)[contains(xContry, number(tCountry)]
    
      • Consultant and developer
      • Javier
      • 1 yr ago
      • Reported - view

       Absolutely great! Much better (and easier) than using simple text.

      Thank you very much!

Content aside

  • 1 yr agoLast active
  • 9Replies
  • 582Views
  • 4 Following