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.

## 16replies • Ninox partner
• RoSoft_Steven.1
• 2 mths ago
• Reported - view

formula should be:

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

• arisziniatis
• 2 mths ago
• Reported - view

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
• 2 mths 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.

• Jacques_TUR
• 2 mths 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);
}``````
• Jacques_TUR
• 2 mths 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
• 2 mths ago
• Reported - view

Sorry, it does not work for me that way

• Jacques_TUR
• 2 mths 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?

• Jacques_TUR
• 2 mths ago
• Reported - view

Finally, you can use the following function:

``````let a := "text(Jim)";
count((select Timetable)[eval(a, this) = "norm"])
``````
• arisziniatis
• 2 mths 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
• 2 mths 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
• 2 mths 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.

• Jacques_TUR
• 2 mths 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
• 2 mths 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
• 2 mths 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
• 2 mths 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
• 2 mths 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