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
-
formula should be:
let a := "norm";
cnt(select Timetable where text(Jim) = a) -
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.
-
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.
-
Sorry, it does not work for me that way
-
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? -
Finally, you can use the following function:
let a := "text(Jim)"; count((select Timetable)[eval(a, this) = "norm"])
-
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
elseif 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) / 2I hope it makes sense , thank you
-
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" etcI 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 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.
Content aside
- Status Answered
- 1 yr agoLast active
- 18Replies
- 435Views
-
4
Following