I'm calling the browse method and trying to find an order by number. This code takes around 4-5 minutes. Are these method really that slow?
var view = OpenView("OE0520");
view.Browse("ORDNUMBER = 5555555555", true);
Use view.Order = 1
One more on this, I just want to make sure this is normal from your experience.
var view = OpenView("OE0520", DBLinkFlags.ReadOnly);
view.Browse("", true);
var gotOne = view.GoTop();
while (gotOne)
{
var orderNumber = (string)view.Fields.FieldByName("ORDNUMBER").Value;
Console.WriteLine(sageOrder.OrderNumber);
gotOne = view.GoNext();
}
Running this code w/o a filter so it's basically returning all orders - takes 5 minutes for 35,000 orders. That doesn't seem right for a SQL database query
Use While(view.Fetch()), never GoTop or GoNext
The database layer doesn't work the way you're thinking it should. Its history is steeped in transactional database processing vs working with recordsets.
Typically, if you want to work on a specific order, you will get to the order using the best index for the job and a .Read call. .Read if you have all of the key field values, .Browse if you do not.
If you need to locate a group of orders to do some processing on then your search loop either needs to use an existing index, or, be clever with finding the records using CS0120 which allows you to say, access a SQL view to locate your records.
Calling .Browse/.Fetch doesn't return a recordset to navigate through. It points you to the first record that satisfies the criteria and then .Fetch will go back to the database to find the next record that satisfies the criteria based on your current position. That's important in the event you are updating a field that impacts the index you're using.
yikes, ok. So there is no way to just return a recordset using the API?
No, not in the way that you're expecting. What is it that you're trying to do? We likely can get you pointed in the right direction.
I'm writing an integration that pulls orders from an ecommerce, sends to sage and the pull order, customer info out of sage into text files. So, I don't really need to get all orders - I was just testing. I need to get data since the last time the sync runs and then query on order number, customer number...etc to find if they exist already. I think using filters, index and the fetch will probably work
I'm writing an integration that pulls orders from an ecommerce, sends to sage and the pull order, customer info out of sage into text files. So, I don't really need to get all orders - I was just testing. I need to get data since the last time the sync runs and then query on order number, customer number...etc to find if they exist already. I think using filters, index and the fetch will probably work
So locating an order in Sage to see if it exists will be fastest to do .Order = 1, .put("ORDNUMBER")=<order number>, if .Read then it exists.
I'm not clear on your requirement around getting "data since the last time". If that's Sage data then you're going to need to flag the order - you can look at the ORDUNIQ field value - it will increment as orders are added to the system. That's the primary key so you could easily ask for all records where the ORDUNIQ value is greater than the value you last recorded.
when the sync runs, I save the datetime of the last run, then I'd need to say give me only orders modified since the last run
I've been able to get the queries faster using indexes but this one isn't working. I want to get a customer from the email2 field, I found the index but it's throwing an error. I did customerView.Order = 129. Am I doing it wrong for this field?
No, there are only 13 possible orders:
Index List - 13 Index(es)
0 - Customer Number
IDCUST
1 - Customer Short Name
TEXTSNAM
2 - Group Code
IDGRP
3 - National Account Number
IDNATACCT
4 - Billing Cycle
IDBILLCYCL
5 - Natl. Acct./Account Set
IDNATACCT, IDACCTSET
6 - Natl. Acct./Billing Cycle
IDNATACCT, IDBILLCYCL
7 - Natl. Acct./Interest Profile
IDNATACCT, IDSVCCHRG
8 - Natl. Acct./Cust. Type
IDNATACCT, SWBALFWD
9 - Natl. Acct./Group Code
IDNATACCT, IDGRP
10 - Account Sets/Customer Number
IDACCTSET, IDCUST
11 - Currency Code/Interest Profile
CODECURN, IDSVCCHRG, IDCUST
12 - Terms Code/Customer Number
CODETERM, IDCUST
OK, then the fastest method to retrieve a batch of customers, using a non-indexed field like EMAIL2 is using browse/fetch, right?
No, the fastest way is using the CS0120 view, which uses good-old pure SQL queries.
great, I'll give that a try
Sorry, but does this look right because it's returning nothing for the fields, I don't see any docs on this view
var view = OpenView("CS0120");
view.Browse("SELECT IDCUST FROM ARCUS", true);
while (view.Fetch(false))
{
var field = view.Fields[0];
Console.WriteLine((string)field.Value);
Console.WriteLine((string)field.Name)
}
Nevermind, I found some sample code, I guess you had to do
.InternalSet(256);
There are caveats to CS0120 that you have to be aware of. It's an excellent tool but it behaves in atypical ways.
You need the Internalset statement, it's only used with CS0120
view.Browse("your sql here",true);
view.Internalset(256);
while (view.Fetch())
...
I'm finding a lot of this API difficult, but this forum has been awesome which is super helpful
thanks!
OK, so fields like QTONHANDA on the items you can't return using this method so I'd still need the normal view/browse then?
That's a computed field, CS0120 won't return it. Get QOH from ICILOC, you'll need LOCATION, too.
Do you know what tables I can find TAMOUNT1 & SHINETWTX
I can't get these from CS0120 and I'm not familiar yet w/ Sage 300 to figure out what table. My guess would order details for taxes? Loop and look for a certain line item type?
SHINETWTX: OEORDH and OESHIH
TAMOUNT1: OEAUDD, OECRDD, OECRDH, OEORDH, OEORDD, OEINVH, OEINVD
whoa, I need all those tables to get the tax amount?
No, you just need the right one. Do you want the order, the shipment, or the invoice version, and the header or detail version? I assume in your case it's OEORDH, the total on the order header.
Ah, ok. Yes order details for both. I was using OE0520
OE0520 is the header. OE0500 is the detail. You should visit this page and get the AOM for your version support.na.sage.com/.../viewContent.do
Welcome to the jungle
haha, I don't see a tamount1 field in the OEORDH table. It says it's calculated. So, trying to figure out how I can get it with CS0120. My guess is find all the line items and add up all their taamount1 fields
Oops, you're right. D'oh! I was working with views all day (I love Orchid Extender!), I forgot SQL ain't the same all the time.
*Community Hub is the new name for Sage City