Get count of chosen values from different table

arisziniatis
1 yr ago
18replies
Answered

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.

Scripting and Functions

- 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
  
  1 yr 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
  
  1 yr 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
1 yr agoLast active
18Replies
431Views
4 Following

Get count of chosen values from different table

18 replies

Content aside

Overview