Return a record based on a value, if not that value, return the record based on another value? 1

[mmck1964]

I hope the subject line helps with my problem.

I have a report where I have grouped by customer number. Some customers have multiple address depending on the addresses specific use. Every customer has an address where {address_type}="XX". Not every customer has an address where {address_type}="CC"

I want to return the address for the customer based on if {address_type}="CC" only, and not return the address that the {address_type}="XX".
But, if the customer does not have an {address_type}="CC", then I do want to return the address where {address_type}="XX".

Currently, I am getting both the {address_type}="CC" and the {address_type}="XX"

I tried and If Then Else formula without any luck. Below is an example of the data in the table:

{Cust_No} {Address} {address_type}
11111 123 Main St CC
11111 456 West Ave XX
22222 9981 North St XX
33333 144 Oak St XX
44444 789 County Rd CC
44444 5211 Fourth Ave XX

Please advise and thank you.

 

[fisheromacse]

i might be oversimplifying, but i would group on customer # (which you already did) then sort by address_type
put the address info in the group header.
if there is a CC record it will show, but the XX will not unless there is no CC as it is only going to show the top/first address per customer group.

 

[mmck1964]

I can see how that would be an option, but I really would like to make the correct choice based in the address_type field.

 

[fisheromacse]

maybe something like this:

Depending a host of other factors which could complicate this, you could use a formula in your details similar to below to find your address and then put the display formula in the group footer and the reset formula in the client group header.


//{@CCorXXAddr}
stringvar addr;

IF {address_type} = "CC"
then addr := {Address}
else
IF {address_type} = "XX"
then addr := {Address}
else
addr := addr;
addr


//{@DisplayAddr}
stringvar addr;
addr


//{@ResetAddr}
stringvar addr := "";
addr

 

[mmck1964]

I think this is close because I see both "CC" and "XX in the detail section, but I always return the address for address type "XX" in the group footer. Also, I did add WhilePrintingRecords in each formula.

 

[fisheromacse]

if you sort them by the type, you can control which appears in the footer.
if the records are sorted A-Z, the last record would be the one visible in the group footer (ie: the XX since it comes after CC)
if you sort the records Z-A, the CC record will come after the XX record, and it will be the one displayed.

If you don't want to (or other report requirements make you unable to) sort the records like that, then, you might be able to alter the formula to something like this:

//{@CCorXXAddr}
stringvar addr;
numbervar cc;

IF {address_type) = "CC" then cc := cc + 1 else cc := cc;

IF {address_type} = "CC"
then addr := {Address}
else
IF {address_type} = "XX" and cc = 0
then addr := {Address}
else
addr := addr;
addr




//{@ResetAddr}
stringvar addr := "";
numbervar cc := 0;
addr & cc