Counting the number of
I'm trying to learn how to count the number of entries with a particular status set.
1. I have two tables: JOBS and TASKS.
2. Each entry in JOBS can have multiple TASKS assigned to it, so I have TASKS -> JOBS.
3. Each entry in TASKS can have a flag set for either done or outstanding.
Question: I would like to be able to see the # of outstanding jobs for each of the tasks.
I have tried constructing a filter for this using the visual editor. I can see how to count the number of tasks for each job (cnt), what the first or last task is for a particular job, or to concatenate the status.
In the text editor I would guess I need something like:
let NumberOutstanding := 0
for Counter in cnt(TASKS)
if (TASKS[Counter] == "Outstanding") the NumberOutstanding = NumberOutstanding + 1
Is this the best way to do this? Is there a code bank of example code? Can someone correct my syntax? I dont' know how to refer to individual elements of TASKS.
Thank you very much for your help.
Are you trying to create a filter or a field? I don't think filters can do select statements or pull data from another table.
One way, is create a new formula field in your JOBS table or a new formula column if you don't need it to be permanent. Then put something like this in the formula: Please make sure to update any table/field names as I don't know them.
let curRec := this; <-- the "this" statement tells Ninox to get all the data for the record
let nTasks := (select TASKS where JOBS = curRec and Flag = "Outstanding"); <--this finds all the records that match these two parameters. I'm only guessing that the reference field name in TASKS is called JOBS. If it isn't then change it to whatever you call it. Also am guessing that your field is called Flag, be sure to change this to your actual field name. Also am guessing that it is text field. If it is a choice field then it will need to be modified to text(Flag).
count(nTasks) <-- finally we can count the number of tasks found that match the current Job record and are outstanding. You can merge the last two lines, but for me it just complicates things and harder for me to debug. One line, one task.
This will then show up in your table view in JOBS showing the total number of outstanding tasks.
If there is a reationship between two tables you can use aggregate functions in the onesuch a cnt, sum, max, etc on the many. So to find the count of tasks in a job create a formula field and put this as the formula
Unless I am mistaken I don't thisk you can use a where clause to only count those that are outstanding or done through the relationship. You can move away from the relationship and use a select statement on the table but this may be a sledgehammer to crack a nut. I would add two formula fields in your TASKS table, one called Outstanding and one called Done. Use this formula for Outstanding, you will need to replace Status with your own field name.
if Status = "Outstanding" then 1 else 0 end
and this one for Done
if Status = "Done" then 1 else 0 end
Now in JOBS you can create formula fields Outstaning and Done that use sum(TASKS.Done) and sum(TASKS.Outstandng)
I hope that makes sense.
Thank you both for the prompt and thoughtful responses.
Here's what ended up working. I neglected to indicate that STATUS was a multiple choice field and that the value of the lable "Outstanding" was 1.
let curRec := this;
let nTasks := (select TASKS where JOBS = curRec and STATUS = 1);
Very much appreciated. This is awesome.
Hi John -
I like your solution as well. If you have hundreds of thousands of records this approach is a very fast way to get sums/counts/etc.
I'm not sure I understand your question around the where clause. With the formulas you give there is no where to put a where clause. If you had a select statement then you can. Does this answer your question?
kchulists, you can use numbers or if you did text(STATUS) = "outstanding" then you can use text if your brain works better that way. I think the number is cleaner but to each their own.
Glad this worked for you. Now you can dream of bigger things.
i don't know if I am right but I have noticed that select statements at the formula field level don't always get updated for every record when looking at a list I saw this in the running total question and this one is there a way to force a refresh for every record or should we be looking to avoid using select statements in formula fields.
I did use the select statement in numerous formula fields without apparent problems. I mean that I did not notice missing or spurious records. Maybe I just overlooked them...
The syntax for using either the "select" statement or the references is sometimes difficult to figure, especially the correct placement of parentheses. In the place shown below, I did put two example databases, "Test Select" and "Test Reference", showing samples of the allowed syntax. Just open the field definitions for "Table1" in either database, and look at the various formula definitions.