Crystal Reports If duplicate then

Tips and Tricks
Crystal Reports Forum : Crystal Reports 9 through 2022 : Tips and Tricks

Topic: If duplicate then

Author	Message
cseaton Newbie Joined: 21 Mar 2012 Online Status: Offline Posts: 3	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 Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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 Logged

cseaton Newbie Joined: 21 Mar 2012 Online Status: Offline Posts: 3	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 Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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 Logged

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

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	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}) SUM({@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 MINIMUM({charges.begin_date_of_service},{table.client}) = dateadd('d',-1,currentdate) and SUM(0">{@flag},{table.client})>0
	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.