Count query

Mark HHP

Hi guys,

I have a query query and I'm thinking I would need to use the COUNT function but maybe not.

I have Client BO which have Task BOs attached. The Tasks have two statuses, Completed and In Progress. How could I pull a report of all Clients where there are no In Progress tasks? This is where I though COUNT would come in but I'm not sure (a) where to go with that line of thinking or (b) if there's an easier way.

kenlyle

My first thought on this was "outer join".

I did a quick Google search for will a SQL count query result in zero, and this seems to be your answer:

http://stackoverflow.com/questions/14793057/how-to-include-zero-0-results-in-count-aggregate

Mark HHP

Hmm, ok but is there a way to run that through Aware rather than MySQL?

tford

I have Client BO which have Task BOs attached. The Tasks have two statuses, Completed and In Progress. How could I pull a report of all Clients where there are no In Progress tasks?

1) Add some attributes:
Client.Nbr_Tasks
Client.Nbr_Tasks_Completed
Client.Nbr_Tasks_In_Progress
Client.Nbr_Tasks_No_Progress

2) Add some rules to Client BO:

Client.Nbr_Tasks=COUNT Tasks WHERE Task in Client.Tasks
Client.Nbr_Tasks_Completed=COUNT Tasks WHERE Task in Client.Tasks AND Task.Status='Completed'
Client.Nbr_Tasks=COUNT Tasks WHERE Task in Client.Tasks AND Task.Status='In Progress'
Client.Nbr_Tasks=COUNT Tasks WHERE Task in Client.Tasks AND Task.Status IS UNDEFINED

Sounds like you would then report on Clients with Client.Nbr_Tasks equal to Client.Nbr_Tasks_No_Progress

Mark HHP

Urgh Tom, again you shame me with how simple the solution is. I would use the end of year as excuse but my brain is like this throughout the year! Thanks again.

BLOMASKY

Ummmm...Errrr.... how about you don't create any new attributes and you don't create any new rules and instead your query looks like:

FIND Client WHERE NOT (EXISTS Tasks WHERE (Client.om_Tasks.status = 'Progress'))

Bruce

Mark HHP

That sounded good, until this: Internal error. Error reading business object. Unknown column 'Client.ID' in 'on clause'

FIND Client WHERE NOT (EXISTS Tasks WHERE (Client.om_Tasks.status = 'Progress')) << What does the om refer to? I think mine would be: FIND Client WHERE NOT (EXISTS Task WHERE (Client.Tasks.Status = 'In Progress'))

tford

FIND Client WHERE NOT (EXISTS Tasks WHERE (Client.om_Tasks.status = 'Progress'))

Good idea Bruce.

The one downside to this approach though is that if you wanted to allow a user to see that ONE particular client has not made progress, you would have not have an attribute to flag that condition. As you pointed out though, it could be used very effectively for the aggregate scenario.

BLOMASKY

Mark HHP wrote
That sounded good, until this: Internal error. Error reading business object. Unknown column 'Client.ID' in 'on clause'

FIND Client WHERE NOT (EXISTS Tasks WHERE (Client.om_Tasks.status = 'Progress')) << What does the om refer to? I think mine would be: FIND Client WHERE NOT (EXISTS Task WHERE (Client.Tasks.Status = 'In Progress'))

Mark, I don't know how you named your attributes. I am following Mark Baileys naming by prefacing all relationships with OM (own many), OS (own Single), OB (owned by) and PS and PM for peer single and peer many.

Yes, you do have to change the code to how you name your attributes.

Does this work for you? Fast enough?

BLOMASKY

Tom, it seems, just like when using Magic, there is more than one way to skin a cat (or code a solution). I am not sure what you mean about find the ONE client with no progress. I thought the requirement was to find all clients with NO progress.

(and being a lazy bum, anything where I have to write less code is GOOD!)

tford

I am not sure what you mean about find the ONE client with no progress. I thought the requirement was to find all clients with NO progress.

Your solution definitely fit his requirement Bruce.

I was just pointing out that if you were editing or viewing one Client on a form, having an attribute that that easily identifies the status might be useful.

Mark HHP

I keep getting that error: Internal error. Error reading business object. Unknown column 'Client.ID' in 'on clause'

Blargh. All of this stuff I wish I'd known in the beginning.