0

Select records through Dynamic Multiple Choice Field

Hello,

I've been looking for a solution for a couple of days but I can't find it even if it's a simple thing.

I have a TabCategories table.

I have another table, TabCustomers, where there are, among others,
- a 'Category' Dynamic Multiple Choice Field that takes data from TabCategories.
- a 'Sales' field indicating the customer turnover

Finally I have a TabSearch table where it is present
- a 'Category' Dynamic Choice Field that takes data from TabCategories.

In this last table, TabSearch, I would like to insert a formula that takes the largest customer turnover from the TabCustomers.Sales field between TabCustomers records that have the TabSearch.Category field among the TabCustomers.Category arrays field.

I tried with the following formula

let t := this.Category;
let a := select TabCustomers[Category = t];
let r := max(a);
r

Unfortunately, however, it does not work because TabCostumers.Category is a multiple selection.

I hope I have been clear. Thanks

3 replies

null
    • Fred
    • 6 mths ago
    • Reported - view
     said:
    In this last table, TabSearch, I would like to insert a formula that takes the largest customer turnover from the TabCustomers.Sales field between TabCustomers records that have the TabSearch.Category field among the TabCustomers.Category arrays field.

    I can't help you with finding the "largest customer turnover" because you don't give us enough info about your structure to figure it out. Your code only returns records. Is there a field you need to sum or do you need to count?

    I can help with comparing dynamic choice fields. To compare two dynamic multiple choice fields you have to use a bit of knowledge passed along by in this post.

    let t := Category;
let a := (select TabCustomers)[var suba := numbers(Category);
        count(suba[var suba1 := this;
                    count(numbers(t)[= suba1]) > 0]) > 0];
concat(a)

    You should see an array of record Ids where the selection(s) in the field Category in TabSearch are found in the records that match selection(s) in Category in TabCustomers.

    • Web_Namer
    • 6 mths ago
    • Reported - view

    Thaks

    You're right, here's the feature I applied:

    let t := Category;
let a := (select TabCustomers)[Category = t].Sales;
let r := max(a);
r

    I am attaching three screenshots of the three respective tables.

    The function I apply in the TabSearch.Fx field considers only Gamma and Iota clients and returns 300. It does not consider the Alfa customer because it belongs to multiple categories.

    I would like to find a function that also considers those customers who belong to multiple categories and, among these, there is the category chosen in TabSearch.

     

    For the sake of completeness, I am attaching the sample Ninox file.

     

    Thank you!

    XP.ninox
    • Fred
    • 6 mths ago
    • Reported - view
     said:
    The function I apply in the TabSearch.Fx field considers only Gamma and Iota clients and returns 300. It does not consider the Alfa customer because it belongs to multiple categories.

    Ninox is doing exactly what you asked it to do. TabCustomers.Category is a dynamic multi choice field. TabSearch.Category is a dynamic choice field. Since a multichoice field can have more than one choice an array is created by Ninox of the choices. So the only time a multichoice will equal a simple choice is when there is only 1 choice selected.

    To force Ninox to take the simple choice and compare it to all selections in a multichoice you have to add a bit of code:

    let t := number(Category);
let a := (select TabCustomers)[var suba := numbers(Category);
        count(suba[= t]) > 0].Sales;
max(a)

    Line 2 is where we create a variable that stores an array of all of the choices in each record in TabCustomers.Category. Then we take that new variable array and compare it to the choice selected in TabSearch.Category. Then we count the number of records return and if it is more than 0 then we keep that record.

    If you create a new formula field and put this in TabSearch:

    let t := number(Category);
let a := (select TabCustomers)[var suba := numbers(Category);
        count(suba[= t]) > 0];
concat(a)

    You will see the record Ids change as you make your selection.

Login to reply

Content aside

  • 6 mths agoLast active
  • 3Replies
  • 189Views
  • 2 Following