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
So searching on anything not in this list will take 4-5 minutes?
Browse will search record by record using the index or order that you've specified.
If you change the .Order then the .Fetch will arrive at the record much faster.
If you .Put a key field value into the field and you're using the right .Order then you can call .Read and land right on the record directly.
To answer your question - maybe - it depends on your database, volume of data, etc.
You can use the CS0120 view to call the database directly so if you have other indexes on the table you can find records that aren't indexed by the standard views.
But - like with any database application - if you're not using an index, you're waiting.
Ok, where did you find that index? Also if I wanted to query on last posted date, being that's not indexed - you're saying to try CS0120?
The two ways to easily view indexes by view are by using the downloadable Application Object Model or U.I. Info tool located in your Sage 300 program group. It is organized by module / ROTO ID. To determine the ROTO ID, you can right-click / properties on the icon making use of the target view - in this case, Orders.
Thank you. Did you have any ideas on this one?
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
FilterFetch works the same way?
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
*Community Hub is the new name for Sage City