record selection and placement 1

[dh42891]

Our demographic table is obnoxiously formatted. It basically has three important fields: Client_Name, Data_ID, and Data_Value. The Data_Value field has all the demographic info- phone numbers, contact names, address, zip codes. Each type of data is identified in the Data_ID field by a number. For example, every phone number in the Data_Value field is identified in the Data_ID field by a 9; each Zip Code by an 8, and so on. This produces a table where every 9 records is associated with a client- records 1-9 belong to Client A, 10-18 belong to Client B, and so on.

I've got an account balance reporot that I want have show the client name, phone number, contact name, and balance. I can SELECT Data_Value WHERE Data_ID = 9 to get phone numbers by placing a column that shows the Data_Value. But, if I SELECT Data_Value WHERE Data_ID = 9 or Data_ID = 2 I get two records for each client. I want to have a seperate column for each phone number and for each contact name.

In short, I want this:

------------------------------------------
Client | Contact | Phone# | Balance |
-----------------------------------------
Client A | Joe | 123-4567 | $132.00 |
-----------------------------------------
Client B | Bob | 674-7465 | $784.00 |
------------------------------------------


But what I get, is this:

----------------------------------
Client | Data_Value | Balance |
----------------------------------
Client A | Joe | $132.00 |
----------------------------------
Client A | 123-4567 | $123.00 |
----------------------------------
Client B | Bob | $784.00 |
----------------------------------
Client B | 674-7465 | $784.00 |
----------------------------------

Does anyone have any ideas on formulas to try to basically trick CR into thinking each Data_ID is a different field? I really appreciate anyone helping out. Sorry it's long and probably confusing,

dylan
CR 8.5
Pervasive SQL, ODBC

 

[synapsevampire]

Each Data_ID meaning each Data_Value field?

And I assume that the 132 vs. 123 in the balance field is a typo, they are intended to be the same?

Group by the client (Insert->Group)

Now place the Data_Value in the details section, right click it and select Insert->Summary->Maximum for the group level

Do the same thing and select the Minimum.

The minimum will have the phone, the max the name.

Place the balance field in the details and select Insert-Summary->Maximum or Minimum

Now display the fields in the group footer.

Print out 300 copies of the report, scatter them around the DBA's desk and ignite them.

-k

 

[fredp1]

Hi Dylan,

Create formulaes for every data type.
e.g.
{@PhoneNumber}
if Data_id=9 then Data_Value

{@Zip Code}
If data_id=8 then Data_value
etc
Group by client
place each of your new fields in the detail section and the balance field.
Now for each formulae click Insert, Summary and use the maximum function and put it in the group section that was created.

Suppress the details and your pretty much done

If the DBA survived the previous burning, try again!

Fred

 

[dh42891]

Fred,

I gave your explanation a shot and it worked like a champ. Thanks a lot!

Side question: I know there is a function to change text to upper or lower case but what about proper case? Can I change FRED JONES to Fred Jones? FRED is one Data_Value and JONES is another, so I use two formulas like you suggested to pull out the full contact name. I suppose I could have Crystal grab the first letter of the field and then convert the remaining letters to lower case. Anyway, I'll do that unless there is a function for proper case or someone knows of a better way. Thanks again,

dylan

 

[fredp1]

Hi Dylan,

Its always best to start a new thread... even though its tempting to continue with this one

There is an existing thread on Proper Case

http://www.tek-tips.com/viewthread.cfm?SQID=568676&SPID=767&newpid=767&page=1

I'd go the UFL route(less coding). Just install it on the c:\WINNT\Crystal directory of every pc you want to use it on.

Fred

 

[lbass]

The only caution is that if you are distributing the report to users who do not have the UFL installed they might not be able to view the report--from what I've read...which I believe is why Fred is recommending installing it on every pc.

-LB

 

[dh42891]

Okay, I'll read through that thread. Thanks guys,

dylan