0

Help with counting number of status line in subform

Can anyone give me some guidance on counting a status flag (choice field) - or suggest a better way forward! as currently just a bit stuck!

Basically I have an Alert flag (formula) field on the main form (Employees) header and this is what I want to do!
Look at the related subform Tracking and only count the number of 'Active' status flags and for a given recorded procedure (Process or Conduct) for that employee. If the count is 2 or more for the same reason then change the alert field to red.

Status is active when the expiry date is within 6 months of todays date (I have this part already sorted!) as once it goes to 6 months + 1 day the status field changes to 'Expired'..

So in the Alert Field in formula I have the following :- if not Tracking.Status >= 2 and not Tracking.Process >= 2) or if not Tracking.Status >= 2 and not Tracking.conduct >= 2) then color("red") end

Basically this in the right lines but not working as expected as without the 'not' then then Alert goes red on all other records bar the one I want it to.

Also this is not doing a count as it is only detecting the setting of the status (choice field) and not counting each entry

Ie status field not set is 0 - Status set to 1 is 'Active' and status set to 2 is 'Expired'.
I can't work out how to count the number of active records in the sub form against 'status' not undetstand why i having to use not? to get the currebt record! so i'm assuming that as status stores it value as a number then i can use this to count on!

I could combine both Process and Conduct fields into a Yes/No field instead of seperate fields as when an entry has to be make it has to set as one or the other! and nevr both at same time!

Any guidance/pointers would be very welcomed - no need to give me the complete answer as half the joy is the learning process!

image is attached is that helps (Also what is the ideal image size to select (dimensions?) when loading images onto ninox forum? and when trying to load the preview image always looks terribly squashed !!

9 replies

null
    • Mel_Charles
    • 4 yrs ago
    • Reported - view

    Sorry forgot to add image where it shows as alert set to 'Red'

    • Alain_Fontaine
    • 4 yrs ago
    • Reported - view

    Just a hint? Maybe something like:

    cnt(Tracking[Status = 1].Process) > 1 or cnt(Tracking[Status = 1].Conduct) > 1

    • Mel_Charles
    • 4 yrs ago
    • Reported - view

    Hi Alain

    - ha ha just found out why I could not progress on this- I have since been been trying something like count(tracking.status >=2).process or etc  !!! and it would not work! (been using dataease for years and count is err count !!!! DOH!)

    THEN !! i came back here and you given me more than a great hint !!!!! and If this then do that and color attribute!

    So now done and it WORKS!!!! Yey! (changed your hint to >= 2  for my purposes)

    You are the Man! :-)

    Basically it's matter of learning how Ninox phrases the scripts

    as said been using dataease for many years and script syntax (DQL) is very procedural and the syntax is like German is to English - ie same but different!! Ha Ha

    • Mel_Charles
    • 4 yrs ago
    • Reported - view

    err. actually 

    I still don't have it right as I can get it to trigger but when one goes expired it is not reseetting

    this is my script at the moment

    if cnt(Tracking[Status = 1].Process) >= 2 or cnt(Tracking[Status = 1].Conduct) >= 2 then color("red");
       if cnt(Tracking[Status = 1].Process) < 2 or cnt(Tracking[Status = 1].Conduct) < 2 then color("blue")
       end
    end

     

    hmmmm

    • Alain_Fontaine
    • 4 yrs ago
    • Reported - view

    "Alert" is a function field, yes?

    styled("", if cnt(Tracking[Status = 1].Process) >= 2 or cnt(Tracking[Status = 1].Conduct) >= 2 then "red" else "blue")

    • Mel_Charles
    • 4 yrs ago
    • Reported - view

    Hi Alain

    Alert is a plain old Ninox formula field and can be styled !

    Also just a question is - (Tracking[Status = 1].Process) >= 2 Ninox's syntax to say Status =1 AND process >= 2

    cos i need both these conditions to be true

    ie in the tracking table (for a given employee) if the status = "active' and the count of Process is >= 2 OR status = Active and the count of Conduct is >=2 then flag the color. In all lines they must be active and it don't count if there is only 1 line of process and 1 line conduct!.
    I can't touch it tonight - but will have another stab tomorrow. If i get a bit stuck I will plead for some more help! 

    Interesting I flipped the code around a little to : (only did this on a test bit so left our 'process' etc

    -if cnt(Tracking[Conduct = 1].Status) >= 2 then color("red")
    end

    and that deffo picked up the red for both lines but only 1 status

    So if i can get it functioning on say just conduct then i can build process in later

    • Alain_Fontaine
    • 4 yrs ago
    • Reported - view

    Sorry, I had not paid enough attention to the definitions of the various fields. The formula below, as the definition of the "Alert" formula field, should work better:

    styled("", if cnt(Tracking[number(Status) = 1 and Process]) >= 2 or cnt(Tracking[number(Status) = 1 and Conduct]) >= 2 then
        "red"
    else
        "blue"
    end)

    This: « Tracking[number(Status) = 1 and Process] » returns the records linked to a given Employee where "Status" has reference number 1 and "Process" is true.

    This: « cnt(Tracking[number(Status) = 1 and Process]) » counts the returned records.

    This: « cnt(Tracking[number(Status) = 1 and Process]) >= 2 » checks if the count is >= 2.

    The same test is repeated for ”Conduct", and both are or-ed.

    • Mel_Charles
    • 4 yrs ago
    • Reported - view

    Hi Alain

    for some reason it didn't like the styed("", bit

    so reset is as following to add the color statement back in

    if cnt(Tracking[number(Status) = 1 and Process]) >= 2 or cnt(Tracking[number(Status) = 1 and Conduct]) >= 2 then
    color("Red")
    else
    color("Clear")
    end

    and it now works perfectly!!

    I would not have got there without your help - I owe you buddy !!!

    Kind regards - You are the man !

    • Alain_Fontaine
    • 4 yrs ago
    • Reported - view

    I am glad that you git the result you expected. I don't understand why styled() does not work, but if you need to only show a colored patch with no text in it, color() is fine.

Content aside

  • 4 yrs agoLast active
  • 9Replies
  • 813Views