Creating Profile type report that hold fields with multiple values

[#1]

Really hoping someone can please help me out as I am very new to Crystal Reports.

We use Maximizer CRM and we have been in need of some custom reports to rule out risk for regulators. I contacted Max and they suggested the only possible way is to create through Crystal. Its been almost one month already and I still cannot for the likes of me get this report operating properly. I have been inside and out on all sorts of forums, posted topics but no luck! So I will give it one more attempt in hopes that one of you geniuses can show me the way.

In Maximizer CRM there is date, numeric, alphanumric and table. Our table fields items can be set to either single value or multi-value. So in crystal i did a default join of Client.tbl and the user-defined fields from view and joined the client id and contact number from all the view fields to client table. See Image:

and I have dragged all the relevant fields in rows (in details section) rather then columns because we would be reporting on more then 1 record at a time. My problem is - If there is a table with multiple items selected (values), the records triple in count and it will show the same record over and over with just single field value changing at a time.

The formula field you see in the image is from when I posted a discussion and Abhilash assisted me by providing the formulas I should add:

1) Create a formula with this code and place this on the Details Section:

whileprintingrecords;

stringvar s := s + {field_with_multiple_values} + ", ";

2) Next, move all the fields (except the formula field above) from the Details section to the Report Footer

3) Create a formula with this code and place this on the Report Footer. This field would replace the existing field that contains multiple values:

whileprintingrecords;

stringvar s;

Left(s, len(s)-2);

This method is not working out for this type of report. When I add the formula Crystal is still counting my 2 records as 5 records but I can only view it as a single record and the multi-field has all the values for both records and displaying as a single record. See image:

Can anyone please assist and advise where I am going wrong?

-Jared

[former_member292966]

Hi Jared,

Thanks for taking down memory lane that is Maximizer.  Nice to see their table structure hasn't been simplified in the last 20 years.

If I understand what's happening, you should only see 2 records and not 5.  That means your joins are creating duplicate records.  For now I'm going to skip over trying to optimize your query because I still have bad dreams of linking Maximizer tables.

There are a couple of ways to work around the duplicates, one is to create a group and instead of having your formula in the Detail section, put it in the Group Header.  The question is what would you create your group on that would get you a unique record?

If you know where the duplicates are coming from, create a Record Selection Formula that will remove the duplicates.

There is also the menu option Database | Select Distinct Records.  I've never really had success with this one but there's no harm in giving it a shot.

I would have you try and find which table or combination of tables is generating the duplicates but that requires playing with your links.  Normally I'd start by adding one table at a time and dropping one field onto the report.  If it doesn't repeat then add another table and field and repeat until you get your duplicates.  Once you know where they are coming from then you can either drop that table from your query or create a selection formula that removes the duplicates.

Good luck,

Brian

[Former Member]

Hi Brian,

Thanks for the motivation with Maximizer lol. I know exactly which field causes duplicates, its a table field with 2 items selected (has 2 values). All other fields are fine cause they are all single value but as soon as I add this specific table field with multiple values, the record count doubles. 

All i really want to do is have that field display the two values rather then have it create a whole new record just because of the second value. I have tried grouping but same thing happens, it just creates an additional record where the second value would be seen. I have used a_myudf_field view to the client.table and joined client id to client id and contact number to contact number and I have also selected the distinct records but no luck.

Do you know of a formula I could try to have it display both values in that fied without is creating an additional record?

I am pulling my hair out on this one!

-Jared

[former_member292966]

Hi Jared,

Create 2 groups:  1 is on the client and the second on the duplicating record.

Move everything from your Report Footer into Group Footer 1 which is your Client.

Your formula that is building the list of values, move into Group Header 2.  The formula that is displaying the list will go into Group Footer 1 with the rest of the data from the report.

Because the group appears only 1, the formula will run once per group instead of for each record.  This will get you what you need.  Ignore the record count but as a santiy check, do a Group Count on Group 2 to get the number you want.

Good luck,

Brian