| Author |
Message |
BoltzGirl
Groupie
Joined: 22 Jan 2013
Online Status: Offline
Posts: 89
|
   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 Logged |
|
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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 Logged |
|
BoltzGirl
Groupie
Joined: 22 Jan 2013
Online Status: Offline
Posts: 89
|
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 Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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 Logged |
|
BoltzGirl
Groupie
Joined: 22 Jan 2013
Online Status: Offline
Posts: 89
|
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 Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 14 Jul 2017 at 9:49am |
|
Yes, the tables and the current linking between each table (looks like three tables involved here).
|
|
IP Logged |
|
BoltzGirl
Groupie
Joined: 22 Jan 2013
Online Status: Offline
Posts: 89
|
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 Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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 Logged |
|
BoltzGirl
Groupie
Joined: 22 Jan 2013
Online Status: Offline
Posts: 89
|
Posted: 14 Jul 2017 at 10:40am |
I GOT IT!!!!!!!!!!!!!!!!!!!!
Thank you so very much for your time!!!!!
Really helpful and appreciative. 
|
|
IP Logged |
|
|
|
