0

Records filtered by Dynamic Multiple Choice

So, I have many fields that are dynamic "single" choice.  My goal is to filter data using a multiple choice dynamic field but am having issues with scripting it correctly. 

 

For instance, I want to filter data based on Lead Source and Lead Student Type.  In my these fields are dynamic choice.  Now on a separate table, I want to be able to filter that data using a dynamic multiple choice using the same table reference as my dynamic choice.  I can filter by only selecting one from each, but I can't filter by selecting multiple values.  Any help is greatly appreciated.  A sample code of what I am currently using is below:

let curRec := this;
let xStart := 'Start Date';
let xEnd := 'End Date';
let xSource := 'Lead Source';
let xType := 'Lead Student Type';
count(select Leads
        where Status = 26 and 'Lead Entered Date' >= xStart and 'Lead Entered Date' <= xEnd and
            'Lead Source' = xSource and
        'Lead Student Type' = xType)

4 replies

null
    • Fred
    • 9 mths ago
    • Reported - view

    Once you start working with multiple choice fields (regular or dynamic) then you can't use the equal sign so easily.

    Luckily now you can use the contains() function to filter on arrays, so maybe try:

    let curRec := this;
let xStart := 'Start Date';
let xEnd := 'End Date';
let xSource := 'Lead Source';
let xType := 'Lead Student Type';
count(select Leads
        where Status = 26 and 'Lead Entered Date' >= xStart and 'Lead Entered Date' <= xEnd and
            contains(xSource, 'Lead Source') and contains(xType,'Lead Student Type')

    You might need to wrap all the fields in numbers(), for multi choice fields, or number(), for single choice fields.

      • Dave_Irving
      • 9 mths ago
      • Reported - view

      Tried that, while also adding the numbers function too.  Can't seem to get it correct.  Without numbers, the following errors are: Function is not defined contains(dchoice, dmulti) for both lines 8 and 9. 

    • Dave_Irving
    • 9 mths ago
    • Reported - view

    Oh my, I feel like an idiot.  I didn't think the order of arrangement mattered, but it does.  I had my reference field first instead of my filtered field.  Wow, such a simple solution. 

      • Fred
      • 9 mths ago
      • Reported - view

      I get confused by contains() as well. How I remember it is the large pool that you want to look in is first and the single item you want to look for is second.

      When you get a chance, please mark it answered if you are satisfied with the results.

Login to reply

Content aside

  • Status Answered
  • 9 mths agoLast active
  • 4Replies
  • 86Views
  • 2 Following