x Want to participate? Join the group to interact
  • Replies 7 replies
  • Subscribers 133 subscribers
  • Views 1248 views
  • Users 0 members are here
Browse Forums
Announcements
268
topics
260
replies
Databases and Operating Systems
756
topics
2k
replies
Financials Suite
1k
topics
3k
replies
General Discussion
2k
topics
10k
replies
Operational Suite
851
topics
2k
replies
Reporting and Analytics
1k
topics
3k
replies
Reports, Macros, and Customizations
876
topics
3k
replies
Sage CRM for Sage 300
182
topics
410
replies
JStrz

CS0120 Options for Calculated fields

Posted By

I was having a discussion in another post but it was getting too long and I figured this might be a good post for others to find. Trying to grab a lot of data from Sage, and I was pointed to CSO120. It's been great so far, but I've run into a couple snags. The biggest is some of the fields I need that were in the View OEORDH and other views are not returned in CS0120 because they're calculated. The fields are TAMOUNT1 & SHINETWTX in the case of OEORDH

I'm not sure 100% how to get these directly from the database or if it's going to be too much of a pain. I think tax amount 1 would be the sum of tax amount 1 on line items but I'm not 100% sure and it doesn't seem to match what is being returned from TAMOUNT1

Because I have to pull a lot of orders, products..etc. It can be slow if I don't use CS0120

My options are:

  1. Just use browse/fetch to get all the order views I need and their calculated field (very slow, like minutes)
  2. Use CS0120 to get all the ID's I need, then just query one at a time using unique ID and read for the calculated fields - I sort of think this would still be slow though
  3. Only use CS0120, but someone would have to tell me how to calculate the TAMOUNT1 & SHINETWTX - If it's too difficult to do manually, it might not be a viable option

Just wondering other thoughts or explain if it's feasible to calculate these fields on my own. What do others do?

Parents

  • Unfortunately, you've been thrown into the pool where there's not a lot of documentation and only peer-to-peer support. I don't know if any of the old training materials are still around.

    I'd suggest a hybrid approach which may be slower (humans won't notice) and it future proofs you.

    Use CS0120 to get what you can but also pull the key fields for the order transaction in your SQL. With the key values in hand, you can use the views to get the rest of the data that you need. When you use key field values you're going to have excellent speed.

    Browse can be slow but it doesn't have to be - it's all dependant on what field values you have access to and what indexes are available.  If you have an order number you can find that record instantly (in database terms). If you have access to the SQL server and can create your own views, stored procedures, triggers, tables, etc then you can mitigate all sorts of performance bottlenecks using CS0120.

  • in reply to Django

    OK, that's option #2 so I can get all the orders using CSO120 with the non-indexed fields, then using the ID to read the view for the others

  • in reply to JStrz

    Yes.

    And remember, in OEORDH, the ORDUNIQ field is the primary key and is an auto-incrementing number independent of the ORDNUMBER. So if you need to process all new orders since 'the last time' just keep track of the last ORDUNIQ value that you processed and you'll be able to, very quickly, arrive at a list of order records to work with.

  • in reply to Django

    OK, so using this option - can  you tell me if I have what you believe to be the best option to pull a record by ID

    var dataLinkRW = _session.OpenDBLink(DBLinkType.Company, DBLinkFlags.ReadOnly);
    var ordersView = dataLinkRW.OpenView("OE0520");
    ordersView.Order = 0;
    ordersView.Fields.FieldByName("ORDUNIQ").SetValue(ID, false);
    //ordersView.Read(false);

    ordersView.GoTop();
    var ordID = (decimal)ordersView.Fields.FieldByName("ORDUNIQ").Value;

  • in reply to Django

    OK, so using this option - can  you tell me if I have what you believe to be the fastest option to pull a record by ID

    ...inside loop of IDs
    var dataLinkRW = _session.OpenDBLink(DBLinkType.Company, DBLinkFlags.ReadOnly);
    var ordersView = dataLinkRW.OpenView("OE0520");
    ordersView.Order = 0;
    ordersView.Fields.FieldByName("ORDUNIQ").SetValue(ID, false);
    ordersView.Read(false);
    var ordID = (decimal)ordersView.Fields.FieldByName("ORDUNIQ").Value;

    This takes about 1min to do 1000 records. I also tried browse/fetch gotop but it didn't see to make it much faster. I have the index at 0

Reply
  • in reply to Django

    OK, so using this option - can  you tell me if I have what you believe to be the fastest option to pull a record by ID

    ...inside loop of IDs
    var dataLinkRW = _session.OpenDBLink(DBLinkType.Company, DBLinkFlags.ReadOnly);
    var ordersView = dataLinkRW.OpenView("OE0520");
    ordersView.Order = 0;
    ordersView.Fields.FieldByName("ORDUNIQ").SetValue(ID, false);
    ordersView.Read(false);
    var ordID = (decimal)ordersView.Fields.FieldByName("ORDUNIQ").Value;

    This takes about 1min to do 1000 records. I also tried browse/fetch gotop but it didn't see to make it much faster. I have the index at 0

Children
No Data

*Community Hub is the new name for Sage City