One to Many relationship where the one may not exist

[lnusbaum]

Hello;

I'm trying to write a report (a vehicle inventory for our fleet) and I think the answer to this question lies along the lines similar to thread 767-284371. However in my case I only want to see what resides in the EFLDVALS.FLDVALUE field refereneced by a EFLDVALS.FLDNO field in the same table (EFLDVALS).

This table (EFLDVALS) is a child table and I'm using a left outer join from the parent table. I don't want the results of the EFLDVALS table to restrict whats being pulled from the parent table. There are other values in EFLDVALS table but I'm only interested in retreiving a certain field value, in this case its field 3 . So in other words I would like to see the value of EFLDVALS.FLDNO = 3 and then put the corresponding value EFLDVALS.FLDVALUE in my report.

I'd like to be able to do this without having the report or display all the other values of the EDFLDVALS table along with duplicate information from the parent table on my report. And one other twist, being that we are populating the data base, field 3 may or may not exist. If it does exist I would like CRW to put the corresponding value in my report. If it doesn't exist I would just like it to leave a blank space there.

Any tips/advice/help would be greatly appreciated.

Thanks

 

[synapsevampire]

When you say that the field may not exist, I hope you mean that it is null or blank, otherwise we have problems.

What you're describing is a conditional column with a LO join.

You can either do this on the database side (depending upon the database), or within crystal.

From the database you would either create a SP/View to do this in advance(best), or you might use a SQL Expression to return either the value or blank based on some criteria.

From within Crystal you would just create a formula on that field and based on te criteria, return either blank or the value.

You can turn on Select Distinct Records to help resolve the dupes.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[lnusbaum]

I was mistaken I am currently using a Left Outer join, not the right. And your right as well, the field does exist on every record however its either populated or null/blank. I did try using an if then statement, however it evaluates every entry in the child table and that causes duplicate lines on the report. would a sub-report be advisable to dig out only the field that i want? or would something in the group selection area be easier to use? The database is a SQL PRObase data base.

 

[synapsevampire]

Please define what you expect to see in the results, with a little sample data and what is ultimately displayed.

It sounds like you want a distinct parent-child LO join, with a conditional column based on EFLDVALS.FLDNO = 3.

Your DB's SQL may support a conditional column, in which case you should be able to use a SQL Expression to construct this on the database side and improve performance.

Within CR, you might return the distinct rows in the join and use a formula where EFLDVALS.FLDNO = 3 to limit the rows as required for your value(s).

Subreports are always a last ditch effort for me because of the performance hit.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[lnusbaum]

What I'd like to see is this (in columns):

Route # location Weight VIN State Lic No. Division..

The route number is what is located in field 3 of the EDFLDVALS table. I would like one line per vehicle.

At this point whenever touch the EDFLDVALS table, with or without a formula, I get one line for each of the values that are in the EDFLDVALS table along with duplicate information from the parent table.

Other values that are stored in the EDFLDVALS table as an example are: field 1 stores the driver, field 2 stores contact information, field 3 stores the route number, field 4 stores engine information, etc... currently i can get up to 5 lines per vehicle when I only want one line per vehicle (that which contains the value in field 3 or a blank space if the value is null or blank).

Thanks again for your assistance.