SaaS CMS has officially launched! Learn more now.

Searching for Orders Problem

Vote:
 

Hi,

I need to find orders which has a special metaField and I am using EPIServer 8.11.0.0. I tried Order Search described in this  article. but it returns no row.

To make it simpler, I tried to find orders that the field is not null but still I there was no success. I know that I used the correct fields and classes since it returns data when I ask for "field is null". 

Any Guidance?

var searchOptions = new OrderSearchOptions
{
CacheResults = false,
StartingRecord = 0,
RecordsToRetrieve = 10,
Namespace = "Mediachase.Commerce.Orders"
};
OrderSearchParameters parameters = new OrderSearchParameters();
searchOptions.Classes.Add("OrderFormEx");
//parameters.SqlMetaWhereClause = string.Format( "META.DocNumber LIKE '%{0}%'",docNumber);
parameters.SqlMetaWhereClause = "NOT META.DocNumber IS NULL";
//parameters.SqlWhereClause = "OrderGroupId IN (Select ObjectId FROM OrderGroup_PurchaseOrder)";
var purchaseOrderCollection = OrderContext.Current.FindPurchaseOrders(parameters, searchOptions);



//Returns purchaseOrderCollection[0]

#142290
Dec 08, 2015 15:30
Vote:
 

Hi,

I would suggest you to run your query with this "tool": http://world.episerver.com/blogs/Quan-Mai/Dates/2014/10/Order-searchmade-easy/

I think the SqlMetaWhereClause is not correct? Shouldn't it be  "META.DocNumber IS NOT NULL"?

Regards,

/Q

#142292
Dec 08, 2015 15:36
Vote:
 

I've just checked the DB and there are some rows in [OrderFormEx] which has value in field "DocNumber" for sure.

What is wrong?

#142294
Dec 08, 2015 15:41
Vote:
 

Hi Quan,

Thanks for your fast responces.

I also thought of that but still no records.  I will try the tool, and come back to you. thanks

#142295
Dec 08, 2015 15:43
Vote:
 

Hi again,

I am really confused :) 

I could make my query work, but with joining and putting everything in where clause. but I don't like it this way. Do you have any advice?

var searchOptions = new OrderSearchOptions
{
CacheResults = false,
StartingRecord = 0,
RecordsToRetrieve = 10,
Namespace = "Mediachase.Commerce.Orders"
};
OrderSearchParameters parameters = new OrderSearchParameters();
searchOptions.Classes.Add("PurchaseOrder");
parameters.SqlWhereClause =string.Format( "OrderGroupId IN (SELECT OrderForm.OrderGroupId FROM OrderForm LEFT OUTER JOIN OrderFormEx ON OrderForm.OrderFormId = OrderFormEx.ObjectId WHERE (OrderFormEx.DocNumber = '{0}' ))", DocNumber);
var purchaseOrderCollection = OrderContext.Current.FindPurchaseOrders(parameters, searchOptions);



#142300
Dec 08, 2015 16:46
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.