How to do data filtering in macro VBA?

Question

Hi all, 
 how do I do data filtering in macro? Lets say I want to filter by invoice number in OE0520

murni · Accepted Answer

Thank you for the tips. So I ended up using OE0420 to get filter for Invoice Number and using FilterCount instead of Browse function 
 Function ValidateInvoiceNumber(invoiceNo As String) As Boolean
 
 Dim uniq As Boolean
 
 Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
 Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)

 Dim mDBLinkSysRW As AccpacCOMAPI.AccpacDBLink
 Set mDBLinkSysRW = OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)
 
 Dim temp As Boolean
 Dim INVheader As AccpacCOMAPI.AccpacView
 Dim INVheaderFields As AccpacCOMAPI.AccpacViewFields
 mDBLinkCmpRW.OpenView "OE0420", INVheader
 Set INVheaderFields = INVheader.Fields
 
 If INVheader.FilterCount("INVNUMBER=" & invoiceNo, 0) > 0 Then
 Logger.LogDetails "Invoice Number=" & invoiceNo & " already exist."
 ValidateInvoiceNumber = False
 Else
 ValidateInvoiceNumber = True
 End If
End Function

Shane - Ascend Business Solutions · Answer

You can filter by last invoice number, but this is not recommended as there can be a one-to-many relationship between an order and invoices. The approach I'd go with is to query invoice detail lines by Order (OE0400) - also, I'd suggest ensuring your order for the Browse is set to 3 - Order/Ord Dtl/Dtl

Sage 300

How to do data filtering in macro VBA?

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING