Tips and Tricks
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Tips and Tricks
Message Icon Topic: If duplicate then Post Reply Post New Topic
Author Message
cseaton
Newbie
Newbie
Avatar

Joined: 21 Mar 2012
Online Status: Offline
Posts: 3
Quote cseaton Replybullet Topic: If duplicate then
    Posted: 21 Mar 2012 at 4:28am
I need help writing a formula..
 
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!!
Charles Seaton
Report Developer
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Mar 2012 at 7:18am

can you group on the person or will that break your report design?

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?


Edited by DBlank - 21 Mar 2012 at 7:19am
IP IP Logged
cseaton
Newbie
Newbie
Avatar

Joined: 21 Mar 2012
Online Status: Offline
Posts: 3
Quote cseaton Replybullet Posted: 21 Mar 2012 at 9:03am
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"
                  
Charles Seaton
Report Developer
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Mar 2012 at 9:22am
First lockwelle would suggest using a stored procedure as your source and for things like this I agree that would be easiest. However I am going to assume you need a "crystal only" solution here.
 
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? 


Edited by DBlank - 21 Mar 2012 at 9:24am
IP IP Logged
cseaton
Newbie
Newbie
Avatar

Joined: 21 Mar 2012
Online Status: Offline
Posts: 3
Quote cseaton Replybullet Posted: 21 Mar 2012 at 10:03am
Yes, that is exactly right..
Charles Seaton
Report Developer
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Mar 2012 at 11:57am
maybe
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})
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
 
MINIMUM({charges.begin_date_of_service},{table.client}) = dateadd('d',-1,currentdate)
and
 
 
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.