Best way to search and return results?

Discussions related to custom development with Select.
Post Reply
ExovelConsulting
Posts: 4
Joined: Wed Mar 01, 2017 1:14 pm

Best way to search and return results?

Post by ExovelConsulting »

I've built a standalone app that interacts with Select via the API. I'm trying to perform a search and return specific fields for reporting purposes.

The first way I tried is:

Code: Select all

 var os = ss.GetService<IOrderStore>();
 var orders = os.Orders.Where(x => x.EscrowStatus == SoftPro.OrderTracking.Client.Orders.EscrowStatus.InProcess)
 foreach (var order in orders)
                {
                        var fullOrder = os.OpenOrder(order, OrderEditMode.ReadOnly);
                       .. etc
However, this is extremely slow and resource intensive since it first runs the search and then I need to load the full details of every returned order in order to dig down into the details to get the fields I'm looking for.

The second way that I tried is:

Code: Select all

 var qm = ss.GetService<IQueryManager>();
                var source = qm.Sources.Where(x => x.Name == "Orders").First();

                var q = qm.NewQuery(source);

                IQueryExpression expression = q.Factory.MakeBinary(
                    QueryExpressionType.Equal,
                    q.Factory.FieldRef(OrderSearchFields.EscrowStatus),
                    q.Factory.Value(SoftPro.OrderTracking.Client.Orders.EscrowStatus.InProcess)
                );
                 q.Expression = expression;

                var orders = qm.Execute(q);
Which returns quite quickly, but returns a datatable that only has certain fields in it. Strangely, the query object has two properties, one of which is "AvailableResultColumns", but I haven't found a way to access those columns.

My question is.. how should I be performing this search?
john.morton
Posts: 89
Joined: Wed Nov 16, 2011 11:51 am

Re: Best way to search and return results?

Post by john.morton »

I would recommend using the ISearchObject NewOrderSearch(Expression<Func<IOrderSearchFields, bool>> expr) method.

Code: Select all

var os = ss.GetService<IOrderStore>();
searchObject = os.NewOrderSearch(i => i.EscrowStatus == EscrowStatus.InProcess);

var orders = os.Orders.Search(searchObject).ToList();
This could, obviously, return a whole bunch of IOrderInfos, so if you want to limit the results you can also do this:

Code: Select all

var orders = os.Orders.Search(searchObject).Take(10).ToList();
... which will just bring the first 10 across to the client side. You can use this together with .Skip() to chunk the orders you want to work on:

Code: Select all

var orders = os.Orders.Search(searchObject).Skip(10).Take(10).ToList();
If the fields you are looking for aren't available on IOrderSearchFields or IOrderInfo, then you will have to open the orders to look for you data. If you do need to open the order to look for some more specific data, it would be a lot faster to open it as either PromotableRead or ReadOnly.
ExovelConsulting
Posts: 4
Joined: Wed Mar 01, 2017 1:14 pm

Re: Best way to search and return results?

Post by ExovelConsulting »

I may have simplified my example a bit too much. I'm trying to get orders that have an escrow status of "In Process" or "Hold", and are in a specific workflow.

The bare minimum fields I'd like to get back are:
EscrowOfficer (included in results)
TransactionType
Workflow
- Requested
- Due Date
- Completed

I was able to get results with NewOrderSearch, but it seems I would then have to use LINQ to filter down by Workflow, and then still have to load each order individually ? Is there a more efficient way of doing that?
john.morton
Posts: 89
Joined: Wed Nov 16, 2011 11:51 am

Re: Best way to search and return results?

Post by john.morton »

I'm not a Workflow expert, so I'm assuming you're talking about finding orders that have specific kinds of Tasks on them... if this is not the case, please let me know.

That being said, you can use the LINQ expression on the NewOrderSearch() method to search on child items like Tasks using the Any() method. Something like:

Code: Select all

os.NewOrderSearch(i => i.EscrowStatus == EscrowStatus.InProcess && i.Tasks.Any(t => t.RequestedDate == <Some date> && t.Status == TaskStatusType.Completed));
...FYI, this code sample is written on the fly, so there might be a missing parenthesis or something here and there.
ExovelConsulting
Posts: 4
Joined: Wed Mar 01, 2017 1:14 pm

Re: Best way to search and return results?

Post by ExovelConsulting »

Thanks for your help, I do really appreciate it. I'm no expert in any of this!

I'm not trying to search by those fields, they are part of what I'd like to be returned. The piece I would like to include in the search is the WorkflowProcess. It's one of the fields returned in the search (IOrderInfo). I may be able to grab the names of the Tasks from the Workflow and use them in the way you've illustrated rather than trying to search by Workflow directly.
Post Reply