Hello,
How would i link transactionhistory to slcustomeraccount so I can filter sold items by a single account.
Kind Regards,
James
Hello,
How would i link transactionhistory to slcustomeraccount so I can filter sold items by a single account.
Kind Regards,
James
if i recall, the "source" columns in the transaction history table will have l that information
Also consider using SOP not Stock - link SLCustomerAccount to SOPInvoiceCredit, SOPInvoiceCreditLine etc
I have beee furlouged since i last looked at this comment, how would i link these tables when i bring them in theirs no standard link
James, you could try the below:
SELECT *
FROM SOPInvoiceCredit
INNER JOIN SOPInvoiceCreditLine ON SOPInvoiceCreditLine.SOPInvoiceCreditID = SOPInvoiceCredit.SOPInvoiceCreditID
INNER JOIN SOPOrderReturnLine ON SOPOrderReturnLine.SOPOrderReturnLineID = SOPInvoiceCreditLine.SOPOrderReturnLineID
INNER JOIN SLCustomerAccount ON SLCustomerAccount.SLCustomerAccountID = SOPInvoiceCredit.CustomerID
INNER JOIN SOPStandardItemLink ON SOPStandardItemLink.SOPOrderReturnLineID = SOPOrderReturnLine.SOPOrderReturnLineID
INNER JOIN StockItem ON StockItem.ItemID = SOPStandardItemLink.ItemID
Not the neatest but would allow filtering on Items & Customer in a period
You don't need SOPOrderReturnLine in that query:
FROM SOPInvoiceCredit INNER JOIN SLCustomerAccount ON SOPInvoiceCredit.CustomerID = SLCustomerAccount.SLCustomerAccountID INNER JOIN SOPInvoiceCreditLine ON SOPInvoiceCreditLine.SOPInvoiceCreditID = SOPInvoiceCredit.SOPInvoiceCreditID INNER JOIN SOPStandardItemLink ON SOPInvoiceCreditLine.SOPOrderReturnLineID = SOPStandardItemLink.SOPOrderReturnLineID INNER JOIN StockItem ON StockItem.ItemID = SOPStandardItemLink.ItemID
You may not need StockItem either as SOPInvoiceCreditLine has ItemCode and ItemName.
You will (probably) need to filter on "SOPInvoiceCredit.SOPInvoiceCreditTypeID = 0" (Invoices only) and "SOPInvoiceCredit.DocumentStatusID =2)" (completed)
How would i attach a customers order no like > invoice number | customers order no | vat price
*Community Hub is the new name for Sage City