Check for the existence of a ShipTo 1

[JoeAtWork]

I have a function that checks if a ShipTo currently exists: DoesShipToExist(string CustNo, string ShipToNo)

It returns a value indicating if that ShipToNo exists for that customer. In my test database it has always worked correctly. In the production database it sometimes returns a value indicating the ShipTo doesn't exist when apparently it does. This causes a problem because the next step is to add the ShipTo, and then there is an error that the ShipTo already exists.

Here is the code I use for checking if the ShipTo exists (in C#):

	AccpacCOMAPI.AccpacDBLink dbLink = null;
	dbLink = (AccpacCOMAPI.AccpacDBLink)Application["dbLink"];

	// ShipTo view
	AccpacView vwShipto = null;
	dbLink.OpenView("AR0023", out vwShipto);

	// Open a view, filtered for this particular Customer # and Shipto #
	string strFilter = "IDCUST = \"" + CustNo + "\" AND IDCUSTSHPT = \"" 
		+ ShipToNo + "\"";

	vwShipto.Browse(strFilter, true);
				
	// Does this Shipto exist in the filtered view?
	shiptoExists = vwShipto.Fetch();

	vwShipto.Close();

For those not familiar with C#, the strFilter variable would equate to something like:

IDCUST = "MyCustNo" AND IDCUSTSHPT = "MyShNo"

I thought maybe I am doing something wrong with the Fetch method, should I be doing the equivalent of a "MoveFirst" with the view before calling Fetch?

 

[jerle78]

hi Joe,

I don't have an answer for you, but if you ever figure this out, please let me know. I wrote a similar script to verify shipto information a long time ago, but it seems AccPac doesn't always store the ship to in teh same directory. If i use Accpac to view the shipto of a customer, i can find it, but i find no record in the backend database tables. I gave up on it. I mainly need it to correct and edit the ship to phone numbers.

R-
jerle

 

[JoeAtWork]

Thanks Jerle,

I haven't solved the problem, but it's nice to know its not just me

One of the few tools I have is a listing of the AccPac tables/views and fields, I couldn't find anything that looked like an "alternative" ship-to table to the one I use (AR0023).

My client is thinking about purchasing the SDK, in which case I would finally be able to get some tech support for some of these mysterious questions!

 

[ettienne]

Use the AR0023 view, this is the ship to table ARCSP.

To test if a record exists use .Read

First you need to set the index to use for the view.
You would declare and open the views in the normal manner, then

ARCSP.Order = 0 'Use first index: IDCUST, IDCUSTSHPT

'Customer ID
ARCSP.Fields("IDCUST").PutWithoutVerification "ABC123"
'Ship to
ARCSP.Fields("IDCUSTSHPT").PutWithoutVerification "XYZ123"

If ARCSP.Read then
'Record found
else
'Record not found
end if

The .Read method is the same as the Seek method used with Access tables, you need to use an index field to search on.
If you want to search a non indexed field then you use .Browse and .Fetch (or the newer .GoNext) methods.

 

[JoeAtWork]

Thanks for the clear answer ettienne. I tried your code and it works! I hope it proves more reliable on the production system than the .Fetch method seemed to.

Here is my code, written in C#:

// ******* Use .Read instead of .Fetch to check for existing record ****
object obj = null;
vwShipto.Order = 0;			// Set to use first index (Cust# & Shipto#)
				
obj = (object)CustNo;
vwShipto.Fields.get_FieldByName("IDCUST").PutWithoutVerification(ref obj);
obj = (object)ShipToNo;
vwShipto.Fields.get_FieldByName("IDCUSTSHPT").PutWithoutVerification(ref obj);

// Does this CustNo + ShipToNo exist?
shiptoExists = vwShipto.Read();