[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 650
  • Last Modified:

Crystal XI, SQL db, tracking change/previous dates

This is a 2-fold question: A) exporting from Crystal XI to a csv file, I have a file that is setup for selection as a "full" file or a "change" file.  When I export the "full" file as a csv and open it in TextPad it looks fine, if I export the change file as a csv and open it in TextPad there are gaps between employee lines, see attached "Exporting as csv" word doc.  B) in the attached Crystal XI report, I am working in a SQL db there are several fields that need to be tracked for changes along with the associated date of change.  If I run the "full" file the results for the field that has never changed should be the original date, else if the field has changed the date should reflect the most current change date (EMSH Action Date). On the "change" selection if there is a change on one the fields then the new value should appear along with the current date of change.  If the field has not changed then the field and associated date field should be blank.  See the list of field formulas within the report attached to this question.  There is a Calc-Reset-Display formula that is being used.
VBAS-Interface-B.RPT
Exporting-as-csv.docx
Fields-Impacted-on-Full-and-Chan.docx
0
Grapelady
Asked:
Grapelady
  • 7
  • 5
  • 4
2 Solutions
 
mlmccCommented:
You are getting a blank line for each detail record you suppress.

I believe that i the way the export routine i written and short of writing yur own, I don't think you can get around it.

You could write a routine to scan the CSV file and replace <CR><CR> with <CR>
If there are 2 carriage returns then delete 1

mlmcc
0
 
James0628Commented:
To be honest, I haven't looked at your files, but I think mlmcc is correct.  My first guess was that you were suppressing records in the "change" report.  I believe I've seen this with one or more types of CR export.

 The best solution might be if you could change the report, or the datasource, so that the "change" report only read the desired records and didn't have to suppress any.

 Otherwise, you could use some utility to remove the blank lines.  Or, you could sort the file, so all of the blank lines would be at the beginning, which would make them easier to delete manually in an editor.

 Why are the blank lines a problem?  Is it just because of the blank space when you're looking at the file, or is it something else?  Since this is a CSV file, are you importing it into something else?  If so, and if the problem has to do with importing blank lines, then maybe you could change the report to put dummy data on those lines, instead of suppressing them, and then when you import the CSV file, or sometime after that, you could exclude or remove the "dummy" lines.

 James
0
 
GrapeladyAuthor Commented:
Sorry for the delay on an answer.  Thanks for the suggestions on the blank lines, unfortunately the report is setup to run as a full file or as a change file with a suppression on the detail line for the changes to appear only based on the Calc, Display and Reset formulas, if N then suppress. Once exported out as a *.csv file on the changes the file is sent out to a vendor to feed into another system.  Not sure if the blank lines would have an impact on the vendor's system.  I am going to say at this point I will let this issue drop until further notice.  

Now I did have a second part of the question on tracking previous dates on changes.  I am not sure if there is a way to keep a date on changes once history is buried in the database records.  Like Department changes on 4/1/2012, then there are several more changes to history, this record is no longer a current record, then on full file the date should remain as the date the particular change occurred and not be the current date.  Is there a memory command or something that would keep the date?  I am trying to track it as a previous record.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
mlmccCommented:
Not sure I understand the problem.

Perhaps you could illustrate it with an example

mlmcc
0
 
James0628Commented:
Some more details would be helpful.  For example, in your example, you said that the Department changes on 4/1/2012 and then there are other changes after that, but it seems that the date that you're interested in is 04/01.  Why that date and not the date for the most recent change, or any of the other changes after 04/01?

 Putting that aside for the moment, it sounds like there are a couple of things you might look at.

 You mentioned "previous record".  If you literally want the value from the record before the current record (and only that specific record), there is a Previous function that will give you the value from the previous record.

 If you have a set of records and want to "save" the value from one of those records, you could have a formula save the value in a variable.  The details will depend on exactly what you're trying to do.

 James
0
 
GrapeladyAuthor Commented:
I am not familiar with variables but this sounds like what I may be looking for.  Example would be:  The report if pulling full information would reflect when a change occurred on a specific field.  An employee would have a department change on 4/1/2012, then a miscellaneous change on 5/1/2012, 6/1/2012 burying the department change, so current date would not apply any longer.  I want to keep the date on the report for the actual department change.  So the formula would be looking at previous records to see when the field changed and then keep that date.   Am I clear?
0
 
mlmccCommented:
Are you trying to get the date of department change only?

How do you know which change to keep the date for?

mlmcc
0
 
GrapeladyAuthor Commented:
I am actually trying to capture changes on other fields too, see attachment Fields impacted on the report.  In the past I had used the Previous or the Next along with the WhilePrinting, so I am not sure why this is not working this time, I need to hold the date the field changed.  I don't have anything specific to capture change, it will be random within a date range.
0
 
mlmccCommented:
Are you trying to capture all the changes in a time period or just the first change?

mlmcc
0
 
GrapeladyAuthor Commented:
There are five fields that I am trying to track changes on with a corresponding date.  On the Change selection which would be within a date range would make a comparison from the current record to the past record to check if the field had changed, if it had then I need the action date field to reflect the change date.  If it is the Full selection then I need to see the most current record but I need to see the date that change occurred this could be several dates back in time.  This date would be a variable on each of the five fields I am tracking changes on.  Is this possible?
Full file:
Field 1 changes 01/01/2012, reflect 01/01/2012 on the results
Field 2 changes 02/01/2012, reflect 02/01/2012 on the results
Field 3 does not change so the date field would be reflect the original entry of information
Field 4 does not change so the date field would be reflect the original entry of information
Field 5 changes 03/01/2012, reflect 03/01/2012

Change file with date range of 03/01/2012 - 03/31/2012:
Field 1 does not change during the date range so the date would be blank
Field 2 does not change during the date range so the date would be blank
Field 3 does not change during the date range so the date would be blank
Field 4 does not change during the date range so the date would be blank
Field 5 changes during the date range so the date should reflect the change date.

Does this clarify or not?
0
 
GrapeladyAuthor Commented:
Is there not an answer for this question?
0
 
James0628Commented:
Do you actually have a "change date" field that tells you that field A was changed on date X; or do you have something like transaction records that just give you the field values on certain dates, and you have to compare the values for field A from record to record, and when they change, use the date from the record after that change; or something else?

 If there is a "change date" field, is there a separate field for each data field (eg. "field A change date", "field B change date", etc.), or just a single date field for the whole record (so the "change date" field tells you when something was changed, but not which field)?

 James
0
 
GrapeladyAuthor Commented:
Per your statement this is what I am working with: "do you have something like transaction records that just give you the field values on certain dates, and you have to compare the values for field A from record to record, and when they change, use the date from the record after that change; or something else?  and just a single date field for the whole record (so the "change date" field tells you when something was changed, but not which field)?"

I do not have a "change date" for each transaction on each field change.  I have to compare the values for field A from record to record, compare field B from record to record, etc., they could happen on the same record or diffterent records resulting in different change dates. I do have a date field but it is per record not per field change.

Please advise...
0
 
mlmccCommented:
Could a field change more than once?
If so what change date do you want to keep?

mlmcc
0
 
James0628Commented:
Just to give you some idea of the kind of thing I was thinking about, here are the basics for one approach.

 Let's say you have 4 fields you want to track.  You could have a "change date" variable for each field - ChangeDate1, ChangeDate2, ChangeDate3 and ChangeDate4.  You could have a formula like this:

Global DateVar ChangeDate1;
Global DateVar ChangeDate2;
Global DateVar ChangeDate3;
Global DateVar ChangeDate4;

if not OnFirstRecord then
(
  if {field1} <> Previous ({field1}) then
    ChangeDate1 := {your date field};
  if {field2} <> Previous ({field2}) then
    ChangeDate2 := {your date field};
  if {field3} <> Previous ({field3}) then
    ChangeDate3 := {your date field};
  if {field4} <> Previous ({field4}) then
    ChangeDate4 := {your date field}
);
""


 Put that in your detail section and at the end of the report or group, the variables would have the date of the most recent change for each field.

 That's one basic idea.  The details will depend on exactly what you're trying to do.

 For example, if you want the date for the earliest change instead of the most recent change, the formula would be a bit different.

 If you're trying to get the change dates for a group of records, then you'd want to put a formula somewhere (eg. in the group header) that resets the date variables to some default value (so they start fresh for each new group).

 James
0
 
GrapeladyAuthor Commented:
to mlmcc's questions:  I am trying to capture the most current change on a field change.
to James formula, let me try it out and I will let you know if I have issues with the setup.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 7
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now