Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2016 : Technical Questions
Message Icon Topic: How to pull just one line from multiple Post Reply Post New Topic
Author Message
BoltzGirl
Groupie
Groupie
Avatar

Joined: 22 Jan 2013
Online Status: Offline
Posts: 53
Quote BoltzGirl Replybullet Topic: How to pull just one line from multiple
    Posted: 13 Jul 2017 at 6:46am
CRYSTAL 2013 V14.1.7.1853

I am trying to pull one line of data from multiple on one customer record and can't figure it out.

Example:
Customer #123456 has a notes on their account that are entered on different dates and times.
Customer #123456
    04/15/2016 - Letter sent for balance due
    04/17/2016 - Payment Received
    05/01/2016 - Sent letter for ADHOC

I want to pull in only those Customer #'s that do not have a line that has 'ADHOC' in any note on their account.

I have tried putting multiple ways like one below in my selection criteria and they don't work:

Not ({CustNoteChangeLog.CHGFNC} like "*ADHOC*")

So far advice is that I can't do this in Crystal because there are multiple records within the notes of the record and Crystal can't pick out of many. I had a similar issue a few years ago and someone showed me a 'grouping' that I could create as a formula to bring in all the notes on the record, then sort them so that I was reading the last line only and pull those that didn't have the line I was looking for, but silly me can't find those notes.

Any help or suggestions is kindly appreciated.
IP IP Logged
kevlray
Moderator
Moderator
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1331
Quote kevlray Replybullet Posted: 13 Jul 2017 at 8:07am
It appears that each 'note' is a separate record. Thus, it would only filter out the last note (in your example above).  The other way would be create a command or stored procedure and put the filter (in a Where clause) for that table (CustNoteChangeLog).

There may be a way to do a group filter for this.


IP IP Logged
BoltzGirl
Groupie
Groupie
Avatar

Joined: 22 Jan 2013
Online Status: Offline
Posts: 53
Quote BoltzGirl Replybullet Posted: 13 Jul 2017 at 8:27am
That is correct, sorry forgot to specify that. Those are the recommendations I have been hearing command or stored proc and my team prefers neither (all still beginners we are)...LOL. Just the fear of the unknown that's all. Thank you for your reply!
IP IP Logged
kevlray
Moderator
Moderator
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1331
Quote kevlray Replybullet Posted: 13 Jul 2017 at 12:06pm
The command or stored proc is really the only way around this.  It should just be some basic SQL (do not know what your datasource is).  If you can provide some details (tables, current joins, fields needed) we might be able to assist you.
IP IP Logged
BoltzGirl
Groupie
Groupie
Avatar

Joined: 22 Jan 2013
Online Status: Offline
Posts: 53
Quote BoltzGirl Replybullet Posted: 14 Jul 2017 at 9:09am
Selection Criteria/Parameters:

{AUTHS.GRP} like {?Plan} and
{AUTHS.PLNP} like {?Bus} and
{AUTHS.DecDate} in {@Beg Date}to {@End Date} and
{AUTHS.IPACD} = {?GROUP} and
{AUTHS.RefType} = "HH" and
{AUTHS.Stats} = "App"
{V_DataServType.ServTy} in ['generic','lter'] and
Not ({StatChgLog.NOTES} like "*MDCR*")

FIELDS NEEDED:
AUTHS_PROCDATA
AUTHS
AUTHSTATNOTES
CUSTOMERS
V_DATASERVTYPE

Did I miss anything? SOOOOO Appreciate the help.
IP IP Logged
kevlray
Moderator
Moderator
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1331
Quote kevlray Replybullet Posted: 14 Jul 2017 at 9:49am
Yes, the tables and the current linking between each table (looks like three tables involved here).
IP IP Logged
BoltzGirl
Groupie
Groupie
Avatar

Joined: 22 Jan 2013
Online Status: Offline
Posts: 53
Quote BoltzGirl Replybullet Posted: 14 Jul 2017 at 9:55am
TABLES:
Auth.ProcData
Authz
Customers
Pricing
ProcData
AuthChgNoteLog

The linking is only on Customer number which is in all tables, as well as a seq customer number that we link to and that is all the linking.
IP IP Logged
kevlray
Moderator
Moderator
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1331
Quote kevlray Replybullet Posted: 14 Jul 2017 at 10:17am
Okay here is a query based on what you provided me (forgot to tell you that you could have selected 'Show Query' from under the Database menu item to give me your current query).  The syntax is based on MS SQL.  Also you will need to define all your parameters within the command.  I put tick marks around parameters that I thought would be strings.  Also now that you have a 'different' data source.  You will need to map the current fields to the fields from the command.

I hope this helps.

select
    AUTHS_PROCDATA,
    AUTHS,
    AUTHSTATNOTES,
    CUSTOMERS,
    V_DATASERVTYPE
FROM
    Auth.ProcData
    inner join Authz on Authz.CustomerNumber = Auth.ProcData.CustomerNumber --Not sure what the actual field name is
    inner join Customers on Customers.CustomerNumber =Authz.CustomerNumber
   inner join Pricing on Pricing.CustomerNumber = Customers.CustomerNumber
   inner join ProcData on ProData.CustomerNumber = Pricing.CustomerNumber
    inner join (SELECT CustomerNumber.AUTHSTATNOTES FROM AuthChgNoteLog WHERE Not ({StatChgLog.NOTES} like '%MDCR%') ) AS AUTHSTATNOTES on AUTHSTATNOTES.CustomerNumber = ProcData.CustomerNumber

WHERE
    AUTHS.GRP like '{?Plan}' and
    AUTHS.PLNP like '{?Bus}' and
    AUTHS.DecDate in {@Beg Date}to {@End Date} and
    AUTHS.IPACD = '{?GROUP}' and
    AUTHS.RefType = 'HH' and
    AUTHS.Stats = 'App' and
    V_DataServType.ServTy in ('generic','lter')


Edited by kevlray - 14 Jul 2017 at 10:19am
IP IP Logged
BoltzGirl
Groupie
Groupie
Avatar

Joined: 22 Jan 2013
Online Status: Offline
Posts: 53
Quote BoltzGirl Replybullet Posted: 14 Jul 2017 at 10:40am
I GOT IT!!!!!!!!!!!!!!!!!!!!

Thank you so very much for your time!!!!!

Really helpful and appreciative.
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.031 seconds.