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
-
Sorry forgot to add image where it shows as alert set to 'Red'
-
Just a hint? Maybe something like:
cnt(Tracking[Status = 1].Process) > 1 or cnt(Tracking[Status = 1].Conduct) > 1
-
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
-
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
endhmmmm
-
"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")
-
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 >= 2cos 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")
endand 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
-
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.
-
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")
endand it now works perfectly!!
I would not have got there without your help - I owe you buddy !!!
Kind regards - You are the man !
-
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
- 816Views