0

Counting the number of

Good afternoon;

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

return NumberOutstanding

_____

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.

13replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    • Fred
    • Fred
    • 1 yr ago
    • Reported - view

    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.

    Like
  • Hi

     

    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

     

    cnt(TASKS)

     

    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.

     

    Regards John

    Like
  • Sorry Fred. We ovelapped and I hadn't seen your reply. Do you know if you can use a where clause through the relationship? I experimented but couldn't get anything to work.

     

    Regards John

    Like
    • kchulists
    • kchulists
    • 1 yr ago
    • Reported - view

    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);
    count(nTasks)

     

    Very much appreciated.  This is awesome.

    Like
    • Fred
    • Fred
    • 1 yr ago
    • Reported - view

    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.

    Like
  • It is indeed possible to impose a condition while following a reference. The trick is to use the "square bracket" syntax:

    cnt(TASKS[STATUS = 1])

    Like
    • Sean
    • Sean
    • 1 yr ago
    • Reported - view

    Hi Alain,

    I can't get that to work in the Mac app. I get the error, "Field not found:" for the table equivalent to "TASKS".

    Like
    • Sean
    • Sean
    • 1 yr ago
    • Reported - view

    Alain,

    Never mind. I figured out it must be the Reference name not the Table name. I always rename the Reference field to avoid that confusion.

    Like
  • Alain that is amazing. I'm learning something new every day!

    Like
  • Strange, it works in the iPad app.

    Like
  • While we are at it... An expression like:

    if Status = "Done" then 1 else 0 end

    can be replaced by:

    number(Status = "Done")

    Like
  • Hi Alain

     

    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.

     

    thanks John

    Like
  • 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.

    https://app.box.com/s/ogqz40youex6ncgu6c6z0whklcmrm4dw

    Like
Like Follow
  • 1 yr agoLast active
  • 13Replies
  • 1173Views