SOLD PERIOD

Hello,

How would i link transactionhistory to slcustomeraccount so I can filter sold items by a single account.

Kind Regards,

James

  • 0

    if i recall, the "source" columns in the transaction history table will have l that information  

  • 0

    Also consider using SOP not Stock - link SLCustomerAccount to SOPInvoiceCredit, SOPInvoiceCreditLine etc

  • 0 in reply to Geoff Turner

    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

  • 0

    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

  • 0 in reply to Steve Brown_1

    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)

  • 0 in reply to Geoff Turner

    How would i attach a customers order no like > invoice number | customers order no | vat price