0

Conditional Formula Request

I need some assistance to create formulas that returns values based on two Choice Fields in my stats table.  The choice fields are 'Cohort Year' and 'Cohort Term'.  To explain this simply, let's take a field titled 'Enrolled'  I want this to return the value based on these two choice fields.  So if I choose 2023 for year and SP-A for term, I then want my formula to search all records that match the Year and Term selected and return a number.  The fields in the records it needs to check for are 'Enrolled Term' and 'Enrolled Year'.  Enrolled Term is a choice field and Enrolled Year is a number field.  

Any help, as always, is greatly appreciated!

7 replies

null
    • Fred
    • 3 mths ago
    • Reported - view

    is the choice field a simple choice?

    in the table you want to search is Enrolled Term also a choice field? if not what kind of field is it?

    • Dave_Irving
    • 3 mths ago
    • Reported - view

    So, I made a quick adjustment that I hope will make this easier.  I made the choice fields in the stats table to dynamic.  I've also changed the fields in the lead records to dynamic choices. 

    • Dave_Irving
    • 3 mths ago
    • Reported - view

    I know my syntax is completely off here, but this is the idea I am going for:

    let x := (select Leads)['Enrolled Term'];
let y := (select 'Cohort Terms')[Term];
count(x = y)

    I want it to count all the records that are determined by my choice selection.  So, if I choose SP-A term in my stats table, I want it to match it up with all my records that have SP-A as the Enrolled Term in my leads table.  

      • Fred
      • 3 mths ago
      • Reported - view

      Just a bit of background, if you just put a field name as a filter requirement, in a bracket or after a 'where', then you are telling Ninox to find records that have data in that field. So your two variables are getting all records where there is data in the respective fields.

      Can you upload a sample, non-personalized, DB?

      Can you describe using data in the sample DB what you want to happen?

    • Dave_Irving
    • 3 mths ago
    • Reported - view

    Maybe this is closer to the solution.  However, not sure if this can work for dynamic choice or simple choice fields?  

     

    let x := Term
let y :=Year
count(select Leads where Status=3)['Enrolled Term'=x and 'Enrolled Year'=y]
    • Dave_Irving
    • 3 mths ago
    • Reported - view

    Fred, just talking myself through the problem helps me find the answer.  I got the correct syntax now!!!

     

    let x := Term;
let y := Year;
count(select Leads where Status = 3 and ('Enrolled Term' = x and 'Enrolled Year' = y))
      • Fred
      • 3 mths ago
      • Reported - view

      This is the one I would recommend. If you stay with the 'where' then you should get faster results as Ninox only gets the records that match all requirements. So if you have 100 records that have a Status 3, but only 10 that match the remaining two requirements, Ninox only get 10 records.

      If you do the 2nd one, Ninox gets all records where Status = 3, then applies the remaining filters in brackets. Taking the above example, Ninox gets all 100 records and then filters with the requirements in brackets.

Login to reply

Content aside

  • 3 mths agoLast active
  • 7Replies
  • 54Views
  • 2 Following