SDK Browse By Customer Optional Field 1

[hundel]

We've added a boolean optional field to the customer record (AR0400) in Accpac 5.6 and would like to browse customer records (AR0024) where the optional field "ENABLED" is True.

I can search for records that have the optional field set to true via the finder in Accpac, but the browse criteria do not appear to record to a macro.

I've tried a wide range of browse criteria, but the .Net C# code is currently:

ARCUSTOMER1detail.Browse("OPTFIELD=ENABLED AND VALIFBOOL=True", true);

These fetches return nothing unless I add "IDCUST=1100" or some such valid primary key.

Is it possible to use the SDK to browse all headers given an optional field name and value?

 

[DjangMan]

No. When you're browsing View AR0024 that's just the ARCUS table.

Two options come to mind.
1) Open a separate view variable looking at the customer optional fields (AR0400) and browse on that. The customer number is in that table so you could call .Read on your AR0024 variable. There is an index on OPTFIELD so it should be reasonably quick.

2) Open a separate view variable looking at CS0120 and use its .Browse to make a database call. This will be faster than option #1 above. It also allows you more flexibility for including multi-table criteria.

 

[hundel]

Thanks for your reply. ARCUSTOMER1detail above does in fact refer to AR0400, so I thought I was following suggestion 1. Can you point to what I'm doing wrong there? I can provide a few more lines from the code if tha thelps.

 

[DjangMan]

If ARCUSTOMER1detail is composed with the AR0024 view then your browse statement will only search within the customer that AR0024 is pointing at.

 

[hundel]

Understood, and originally I thought view composition would allow me to relate the two. However, I am not even able to browse the optional fields with just those entries in the where clause.

To be crystal clear, the following browse gives a single fetch result with the enabled value:

ARCUSTOMER1detail.Browse("IDCUST=1100 AND OPTFIELD=ENABLED AND VALIFBOOL=True", true);

But the following returns no results at all:

ARCUSTOMER1detail.Browse("OPTFIELD=ENABLED AND VALIFBOOL=True", true);

 

[DjangMan]

(OPTFIELD="ENABLED") AND (VALIFBOOL=1)

 

[hundel]

Thanks for the assistance DjangMan. Unfortunately, the where clause you recommend returned no results.

The OPTFIELD and VALIFBOOL syntax appears to be working since I can control the result in code example 1 (above) by unchecking my enabled optional field in the Accpac UI.

To me, that seems like a strong indication that browsing optional tables is dependant on providing a primary key for some reason internal to Accpac. I can think of no other explanation for why adding a query condition would increase a result set.

Unfortunately, I can't see the code generated when I find by optional field values from the UI as a macro record does not capture that. Make sense? Sound wrong?

 

[DjangMan]

Hmmm. I exported customers and set the criteria on the optional fields using the UI and that was the filter it generated.

Just to confirm - you want to find all customers who have ENABLED=Yes, right?

I'm using the CREDTWARNING optional field from the sample data here:

Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)

Dim ARCUSTOMER1header As AccpacCOMAPI.AccpacView
mDBLinkCmpRW.OpenView "AR0024", ARCUSTOMER1header

Dim ARCUSTOMER1detail As AccpacCOMAPI.AccpacView
mDBLinkCmpRW.OpenView "AR0400", ARCUSTOMER1detail

ARCUSTOMER1header.Compose Array(ARCUSTOMER1detail)
ARCUSTOMER1detail.Compose Array(ARCUSTOMER1header)

Dim AROptionalFieldView As AccpacCOMAPI.AccpacView
mDBLinkCmpRW.OpenView "AR0400", AROptionalFieldView

AROptionalFieldView.Browse "(OPTFIELD=""CREDTWARNING"") AND (VALIFBOOL=1)", True
Do While AROptionalFieldView.Fetch    
    ARCUSTOMER1header.Fields("IDCUST").Value = AROptionalFieldView.Fields("IDCUST").Value
    ARCUSTOMER1header.Read
    MsgBox "Customer: " & Trim(ARCUSTOMER1header.Fields("NAMECUST").Value) & " has a Yes"
Loop

I set two customers (non-contiguous) and this macro showed me the names of both of those customers.

 

[hundel]

That's very helpful and gives me confidence this will work. I'll take a careful look at what's different in my browse, fetch, etc. I'm using C# so the syntax is a bit different for the quote wraps but it should be equivalent. Thanks again.

 

[hundel]

DjangMan, your example was of great use to me. If you look, you'll see that Accpac opens a second view of AR0400 customer optional fields and does not compose it with the customer header records. That view can be browsed without issuing a customer ID. Works like a charm!