Check for record

[bpeirson]

How can I have Crystal check to see if a record exists in a table.

We have two tables of information. One is intended for long term stock items, table A. The level of detail is quite high in this table. The other is intended for transitory stock items, Table B. This has less detail because the items included will likely be one time sales only.

Table C is a list of transactions, when the front end populates this table it asks if new items should be added to table A or table B.

I would like to build a report which will process transactions in table C and include information from table A or B as required.

I have tried the following but if the item is not in table A then nothing will print to the form. By nothing I mean none of the other records which are not associated with this formula (client name, address etc).

if {TableA.AltPartNo} = "" then {TableB.PartNo} else {Table.AltPartNo}

If the item is on Table A then everything works fine.

Thanks, Brad

 

[lbass]

How are you linking the tables? Does Table C contain all parts that are found in Tables A and B? If so, then use a left join from Table C to Table A and to Table C.

Then you could use a formula like this:

if isnull({tableA.AltPartNo}) then
{TableB.PartNo} else
{Table.AltPartNo}

-LB

 

[bpeirson]

lbass, thanks again. You helped me out last week on another issue. What I was looking for was the isnull()statement.

When I searched for "null" in the Crystal help I didn't see how it was implemented. The search feature highlighted every instance of null on the page except the one place where it says "IsNull".

Thanks again, if I have more problems I'll post them as new threads.

Brad