Concatenating available values

[BryanLane]

I am using Crystal 11.5.8.826

I am creating a "Dead Stock" report that list everything that we are closing out company-wide. The report itself works flawlessly, save for one field. I need to list "Available Locations" that would show which location had it in stock (but not the total available or any details, just list the location number). The location is a character field three character in length with leading blanks, so that what I would consider location 3 is stored as " 3" (blank blank three).

I have a not very easy to maintain solution in the report that dealt with the six locations we had at the time (3, 5, 7, 9, 12, 14) by using 6 Running Totals to determine if the Location was present in the hidden detail rows for each item and 6 formulas to assign the values of 100000, 10000, 1000, 100, 10 & 1 respectively and then another formula to total the values to arrive at a number that I then used a formula to interprete. So for example, if there was dead stock items at location 3, 9 and 12 I would get a total of 100110 (100000+100+10) and the formula would return "3, 9 & 12", based on a series of if-then statements that look like binary, running from 1 to 111111, with the appropriate "then" matching the total of the "if".

Now the location have changed (5 is gone and 51 and 56 have been added, and since we are in growth mode, more are to come quickly). I could rework my formulas to add more values, but I then I would have to start it all over again when we change locations.

In short, what I need is a list of all the unique values in the Location fields from the hidden detail rows for each item that has dead stock (deadst = 1) in one field on the report so that I get a listing that shows, if there is inventory for this flagged item at locations 3, 7, 9 and 51:

Part# Desc Avail_Locations Quantity Inventory_Value
1234 something 3 40 400.00
1234 something 7 10 100.00
1234 something 9 20 200.00
1234 something 51 30 300.00
1234 something 3, 7, 9 & 51 100 1,000.00


My spreadsheet background is clouding my ability to find a database solution....but I know that there has to be an easier may because Starbucks would need one heck of a long formula to do the same thing!

"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[lbass]

Assuming you have a group on part#, you could create these formulas:

//{@reset} for the group header:
whileprintingrecords;
stringvar loc;
if not inrepeatedgroupheader then
locs := "";

//{@accum} for the detail section:
whileprintingrecords;
stringvar loc;
if {table.deadst} = 1 and
not({table.location} in loc} then
loc := loc + trim({table.location})+", ";

//{@display} for the group footer:
whileprintingrecords;
stringvar loc;
if len(loc)>2 then
left(loc,len(loc)-2)

-LB

 

[BryanLane]

Thank you LB!

I had to make two changes when I pasted the formulas into Crystal.

In @Reset I changed locs to loc

then locs := "";

to

then loc := "";

and in @Accum I changed the closing bracket to a parentheses

not({table.location} in loc} then

to

not({table.location} in loc) then


For the vast majority of the part number groups I get the first Location number listed twice (ie 3, 3, or 12, 12, ) in the @Display field and no additional locations shown on any of them. There are a couple dozen groups that have no value displayed in the @Display field. There is no consistency to the blank records other than none of them appear to be a single location, there are some with 2, 3, 4 and 5 locations available.



"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[lbass]

You caught my typos--sorry. Also, please change the accum formula to the following as the trim was interfering with checking for dupes. You want to leave the spaces in at first to eliminate issues with a number being "in" another number:

//{@accum} for the detail section:
whileprintingrecords;
stringvar loc;
if {table.deadst} = 1 and
not({table.location} in loc) then
loc := loc + {table.location} +","; //remove trim()

Change the display formula to the following to eliminate spaces:

//{@display} for the group footer:
whileprintingrecords;
stringvar loc;
if len(loc)>2 then
replace(left(loc,len(loc)-1)," ","")

-LB

 

[BryanLane]

That took care of the duplication issue and the blank records, but no I am getting only the first location followed by a common on every single record.

"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein

 

[lbass]

The accumulation and reset formulas should be suppressed. Only the display formula should display.

-LB

 

[BryanLane]

That did the trick. Now @Display shows the proper locations.

The only remaining issue is that the field displays an ending comma for all records. I changed the last line of @Display from -1 to -2 and it took care of the issue.

Thank you again for your help and for adding another quality item to my growing bag of Crystal tricks.

"The problems we face today cannot be solved by the minds that created them."
--Albert Einstein