Given Invoice#, find original Order#

[Former Member]

Hi,

maybe a simple thing but I can't seem to find any way to do this.

Say I have an OrderId for CartType 'O' that was delivered and invoiced already.

I can find the Delivery document in Webtools by joining on the Shipment/ShipmentDetails table, but I can't find the correspomding Invoice - as it's also just an Order with apparently no link back to even its delivery.

i.e in B1 given a Sales Order, I can find the Delivery Note, from this I can find the Invoice. But there doesn't seem to be any such facilty via Webtools?

Is it possible without having to create and maintain my own lookup table?

thx,

Steve

[Former Member]

Hi Stephen,

The Invoice in WebTools is simply a NPOrder synchronized from B1. There is no link within the WT database from the Order to the Invoice. However, it should be possible to retrieve this link via the B1 database, using something like (untested):

SELECT U_PRX_SID FROM OINV

INNER JOIN INV1 ON OINV.DocEntry = INV1.DocEntry

WHERE INV1.BaseEntry = @synchIDFromOrder AND INV1.BaseType = @orderB1ObjectType

Regards,

Stefan