Hi All,

New to the forum and this is my first post. I'm learning Crystal/SQL on demand so to speak. I'll try to be clear as possible with my semi/newbie question.

I have an existing Crystal Report that is our Purchase Order and it "hangs" off of or is aliased from a 3rd Party Publishing biblio db.

Currently the "Vendor" area of the PO is pulling in vendor name, address, etc from our vendor table. That's great. However some of our vendors may be the ones getting the PO (and need to be on the PO) but a different "affiliated" company may be the "Payee."

When we set up a new vendor in our db we have the option of linking another vendor to the new entry as the "payee."  The primary vendor gets a unique "vendorkey" and in the case of the assignment of a "payee" a unique value is placed in the "paytovendorkey" field for the vendor record. That "paytovendorkey" is actually also a "vendorkey"...that of the "payee."

Utilizing some space on the canvas under the "Vendor" area of the PO I need to create either a formula field or subreport (whichever is easiest/best) that will (when applicable) pull the "Payee" vendor name, address etc. from the same vendor table that "Vendor" is pulled from. It would be first looking at the record for the "Vendor" in the vendor table to see if there is a key in the "paytovendorkey" field.....and then referencing the vendor table for that key in the "vendorkey" field to pull that vendors record.

Make sense?

Something like (but not complete of course)

if gpo.vendorkey=vendor.vendorkey
then vendor.name=vendor.paytovendorkey=vendor.vendorkey

I know this is incomplete and not correct but hoping my explanation of what I'm trying to do will be enough for a pro to help!

:)

Thanks in advance.

Hmmm..not sure I'm able to apply this to what I'm doing but as mentioned I'm a newbie.

Maybe if I expand on this or am more specific.

Basically (insofar as vendor info is concerned) this Crystal Report is fed by our PO table (gpo).  So a record (an individual PO) in our GPO table has among other things the "primary" vendor info (name, address, etc) for the specific Purchase order and this is includes the vendors "vendorkey."

(When a PO is created by the user and thusly a record in the GPO table it pulls the primary vendor info from our VENDOR table and it becomes part of the record.)

What I've done is actually add the VENDOR table to the Crystal Report joining to the GPO table on vendorkey.

When a PO is printed from our system for a given ISBN (we're a book publisher) the vendor that was assigned to the PO by the user populates in the "Vendor" part of the PO. Name, address, etc.

So what I think I want to do is to produce a section for a "Payee" (which is just another vendor associated with the primary) by referencing the record for the PO...specifically the "vendorkey"...look it up so to speak in the newly added VENDOR table....see if there is a value in that records "paytovendorkey" field and then use that key (which is actually a vendor key) to look up this second vendor in the VENDOR table and populate it's name, address, etc.

Hope that clarifies rather than obscures what I'm trying to do.




Edited by pjewett - 25 Mar 2013 at 5:31am

The vendor table wasn't originally included in the report (vendor info was inserted from the it into the GPO table by a db process) I added it knowing I needed to but that's as far as I got.

Do I need to add it a 2nd time or can I make join to the gpo table from the 1st instance of the vendor table...gpo.vendorkey to vendor.paytovendorkey

you should have a vendor table and a vendor_1 table, correct?