Tough 1: Need to parse field to choose correct table

[ChrisCastle]

In my situation I have a field in a table that is labeled with a table codeword plus it’s unique identifier. For example if the column is FAM1444 it needs to use the “Family” table and record number 1444. Or, If the column is MAR432 it needs to use the “Marriage” table and record number 432.

Parsing and using a select case in ASP is no problem but I don’t know how to set this up so crystal reports can parse and get data from the right table.

 

[Ngolem]

I would assume you have a field in the table which contains the value of the recordnumber

One way to do it is to create subreports that will return the values that you want.

I would probably create as many subreports as there are "codes" ...each subreport would use the table associated with the value of the code. Then have the recordnumber stored in a formula that can be linked by each subreport.

So you haven't really described your report so I don't know what information you are returning....it may that you could display the information of the subreport directly or return variable values with the use of shared variables.

If you are using Shared variables the display of the results are done in sections that follow the position of the section containing the subreports. These subreports usually are invisible

so as I see it you would have a main report with associated subreports which may or may not be run depending on the parsed code word and aeach subreport linked to the main report by a parsed out recordnumber



Jim Broadbent

 

[ChrisCastle]

Okay.. I guess it would help a little if I describe this part of the report. I need to list names. For a grim example I have a report that lists palm bearers for a particular client.

Needs to do this:

Joe Smith’s Pallbearers
Jan Smith (first_name, last_name from marriage table record# 244)
Phil Smith (first_name, last_name from relative table record# 23)
Mike Smith (first_name, last_name from relative table record# 54)
Bob Larson (first_name, last_name from advisor table record# 7)

I have a Pallbearer table where the client looks something like this:
ID Client_ID Pallbear_ID
1 45(joe) mar244
2 45 rel23
3 45 rel54
4 45 adv7

Hope that clears it up and I hope this is possible. Thanks in advance.

 

[synapsevampire]

Crystal won't change datasources based on a tables value.

Jim's solution would work if you parse the field in a formula, and then use that formula to link to various subreports, but this would mean that data will always be returned as subsets of data, and requires that you have a finite set of codes, which isn't a realistic long term approach.

You might use a Stored Procedure to do this, depending upon your database, but the simplest solution is to take a white hot cheese grater to your DBA and then build a normalized database

-k

 

[Ngolem]

I would prefer SV's approach of combining all your smaller tables into one that you could draw on but If that isn't possible My approach would work if there were a reasonable number of these smaller tables to work with (probably 5 or less)

Is this something like a church database of some kind?

I'm not sure what else to say....I would probably use shared variables to bring back the data you want....from the looks of it you want only a last name.

Show a report in final form and where the data comes from and I'll see if I can be of more help

Jim Broadbent

 

[lbass]

Everyone's on a really different track here from how I'd approach this, so maybe I'm missing something important, but just in case this helps...

If we can assume that the clientId is represented in each of the tables, then for the Joe Smith example, {Pallbearer.ClientID} could be left joined to {Marriage.ClientID} and left joined to {Family.ClientID} and left joined to {Advisor.ClientID}.

Then create a formula {@whichtable}:

if left({Pallbearer.Pallbearer_ID} = "Mar" then
(if{Marriage.Record_ID} = val(mid({Pallbearer.Pallbearer_ID},4)) then {Marriage.First_Name}+" "+{Marriage.Last_Name}) else
if left({Pallbearer.Pallbearer_ID} = "Rel" then
(if{Rel.Record_ID} = val(mid({Pallbearer.Pallbearer_ID},4)) then {Rel.First_Name}+" "+{Rel.Last_Name}) else
if left({Pallbearer.Pallbearer_ID} = "Adv" then
(if{Adv.Record_ID} = val(mid({Pallbearer.Pallbearer_ID},4)) then {Adv.First_Name}+" "+{Adv.Last_Name}) else ""

I can't really test this, but it might be worth a try.

-LB

 

[Ngolem]

**************************
If we can assume that the clientId is represented in each of the tables, then for the Joe Smith example, {Pallbearer.ClientID} could be left joined to {Marriage.ClientID} and left joined to {Family.ClientID} and left joined to {Advisor.ClientID}.
**************************

We cannot really daisy chain these together, can we?

{Main Report.clientID} ---> {Pallbearer.ClientID} ---> {Marriage.ClientID} ---> {Family.ClientID} ---> {Advisor.ClientID}

SInce if there is data in the Advisor table but nothing in the Pallbearer table we won't see Advisor data

If we have the tables linked

|---> {Pallbearer.ClientID}
{Main Report.clientID} ---> {Marriage.ClientID}
|---> {Family.ClientID}
|---> {Advisor.ClientID}

Won't we get a bunch of duplicate records



Jim Broadbent

 

[lbass]

Jim--I didn't mean a daisy chain. I meant (similar to your second example):

Pallbearer.ClientID--------------->Marriage.ClientID
! ! ! \>Rel.ClientID
!
!---->Adv.ClientID

I can't really test this, but if the only field that uses the right tables is the formula field I suggested, it might not create duplicates.

-LB

 

[lyanch]

I am all for the cheese shredder to the DBA... (who lets these people do things like this?????)

Lisa

 

[Ngolem]

lBass - maybe it won't...I cannot test either but still you will have to retrieve all of the values then with a formula sift through it all....by using the subreport approach you go strictly for the data that you want and ignore the rest.

Lyanch - I have Ginzo Knives that work well too

Jim Broadbent