How to create this variable

[smeyer56]

I have a one to many relationship and would like to search through the many to see if it is one of a few strings. If it is the variable would be 1,2,3... and if none of the many contain these vallues, then it would be 0. Is this possible. Also, if it is found that the second of ten contain one of the values, I need to stop looking at the other 8.

Thanks

 

[dgillz]

Would the literal string "1,2,3" be somewhere within the string together, or do you mean it would separately contain the strings "1", "2" and "3"?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[Madawc]

Do you mean that you need to check a group, several instances of a record from Table B linked to Table A?

If so, first put into a group. For display you'll need to suppress the group header and details and just show the group footer.

You'll need to do several running totals doing the various tests. A count that resets for the group and counts using some formula like

not isnull({your.field}) and
{your.field} in ["1", "2", "3"]

These would then need to be tested in a formula field, allowing you to choose the correct result. Note that running totals that find nothing come out as nulls.

I don't follow what you mean by second of ten. If it is the second of ten records, then you can do a count to find it.

PS. It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[smeyer56]

Sorry for the lack of clarity.
I am using V10.
After thinking about how to do/describe this more, I think it would help to explain a bit more. The one of the one to many is a ticket header and the many is the line items on a sales ticket. If a certain line item is encountered on a ticket, the variable would be set to the corresponding value of "1","2","3" etc and these values would be used to group and sort.

What I am trying to do is look at history to see what tickets have a coupon, designate and sort on what type of coupon and total for an average sale.

Thanks.

 

[lbass]

You should show some sample data and also show what you expect the new field to show in relation to that data.

-LB

 

[Madawc]

Try something like

if isnull({your.field}) 
or {your.field} = " "
then 0
else if {your.field} in ["1", "2", "3"]
then 42
else if....

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[smeyer56]

Madawc,
That would work except the second line item matches and changes the value to 42 but the third doesn't and resets the value to " ".

Lbass,
ticket #1
-line 1 : candy
-line 2 : pop
-line 3 : coupon #2
-line 4 : brat

line 3 is coupon 2 so I want my variable couponNum to be 2

ticket #2
-line 1 : candy
-line 2 : pop
-line 3 : brat

no coupon is used so I want my variable couponNum to be 0

ticket #3
-line 1 : coupon #1
-line 2 : pop
-line 3 : candy
-line 4 : brat

line 1 is coupon 2 so I want my variable couponNum to be 1

I need to group and sort on couponNum then total ticket with that couponNum for average sale. The report will have details of customer info and sales info based on couponNum.

The 2 problems I am having with this are:
1. assigning couponNum before the details are shown
2. if, as in ticket #3, the coupon is found right away, couponNum is not overwritten by the following line items. I only have one couponNum per ticket and once assigned, I can stop checking. Is there a break/end statement in CR that will stop the if.then loop if found?


Thanks

 

[lbass]

You could create a formula like this {@coupon}:

if {table.field} startswith "coupon" then
right({table.field},1)

Insert a maximum on this formula and then go to report->group sort and choose maximum of {@coupon}. This will sort your groups based on the coupon number. You can insert averages on sales per group. Not sure what other summaries you need if any across groups.

-LB