Search in PurchaseOrder


I trying to create a SQL query that I can execute with 

var searchOptions = new OrderSearchOptions
CacheResults = false,
StartingRecord = 0,
RecordsToRetrieve = 10000,
Namespace = OrderNamespace.OrdersNamespace

var parameters = new OrderSearchParameters();

var sqlQueryFilter = new StringBuilder();
sqlQueryFilter.Append("OrderGroupId IN (SELECT ObjectId From OrderFormEx ");
sqlQueryFilter.AppendFormat("WHERE OrderFormEx.MyField = '{0}' )", DataToFind);
parameters.SqlWhereClause = sqlQueryFilter.ToString();
OrderContext.Current.Search<PurchaseOrder>(parameters, options);

I need to query also a field in OrderFormEx.
Any idea how to create the sqlQueryFilter?


Edited, Sep 01, 2020 21:20

If I recall correctly you can do something like:

Sep 02, 2020 7:35
Juan Munoz - Sep 02, 2020 7:42
How should the code looks like then? and where should I put the "Where" case?
Mari Jørgensen - Sep 02, 2020 7:47
Take a look at this old blog post, that should give you examples you need:

There are also example code in the developer guide:

Hi Juan,

The where clause should go like this.

StringBuilder sqlQuery = new StringBuilder();
sqlQuery.Append("WHERE OrderFormEx.MyField = " + DataToFind+ " )");

You have to modify query as following:

StringBuilder sqlQuery = new StringBuilder();
sqlQuery.Append("OrderGroupId IN (SELECT li.OrderGroupId From LineItem li ");
sqlQuery.Append("INNER JOIN LineItemEx ex ");
sqlQuery.Append("ON li.LineItemId = ex.ObjectId ");
sqlQuery.Append("INNER JOIN Shipment sh ");
sqlQuery.Append("ON li.OrderGroupId = sh.OrderGroupId ");
sqlQuery.Append("INNER JOIN ShipmentEx shex ");
sqlQuery.Append("ON sh.ShipmentId = shex.ObjectId ");
sqlQuery.Append("WHERE ex.ExpirationDate > '1/1/2011'");
sqlQuery.Append("AND NOT shex.PrevStatus IS NULL)");

OrderSearchParameters parameters = new OrderSearchParameters();
parameters.SqlMetaWhereClause = "";
parameters.SqlWhereClause = sqlQuery.ToString();
PurchaseOrder[] purchaseOrderCollection = OrderContext.Current.FindPurchaseOrders(parameters, searchOptions);
Sep 02, 2020 19:56
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
* 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.