Is there a group by in a flowfield?

wolfulium

Hey I am trying to get the number of jobs that have billable type planning lines for a cue on the role center.

I have a flowfield that is set up like so:

Count("Job Planning Line" WHERE (Line Type=CONST(Billable),Planning Date=FILTER(<TODAY)))

this is getting me a list of planning lines but also has a multiples for a job.
I only need the count of jobs with planning lines not the count of the planning lines.

Is there a better way to join the job table on to the planning lines? or Is there a group by that helps clean this up?

bbrown

A flowfield will not produce the results you are looking for. It returns a single value and the associated drilldown will return the individual records.

To acheive the desired results consider using a Query and calling it from a function. You could have the function run a page based on a temp table. Then use the Query to populate the temp table.

Slawek_Guzek

I was thinking about suggesting using a query object, but what is really needed in this case is a COUNT(DISTINCT "Job No.")) equivalent. Query, unfortunately, cannot do that.

Rather than having a temp table and populating it in a function I would add a flowfield on the Job table, EXISTS type, which would show if the job has a billable planning line within the date filter. Then set filter =TRUE on this flowfield and then COUNT on Job table.

Kishorm

You can effectively achieve this by using "Group By" in Queries. Have a look at this blog...

https://community.dynamics.com/nav/w/designpatterns/152.select-distinct-with-queries

bbrown

A flowfield will not produce the results you are looking for. It returns a single value and the associated drilldown will return the individual records.

To acheive the desired results consider using a Query and calling it from a function. You could have the function run a page based on a temp table. Then use the Query to populate the temp table.

Slawek_Guzek

I was thinking about suggesting using a query object, but what is really needed in this case is a COUNT(DISTINCT "Job No.")) equivalent. Query, unfortunately, cannot do that.

Rather than having a temp table and populating it in a function I would add a flowfield on the Job table, EXISTS type, which would show if the job has a billable planning line within the date filter. Then set filter =TRUE on this flowfield and then COUNT on Job table.

Kishorm

You can effectively achieve this by using "Group By" in Queries. Have a look at this blog...

https://community.dynamics.com/nav/w/designpatterns/152.select-distinct-with-queries