I have a report that needs to show if a SIM code is duplicated anywhere for a certain person.. is there something like;

 

if [fieldname] in ["SIM code"] = duplicate then "true" else "false"

 

?

 

Thanks!!

do you just want the person, or all rows for the person, or only duplicated rows for the person?

 

Also I do not understand if you want to know this when

1. a person has more than 1 row with SIM CODE  in it

or

2. a person has more than 1 row where the value in the field simcode is duplicated

or

3. a person has more than 1 row where if the value in one field is duplicated when another field ='simcode' 

 

Can you clarify or show sample data and how you want to handle it?

Hi and thank you.. I've actually realized my old logic wouldn't work.. I'm trying this instead..

I need a report for each previous day that shows clients who have been charged as "established" when they were "new" .. all "new" clients are charged using these SIM codes (see formula below)

I thought to use the minimum function to see if yesterdays date is their first encounter ever.. if that is so and anything OTHER than the SIMS below.. then they belong in this report... the below formula does not give me what i need thus far.. any thoughts?

 

 

if {charges.begin_date_of_service}= minimum ({charges.begin_date_of_service}) and not ({charges.service_item_id} in ["99201","99202","99203","99204","99205","99384","99385","99386","99387"] )
 then "true" else "false"
                  

 

You have to pull all records regardless of date into the report. If you exclude any rows then it will not know that row exists and give you false positives.

 

You will have to group on the client and then use group select conditions to get the final data set. I am still not clear on exactly how you are defining your data...

is this accurate:

1. you need to define what is the earliest date of service for any client.

2. if the ealiest date for that client is yesterday then you also need to define what was the charges.service_item_id value assigned to that client on that date.

3. If the value is not in the list you gave then the client should appear in your report.

Is that correct? 

group on client

//create a flag formula

if {charges.begin_date_of_service}= dateadd('d',-1,currentdate) and not ({charges.service_item_id} in ["99201","99202","99203","99204","99205","99384","99385","99386","99387"] ) then 1

 

 

create 2 summaries at the group level

MINIMUM({charges.begin_date_of_service},{table.client})

SUM( mailto:%7b@flag%7d,%7btable.client - {@flag},{table.client })

place in the group footer

this should show you the MIN = yesterday and the sum>0 for all clients that you want to show.

Assuming this is correct you can use these in a group select statement