CR 9 - Best way to query?

[Beanz]

Hey,

I am using Crystal reports 9 and am quite new at it so any help is appreciated!

I have written an application using VB for distributing our products and I use CR for generating delivery notes and invoices etc.

I have the reports all setup and working but the problem is that the delivery/invoice address in SOME cases is different to the actual company making the order. In the Db I deal with this by using a pointer field (ptDealerID) to the correct address details.

Therefore the order/invoice relates to one record but the delivery address comes from another. What is the best way of implementing this via CR?

Should use parameter fields/Selection formulas based on a big ass query or is there another (better) way of doing it?

For example, at the moment the query pulls out the ordering/invoiced companies details along with any delivery company details. This results in a big query that may or may not return a different delivery address, the formulas then determine which fields to display.

Give me a shout if you need more information, and again, any pointer are greatly appreciated!

Danny

 

[synapsevampire]

I'm not sure what there is to implment in CR.

Generally there are types of addresses associated with an order processing system in which you can select the type, as in Bill to, Ship To, Contact, etc.

You 've shared nothing technical here other than CR 9, so it's difficult to asses how you should best extract data, but I suspect that your architecture is poor, hence there's some question of how to extract data.

This "pointer field" referenced must mean that it's a Foreign Key to another table. So add that table and join them, where is the difficulty?

Consider posting example data and expected output rather than text descriptions of the environment.

-k

 

[Beanz]

Sorry for the vagueness of my post!

What information should I post, the table layout of the database???

Cheers
Danny

 

[synapsevampire]

As stated, example data (yes, the tables and an example of the data) with expected results (this would be what the eventual product looks like) would be a great start, also the database and version in case there's a server side process available.

-k

 

[Beanz]

The company table is structure is:

CompID, ptCompType, ptCompMaintStatus, CompCode, CompName, Address1, Address2, Address3, Address4, ptCounty, PostCode,
CompTel, CompFax, CompDX, CompEmail, CompURL, IsDealer, UseZipCompForForms, ptCompSize, ptCompStatus, ptPayment, DateAdded, AddedBy

The invoice is related to the order via an FK called ptOrderID. The order is related to the company via an FK called ptCompID and alternate address is related via an FK called ptDealerID on the CompID also. If ptDealerID is NULL or blank then the order/invoice should goe to the company it was raised against otherwise it should goes to the company in ptDealerID.

Hope that is useful??

 

[synapsevampire]

Ahhh, I think I see your dilema, though example data means data, not table structure alone, and you only supplied one of the tables involved, and expected output means what the output of the report should look like.

This might be best served by using a query that uses an isnull() or whatever the equivalent is for your database in the where clause to handle the join, as in:

where {table1.field} = isnull({table2.field1},{table2.field2})

You can modfiy the where clause of reports, or since you're using CR 9, create the entire SQL to properly extract the data and use the Add Command to paste it in.

-k