Custom Item# Finder for O/E Detail screen 1

[sebik2000]

Hi,

Does anybody know if it is possible to assign a different query/view to the item# finder in O/E Detail screen (F5)?

We created a pricelist for each of our customers (price list ID = Customer ID). Also we set in the Customer screen Customer Pricelist to pricelist code.
When we enter an order for the customer and we select an item (F6 in order detail screen) that is not on their pricelist we get a warning and the record is added with a zero price so the user will need to pick a different item.

Can the item finder show only the items that are in the pricelist for the customer pricelist code (OEORDH & ICITEM & ICPRIC filtered by OEORDH.PRICELIST)?

If not, can a form macro be called from within the screen (by hitting F3 or Ctrl M) and on close to fill the item# field on the screen like the finder does?


Thank you,

Sage Accpac 500 ERP (Version 5.3B)
OE 5.3A SP2
IC 5.3A SP2
Database: Pervasive V8.6

 

[Jay Converse]

At the top:

Public WithEvents lstdetails As AccpacFldList.AccpacViewList

In the UI App Opened:

Set lstdetails = .UIAppControls("avlOEORDDdetail1").GetControl

Then:

Private Sub lstdetails_OnBeforeFinderStart(ByVal bstrColName As String, ByVal bstrFieldName As String, pvBookMark As Variant, pFEditCtrl As Object, pStatus As AccpacFldList.tagEventFndStatus)

pFEditCtrl.ViewFinder.Filter = "{your criteria here}"
End Sub

 

[sebik2000]

Thank you for the fast replay tuba2007.
From what I understand from the code you wrote
the new macro form will include the complete Order Entry screen and will permit me to access by vba code the order detail finder.

My problem is pFEditCtrl.ViewFinder.Filter = "{your criteria here}" will let me filter through the records that filter offer (in Order entry Detail case the inventory view).
The records I need to show in the Finder are made by a query based on ICITEM & ICPRIC or a view if Accpac have it.
(SELECT ICITEM.*, ICPRIC.PRICELIST, ICPRIC.DESC
FROM ICITEM INNER JOIN ICPRIC
ON ICITEM.ITEMNO = ICPRIC.ITEMNO

If I can change somehow the view the finder points to the pFEditCtrl.ViewFinder.Filter = “PRICELIST=’” & afeOEORDHpricelist3.Text & “’” will work perfect.
Does pFEditCtrl.ViewFinder have a propriety that I can set to a query?
Does ACCPAC have a view close to my query?
In the worst situation I can work with a view that have ITEMNO, DESCRIPTION and PRICELIST. From the ICITEM table I need only the COMMENT1-4 fields but if I have to I can live without them.


Sage Accpac 500 ERP (Version 5.3B)
Database: Pervasive V8.6

 

[Jay Converse]

You can't use a query, and you can only use fields that are in the ICITEM table. The program that I snipped that code from has this:

pFEditCtrl.ViewFinder.Filter = "CNTLACCT != FGASM"

because I didn't want items in the FGASM control account to display.

 

[DjangMan]

I've built a custom lookup for a client that will let you run a query for the finder. You have to have a custom OE screen but once installed and running you can make many of the adjustments to the query yourself.

The client needed to be able to look up tires by size and by vendor and they needed to the see customer's price at the same time. So I created the 'SuperFinder'. It plugs in reasonably easy and hits the database via ODBC so you can make it run very quickly with indexing.

If you're interested in talking more you can contact me at:

http://www.silentdsoftware.com.

 

[sebik2000]

Using tuba2007's code I add the following:

Private Sub lstdetails_OnBeforeFinderStart(ByVal bstrColName As String, ByVal bstrFieldName As String, pvBookMark As Variant, pFEditCtrl As Object, pStatus As AccpacFldList.tagEventFndStatus)

Dim sCustomerPricelistCode As String

sCustomerPricelistCode =Trim(AccpacOE1100UICtrl1.UIAppControls("afeOEORDHpricelist3").Value)
pFEditCtrl.ViewFinder.ViewID = "IC0480" ‘ view for ICPRIC
pFEditCtrl.ViewFinder.ReturnFieldID = 2 ‘column of the view to be returned
pFEditCtrl.ViewFinder.Filter = "PRICELIST = " & sCustomerPricelistCode
End Sub

By doing that, when I click the finder on the macro form, that have OE1100UICtrl on it, the finder will show the price list view IC0480 filtered by O/E Header- Customer -Pricelist. By the look of it you can setup the order of the columns lake in any ACCPAC Finder.

Problems so far:
1. The finder starts very slow.
2. I don’t know a view that will also give me the comment 1 to 4 from ICITEM table.
3. Where Accpac saves the columns setup of the finder?


Sage Accpac 500 ERP (Version 5.3B)
Database: Pervasive V8.6

 

[Jay Converse]

1. Try also setting the Currency code. Here are the indexes from the AOM:

Index List - 3 Index(es)

0 - Currency Code
CURRENCY, ITEMNO, PRICELIST
1 - Item Number
ITEMNO, CURRENCY, PRICELIST
2 - Price List Code
CURRENCY, PRICELIST, ITEMNO

2. That's not possible with regular views because you can't do joins, but Djangman's option would work.

3. In the {shared data}\USER\{Accpac loginID}\{company}_P.ISM file.

 

[sebik2000]

1. Is this what you had in mind or there is a way to tell the finder what index to use.

pFEditCtrl.ViewFinder.Filter = "CURRENCY= ""CAD"" AND PRICELIST = """ & sCustomerPricelistCode & """"

Our prices are in CAD only but just by adding the field in the filter I can see a improvement. Is it just my imagination?

2. I was referring to a view like ICXXX that Accpac have that include the tables linked already.

3. Thank you. Notepad doesn’t showed to much of it. At least I know now where is stored.


Sage Accpac 500 ERP (Version 5.3B)
Database: Pervasive V8.6