Formula to SUM distinct records 1

[rbh123456789]

CR 11.5

//@formula
if isnull({temp_rep_app.represent_TYPE}) then 1
else if {temp_rep_app.APP_ID} <> {temp_rep_app.REP_ID} then 1
//

The above formula contains the logic of which records i want to qualify for the count.

Each of the records have a CASEID, which is the main link between all the tables.

The problem if that i have multiple records for many of the CASEIDs.

How can i sum up the above formula to only select the DISTINCT CASEIDs?

 

[lbass]

Still confused here, as you should be grouping on caseID but you seem to be referencing {temp_rep_app.id} instead--is this the case ID??? Assuming it is, then:

Also , this formula needs to check for nulls of the referenced field like this:

//{@tmp_represented}:
if not isnull({temp_rep_app.REPRESENT_PSOID}) and
{temp_rep_app.COMPPOID} = {temp_rep_app.REPRESENT_PSOID} then
{temp_rep_app.id} else
tonumber({@null})

Then change your evaluation formula to:

(
isnull(maximum({@tmp_represented},{temp_rep_app.id})) or
trim(maximum({@tmp_represented},{temp_rep_app.id})) = ""
)

-LB

 

[rbh123456789]

LBASS, YOU ARE A GENIUS!!! all the numbers are calculating perfectly!

(i just had to add a totext function as i was getting a 'string is required here' message)

Thank you so much!