Need help with formulas "replace" and "If" o

[okuser]

I am new at crystal reports and need some help. I am using CR 8.5 and retrieving data from Access. I have a field of a TransDescription that I need to do 2 things to. Any suggestions on how to do this will be helpful. I need to first replace anything that reads "Clock Time" or "Clock Time or Clock-In" with "Regular" just for the report, I cannot change the database. Then I need to split "Regular" to show in one field and all others to show in another. Am sending out a CSV file and they need to be in separate fields. For example the result would be as follows:

sick, (blank)
(blank), regular

I have accomplished writing this in to separate reports and would have to send out 2 separate CSV files. I would rather create one report and send just one file. I was trying to do the "replace" starts with to change the name and then do 2 formulas to do "if thens else" however, I do not know how to get the replace to work prior to the if then or how to write in one formula.

Any suggestions would be helpful.

 

[lbass]

I'm not sure I understand what you meant by your display with the
", (blank)" and "(blank), ". The following will substitute "Regular" for the appropriate substrings. If both of the replaced substrings are in one string, "Regular" will appear twice--is this what you want? Or would you only want to see it once even if it appears more than once in the parent string?

The formula then sorts the string into two strings. The first one will display the "Regular" string:

whileprintingrecords;
stringvar replacex := replace(replace({table.string},"Clock Time or
Clock-In","Regular&quot,"Clock Time","Regular"
stringvar array both := split(replacex, ", "
numbervar loop := ubound(both);
numbervar counter;
stringvar regular;
stringvar other;

for counter := 1 to loop do(
if instr(both[counter],"Regular&quot = 0 then regular := regular else regular := regular + both[counter] + ", ";
if instr(both[counter],"Regular&quot = 0 then other := other + both[counter]+", " else other := other);
left(regular, length(regular)-2);

Copy the formula and change the last line to:

left(other, length(other)-2);

...in order to display the "Other" substrings, e.g., "Sick, Holiday, Vacation."

-LB

 

[okuser]

Let me explain differently what I have and what I need:

I have a table like the following

Hours InDate TransDescription
8 10/1/03 Clock Time
4 10/2/03 Clock Time or Clock-IN
4 10/2/03 Sick
8 10/3/03 Regular

Client needs to receive a CSV that would show:

Hours, InDate, Absent, Worked

Absent hours include everything except Clock Time, Clock Time or Clock-In, and Regular. All of the ones I just listed are then considered Worked and need to read as Regular. All Absent are read as they are in the TransDescription.

So, what I need is to split the items that are in the TransDescription and put all Absent in one field and all Worked in another field and all Worked need to read as Regular.

Could I do a nested IF of some sort like

Worked Formula

If {TransDescription} startswith "Clock" then "Regular" and
If {TransDescription} equals "Regular" then "Regular" else
" "

Absent Formula

If {TransDescription} isnot "ClockTime" then {TransDescription} or
If {TransDescription is not "ClockTime or Clock-In} then {TransDescription} or
If {TransDescription} is not "Regular" then {TransDescription} else
" "

I do not know if this will work, am at home over the weekend and do not have a way to test it. I was just still trying to think of what I could do. I do not know if there is a "isnot" function or "doesnotequal" or something that I could use.

Thanks for your earlier response.

 

[lbass]

I think you just have to use the replace function in a formula {@transdesc} to be placed in the details section:

replace(replace({table.transdescription},"Clock Time or
Clock-In","Regular&quot,"Clock Time","Regular&quot

Group on {table.InDate} and drag the group name to the group footer and then create two formulas:

{@Worked} for details section:
if {@transdesc} = "Regular" then {table.hours}

{@Absent} for details section:
if {@transdesc} <> &quot;Regular&quot; then {table.hours}

Then insert summaries on {table.hours} (for total hours), {@Worked}, and {@Absent} and suppress the group header and details section.

I don't know about creating CSV files, but maybe this will help get you started...

-LB