0

Get count of chosen values from different table

Hello,

The formula below counts the number of records in the table Timetable where the choice field Jim has been chosen the value "norm"   (it works).

count(select Timetable where text(Jim) = "norm")

Why the formula below does not work as well?

let a := "Jim";
count(select Timetable where text(a) = "norm")

 

Thank you.

18 replies

null
    • Ninox partner
    • RoSoft_Steven.1
    • 1 yr ago
    • Reported - view

    formula should be:

    let a := "norm";
    cnt(select Timetable where text(Jim) = a)

    • arisziniatis
    • 1 yr ago
    • Reported - view

    Thank you for your answer Steven,

    Mind the first line works

    count(select Timetable where text(Jim) = "norm")

    I have other reasons why I want to express the formula like this:

    let a := "Jim";
    count(select Timetable where text(a) = "norm")

    It has to do with the structure of my database.

    • Ninox partner
    • RoSoft_Steven.1
    • 1 yr ago
    • Reported - view

    Then you can use the eval() function in combination with your formula:

    let a := "Jim";
    let b := "norm";
    let c := "count(select Timetable where text(" + a + ") = ""+b+"")";
    eval(c,this)

    note that the eval() function is probably not supported in the native ninox version, only when the database is in a team.

      • Ninox developper
      • Jacques_TUR
      • 1 yr ago
      • Reported - view

      RoSoft_Steven You're right, the eval function doesn't work in the where clause of the Ninox application. The get function does not work either, although it would have been appropriate for this use (https://forum.ninox.com/t/g9hs0n7/v3-7-11-new-functions#h7halvn).

      var a := "Title";
      select Customer where eval("text(Title)", this)

      This seems abnormal, as the error returned is not standard:

      TypeError: n is not a function. (In 'n(t)', 'n' is undefined) / function anonymous(ctx, db, n0, params, cb) {
      var s1;(function(cb){s1="Title";(function(cb){db.select("(select A where "+ctx.parse(db.schema,ctx.types.t0,"eval(\"text(Title)\",this)",{},true).toString()+")", function(err,nids){cb(nids||[])})})(cb)})(cb);
      }
      • Ninox developper
      • Jacques_TUR
      • 1 yr ago
      • Reported - view

      RoSoft_Steven A solution has been found by Maria , just use the brackets instead of the where clause:

      var a := "text(Title)";
      count((select Customer)[eval(a, this)="Mrs"])
      
    • arisziniatis
    • 1 yr ago
    • Reported - view

    Sorry, it does not work for me that way

    • Ninox developper
    • Jacques_TUR
    • 1 yr ago
    • Reported - view

    Another solution could be :

    var a := "Jim";
    count(select Customer
            where switch a do
            case "Jim":
                text(a) = "norm"
            default:
                false
            end)

    But it seems quite complicated.
    What exactly are you trying to do?

    • Ninox developper
    • Jacques_TUR
    • 1 yr ago
    • Reported - view

    Finally, you can use the following function:

    let a := "text(Jim)";
    count((select Timetable)[eval(a, this) = "norm"])
    
    • arisziniatis
    • 1 yr ago
    • Reported - view

    Thank you for taking the time and all the answers.

    The last functions works (but not the way I want to use it)

    This is what I am trying to do:

    My database has 2 tables

    - First table "Timetable" . Every day is a new record. Every record has 20 to 30 choice fields

    Each choice field is one person. eg "Jim" and all choice fields have the same values to be chosen: "norm" "half" "off" etc

     

    - Second Table "People" has 1 record for every person.

    So, each record has a field "Name" (which also matches a choice field in the first database) and has a tab that counts this persons chosen values from the first table on a monthly basis. Also to better understand the example below, each record has a field "AA" with a unique number.

    I have been using very ugly and very long formulas to count the choices for each person. Plus I had to add lines to the formula for every new person. So this is what I am trying to address.

    Example: This is a formula field that produces a different number for each person for the month April.

    if AA = 1 then
        let thisEvent := this;
        count(select Timetable where text(Jim) = "norm" and month(Date) = 4) +
        count(select Timetable where text(Jim) = "half" and month(Date) = 4) / 2
    else
        if AA = 2 then
            let thisEvent := this;
            count(select Timetable where text(John) = "norm" and month(Date) = 4) +
            count(select Timetable where text(John) = "half" and month(Date) = 4) / 2
        else

    if AA= 3 then ....... and so on ........ and so on ......

    I would like to change this to something like the formula below by using the field "Name" 

     

    let a := "Name";
    count(select Timetable where text(a) = "norm") and month(Date) = 4) +
        count(select Timetable where text(a) = "half" and month(Date) = 4) / 2

     

    I hope it makes sense , thank you

    • Fred
    • 1 yr ago
    • Reported - view
    arisziniatis said:
    - First table "Timetable" . Every day is a new record. Every record has 20 to 30 choice fields
    Each choice field is one person. eg "Jim" and all choice fields have the same values to be chosen: "norm" "half" "off" etc
    I have been using very ugly and very long formulas to count the choices for each person. Plus I had to add lines to the formula for every new person. So this is what I am trying to address.

    Because of your structure you have to create your very long formulas. May I recommend another way of organizing your DB? Your Timetable table should have a child table called worker, or whatever you want. So you create a new record in Timetable that tracks the day you want, then you will create a record in the child, worker, table for each worker.

    What you are doing is create a many to many (M:M) relationship between Timetable and People.

      • arisziniatis
      • 1 yr ago
      • Reported - view

      Fred Thanks, I will probably have to do that if there is no other way. However, It would be nice too if I could make that little formula work. It seems to require an advanced expression to work, somebody here may know.

      • Ninox developper
      • Jacques_TUR
      • 1 yr ago
      • Reported - view

      arisziniatis 

      arisziniatis said:
      The last functions works (but not the way I want to use it)
      arisziniatis said:
      let a := "Name"; count(select Timetable where text(a) = "norm") and month(Date) = 4) +     count(select Timetable where text(a) = "half" and month(Date) = 4) / 2

      I'm not sure why the solution proposed above doesn't seem to meet your needs?

      We could modify it slightly to match your code exactly:

      let a := "Jim";
      count((select Timetable)[eval("text("+a+")", this) = "norm" and month(Date) = 4]) +
      count((select Timetable)[eval("text("+a+")", this) = "half" and month(Date) = 4])/2

      Also, I think Fred recommendation is a good solution to avoid heavy formulas.

      Also, in the on new record trigger you can put a code that will automatically add a Worker by People each time you create a new Timetable record.

      This solution will allow you to make extractions easily and you can use them in views or in printing. In contrast, with the fixed field system, it will be difficult to manipulate the data.

      • arisziniatis
      • 1 yr ago
      • Reported - view

      Jacques TUR Yes!!! It works, thank you so much.

      I placed it like this:

      let a := Name;
      count((select Timetable)[eval("text(" + a + ")", this) = "norm" and month(Date) = 1])

      So, in every different  record the "a" is the name of a different  person, so now the code is just few lines, amazing.

      I would like to convert the database as Fred suggests, but as you understand, I am not proficient at this and it would take me ages. 

    • Fred
    • 1 yr ago
    • Reported - view
    arisziniatis said:
    I will probably have to do that if there is no other way.

    You can continue to use your method, but what happens when you change people? If you change the name of just one person then all of your code will have to change. And you lose historical data.

    It took me awhile to wrap my head around the power of a relational database, but once you do then things start to open up.

    One thing to think about is if you start to create multiple fields that track the same data (in your case people) then it is time to create a new table to track that data. Once you do, you can very flexible in how many workers you have per day, and you have historical data.

      • arisziniatis
      • 1 yr ago
      • Reported - view

       Fred One last thing, If I was to go on and change my database like you and Jacques TUR recommend, do you have in mind a specific guide or tutorial I could study  that matches my database senario? again many thanks.

      • Fred
      • 1 yr ago
      • Reported - view

      arisziniatis there isn't anything free that I know that speaks directly to your situation.

      You can watch the following videos, in English, for background on relationships and many to many relationships.

      Relationships

       

      Many to Many

       

      • arisziniatis
      • 6 mths ago
      • Reported - view

        Hello, do you know if somebody would be interested in helping me improve my database (as previously discussed in this topic) for a fee? Thank you.

      • Ninox developper
      • Jacques_TUR
      • 6 mths ago
      • Reported - view

       I'd love to. I'll talk it over with (with whom I now work) and we'll get back to you.

Content aside

  • Status Answered
  • 6 mths agoLast active
  • 18Replies
  • 341Views
  • 4 Following