Mulitple rows for a record 1

[Crystalguru]

Hello,

I am writing a report in version 8.5 and need help with the following.

The report is for Apartments.
Bld# Unit# Unit Type Rent$ Amenities

Pretty simple, except that an apartment can have multiple amenities. Which comes out looking like this:
Bld# Unit# Unit Type Rent$ Amenities
1 101 A1 500.00 B
1 101 A1 500.00 G
1 101 A1 500.00 Y

So far this is what i have done:
Grouped by Unit Type: A1
Grouped by Unit #: 101
500.00 B
500.00 G
500.00 Y
But I still get a repeat of Rent$ and Amenities. Some apartments can have over 10+ amenities.

Is there anyway to get all the amenities on one line in the report?

thanks

 

[heppme]

Create a formula for each amenity. (Examp. Formula for Amenity Type 'B': If Amenities = 'B' then 'B' else null)
Group on Rent$ and place all fields in this grouping for display.
Grouped by Unit Type: A1
Grouped by Unit #: 101
Grouped by Rent$
500.00 B G Y

 

[Crystalguru]

What happens if you have a extremeley long list of amenities? Is there a way to have crystal scan through and get all the values then display them? Just a thought...not sure how to program that.

thanks again!

 

[Naith]

This sounds like it should be a cross tab. Is there some reason why it can't be?

 

[Crystalguru]

For simplicity, the report example contains only a few fields from the actual report. The example would make a great cross tab. But the "real" report contains dates, days vacant, etc.. which wouldn't work with a crosstab. Good thought though.
thanks

 

[Naith]

Well, I don't know how extensive your real life situation is in comparison to your example, but the only non-cross tab way I think you can reasonably achieve this is to make groups according to Slizzo's post.

Suppress the header and details section.

In the footer, place all your details information with the exception of your amenities.

In the details, place the following formula:

WhilePrintingRecords;
StringVar Amenities;

Not OnFirstRecord;
If {Amenity} = Previous({Amenity})
Then Amenities := Amenities
Else 
If Length(Amenities) >= 245
Then Amenities := 'Too Many Amenities'
Else
Amenities := Amenities + {Amenity};

Call the Amenities variable in the rent group footer, and reset it using

WhilePrintingRecords;
StringVar Amenities := '';

in the rent group header.

Haven't tested, but if you don't get on with it, let me know.

Naith

 

[Crystalguru]

Interesting! Only one problem, it doesn't know when to stop. I get the following:

Bld# Unit# Unit Type Rent$ Amenities
1 101 A1 500.00 BGYBGYBGYBGYBGYBGYBGY

Anyway to get this to stop repeating? Good so far!

thanks

 

[Naith]

What's the deal with all those BGY's? Are they valid amenities but just not resetting for each group? That is to say, is the output correct for the first group?

Did you reset the formula in the group header like I suggested?

Naith

 

[Crystalguru]

I said the same thing...what's the deal.

Yes, I verified that the amenities are correct. One aprartment unit may have multiple amenities (B=basement, G=garage, Y=yard, etc.)

The formula is resetting for each group.

But for each unit the formula continues BGYBGYBGY. Is it repeating for each row in the database . (?). Do I need a stop of some kind?

thanks

 

[Naith]

The reset is the stop.

Try changing your reset so that it's

Amenities := ' ';

. Also, and I can't stress this enough, make sure that your reset formula starts off with

WhilePrintingRecords;

.

Also, verify that the reset is in the group header, not in the group footer (this is important).

Naith

 

[Crystalguru]

I tried changing my reset to Amenities := ' '; (removed the stringvar) Gave me an error 'remaining text does not appear to be part of formula'.

The reset is in the GH. And does start with whileprintingrecords.

I tried moving ALL the fields around to see what data I would get(sometimes it works...). I keep getting the repeating amenity codes. (BGYBGY).

Such a small thing can cause such pain...

thanks

 

[Naith]

Post each of the formulas which impact the Amenities variable, with a little addendum stipulating where each is placed.

Thanks,

Naith

 

[Crystalguru]

Here are the formulas and their locations:

AMENITIES_1 (GF3)
whileprintingrecords;
stringvar Amenities;
Amenities;

AMENITIES_2 (details)
WhilePrintingRecords;
StringVar Amenities;

Not OnFirstRecord;
If {AMENITIES.AMENITY_CODE} = Previous({AMENITIES.AMENITY_CODE})
Then Amenities := Amenities
Else
If Length({AMENITIES.AMENITY_CODE}) >= 245
Then Amenities := 'Too Many Amenities'
Else
Amenities := Amenities + {AMENITIES.AMENITY_CODE};

RESET: (GH3)
WhilePrintingRecords;
StringVar Amenities := '';


Let me know what you think...

thanks for your help.

 

[Naith]

It should be:

If Length(Amenities) >= 245

not

If Length({AMENITIES.AMENITY_CODE}) >= 245

I just stole your formulas and used them in an adhoc report of my own, and everything looks pretty good.

Confirm that this anomaly occurs in the very first group, or state if the first group is correct.

If the first group is affected, and your data isn't terribly sensitive, leave your email and I'll take a look at the report.

Naith

 

[Crystalguru]

hmmm...still repeating. Corrected formula to read
If Length(amenities)>=245

The anomaly occurs for each record in the group. The data is correct for each record but continues to repeat. It seems to be causing another problem with my counts and subtotals.

Thanks!

 

[Naith]

Repetitive Amenities was down to grouping style.

But the formula given by me was very holey.

It should have been:

WhilePrintingRecords;
StringVar Amenities;

If RecordNumber = 1 
Then Amenities := {AMENITIES.AMENITY_CODE}
Else
If RecordNumber > 1 Then
If {AMENITIES.AMENITY_CODE} = Right(Amenities,1)
Then Amenities := Amenities
Else 
If Length(Amenities) >= 245
Then Amenities := 'Too Many Amenities'
Else
If InStr(Amenities,'Too Many Amenities') > 0
Then Amenities
Else
Amenities := Amenities + {AMENITIES.AMENITY_CODE};

Sorry about that...

Naith

 

[Crystalguru]

Thanks Naith! You have been a tremendous help! Your detailed instructions have made my day!

see you

 

[Crystalguru]

Sorry to bother you so soon, AGAIN...but more questions.

Would this formula work for other types of fields? Other than code fields? Such as a name field? Just came across a request for a report that list current residents and applicants for the same unit #....both reside in the same table(residents).

One last question...can a condition be placed on a variable?
For example:
stringvar resident := if resident.active=Y then resident_name

stringvar applicant := if move in date null then applicant name

These variable are hard for me to grasp!~

later

 

[Naith]

Yes to everything really, but with some caveats...

Yes, the formula will work with other type of fields. Numbers, of course, would have to be converted to strings, otherwise Variable+{NumberField} would invariable return the total the two fields rather than a concatenated string.

The thing to remember is the 254 character limit on all Crystal versions prior to 9. This formula works well with your data because your code field is only one character long. But if you were working with something like a varchar2(40) which was often at limit, you'd only be able to string 6 of those suckers together before your report fell over, or activated the error handler.

Secondly, yes, you can apply conditions to variables. F1 will give you a good heads up on exactly how you can treat variables, but they're very flexible on the whole. The only thing is you just need to check your syntax:

stringvar resident := if resident.active=Y then resident_name

would become

if resident.active='Y' then stringvar resident := resident_name else resident;

and

stringvar applicant := if move in date null then applicant name

is

if isnull(move_in_date) then applicant := applicant name else applicant;

Naith

 

[Crystalguru]

Your so good to me! Thank you once again. I will play around with the variables and see if I can get them working.


see you