Retrieve many PurchaseOrder from Commerce via FindPurchaseOrders

Vote:
 

Hi there,

I would like to retrieve many PurchaseOrder at once using FindPurchaseOrders.

I tried 2 options but both of them return empty collection.

1.

    var poMetaWhere = string.Format("META.{0} in ('{1}')", MetaFieldErpOrderNumber, String.Join("','", erpOrderNums));

2.

    var stringBuilder = new System.Text.StringBuilder();
    for(var i = 0; i < erpOrderNums.Count; i++)
    {
        stringBuilder.Append(string.Format("META.{0} = '{1}'", MetaFieldErpOrderNumber, erpOrderNums[i]));

        if(i + 1 == erpOrderNums.Count)
	{
	    break;
        }

        stringBuilder.Append(" OR ");
    }

In OrderSearchOptions RecordsToRetrieve is set to erpOrderNums.Count.

    RecordsToRetrieve = erpOrderNums.Count,

Code is working fine if poMetaWhere is set like

    string.Format("META.{0} = '{1}'", MetaFieldErpOrderNumber, erpOrderNum)

just for one number.

The OrderSearchParameters are set either

    parameters.SqlMetaWhereClause = string.Format("{0} AND {1}", dealerMetaWhere, poMetaWhere);

(first case) or

    parameters.SqlMetaWhereClause = string.Format("{0} AND ({1})", dealerMetaWhere, poMetaWhere);

(second case).

(Later there is regular call via FindPurchaseOrders)

    OrderContext.Current.FindPurchaseOrders(parameters, options, out totalRecords);


I really new to commerce. Maybe I am missing something important.

#188509
Edited, Feb 26, 2018 14:55
Vote:
 

If you remove dealerMetaWhere, and just only have poMetaWhere as the only condition, does it work with multiple erpOrderNums ?

#188510
Feb 26, 2018 15:06
Vote:
 

Without dealerMetaWhere it returns empty collection in both cases also.

#188512
Edited, Feb 26, 2018 15:18
Vote:
 

Mind to post the entire code here (with only poMetaWhere condition) ?

#188514
Feb 26, 2018 15:28
Vote:
 

No, no. Look.

public IEnumerable<PurchaseOrder> GetOrdersByERPOrderNums(Guid? dealershipId, List<String> erpOrderNums)
{
    int orderCount;
    var parameters = new OrderSearchParameters();  
    var poMetaWhere = string.Format("META.{0} in ('{1}')", MetaFieldErpOrderNumber, String.Join("','", erpOrderNums));

    //var stringBuilder = new System.Text.StringBuilder();
    //for(var i = 0; i < erpOrderNums.Count;i++)
    //{
    //    stringBuilder.Append(string.Format("META.{0} = '{1}'", MetaFieldErpOrderNumber, erpOrderNums[i]));

    //    if(i + 1 == erpOrderNums.Count)
    //    {
    //        break;
    //    }

    //    stringBuilder.Append(" OR ");
    //}

    //var poMetaWhere = stringBuilder.ToString();

    parameters.SqlMetaWhereClause = poMetaWhere;
    parameters.OrderByClause = "OrderGroupId DESC";
    var classes = new StringCollection();
    classes.Add("PurchaseOrder");
    var options = new OrderSearchOptions
    {
        CacheResults = true,
        RecordsToRetrieve = erpOrderNums.Count,
        ReturnTotalCount = true,
        Classes = classes,
        Namespace = "Mediachase.Commerce.Orders",
        StartingRecord = 0
    };

    int totalRecords = 0;         
    return OrderContext.Current.FindPurchaseOrders(parameters, options, out totalRecords);
}
#188516
Edited, Feb 26, 2018 15:55
Vote:
 

This is interesting. I have some minutes to spare so I look into this and it seems I can reproduce this issue. It can be a bug in our side. We will look into this and keep you posted. 

No I did it wrong, I accidentally add the SQLWhereClause condition which has no matches

#188519
Edited, Feb 26, 2018 16:18
Vote:
 

So the same code for your test data works fine?

If so maybe the problem arise if some of order numbers is no present in DB.

There exists some PurchaseOrder.LoadByOrderGroupId(orderId) method but I’ve not found any overload or similar method able to retrieve multiple PurchaseOrder at once. There should be some or am I wrong?

#188520
Edited, Feb 26, 2018 16:35
Vote:
 

No, I did run it directly on SQL Server.

What would return if you run this in SQL Server Management Studio (update MetaSQLClause value with your clause)?

declare @p8 int
set @p8=0
exec ecf_OrderSearch @SQLClause=N'',@MetaSQLClause=N'META.trackingnumber in (''618000'', ''618002'', ''618003'')',@OrderBy=N'OrderGroupId DESC',@Namespace=N'Mediachase.Commerce.Orders',@Classes=N'PurchaseOrder',@StartingRec=0,@NumRecords=2147483647,@RecordCount=@p8 output
select @p8

If you are using Commerce 10 or earlier, you would have to add @ApplicationId = '395C98A4-F799-43A6-9DA0-47F5AF3D6EF2' (need to update ApplicationId to your site)

#188521
Feb 26, 2018 16:38
Vote:
 

I need to colaborate on this with my boss. I shall tell you as it is done.

#188522
Edited, Feb 26, 2018 16:53
Vote:
 

I’ve tried your query. It returns 0 rows despite the correct tracking numbers were used.

#188910
Edited, Mar 06, 2018 17:43
Vote:
 

But does it return correct value if you change the MetaSQLClause to only one TrackingNumber?

It sounds like you have to contact our developer support service and provide your database :) 

#188915
Mar 06, 2018 19:27
Vote:
 

Unfortunately does not.

The query:

declare @p8 int
set @p8=0
exec ecf_OrderSearch @SQLClause=N'',
@ApplicationId = '395C98A4-F799-43A6-9DA0-47F5AF3D6EF2',
@MetaSQLClause=N'META.trackingnumber = ''PO2152395259''',
@OrderBy=N'OrderGroupId DESC',
@Namespace=N'Mediachase.Commerce.Orders',
@Classes=N'PurchaseOrder',
@StartingRec=0,
@NumRecords=2147483647,
@RecordCount=@p8 output
select @p8
#188917
Edited, Mar 06, 2018 19:35
Vote:
 

Very strange. As I suggested you should contact developer support service and provide the database. Might be interesting to look at :) 

#188931
Mar 07, 2018 9:36
Vote:
 

We received your support case from support team today, and I took a quick look at your database. Running this query (You probably recognize the data, even though I obfuscated it a bit)

declare @p8 int
set @p8=0
exec ecf_OrderSearch @ApplicationId = '3D5967C5-627D-4FA8-88A1-FB9A2ECD928F',
@SQLClause=N'',
@MetaSQLClause=N'META.trackingnumber in (''PO1204'', ''PO1205'', ''PO1206'')',@OrderBy=N'OrderGroupId DESC',@Namespace=N'Mediachase.Commerce.Orders',@Classes=N'PurchaseOrder',@StartingRec=0,@NumRecords=2147483647,@RecordCount=@p8 output
select @p8
go
declare @p8 int
set @p8=0
exec ecf_OrderSearch @SQLClause=N'',
@ApplicationId = '3D5967C5-627D-4FA8-88A1-FB9A2ECD928F',
@MetaSQLClause=N'META.trackingnumber = ''PO1205''',
@OrderBy=N'OrderGroupId DESC',
@Namespace=N'Mediachase.Commerce.Orders',
@Classes=N'PurchaseOrder',
@StartingRec=0,
@NumRecords=2147483647,
@RecordCount=@p8 output
select @p8

Returns correctly on my machine. I'm not sure what is wrong here ... 

#190428
Edited, Apr 10, 2018 23:38
Vote:
 

I think I have a clue here

Running this

declare @p8 int
set @p8=0
exec ecf_OrderSearch @ApplicationId = '3D5967C5-627D-4FA8-88A1-FB9A2ECD928F',
@SQLClause=N'',
@MetaSQLClause=N'META.[ErpOrderNumber] in (''172212'', ''172212'', ''172212'')',@OrderBy=N'OrderGroupId DESC',@Namespace=N'Mediachase.Commerce.Orders',@Classes=N'PurchaseOrder',@StartingRec=0,@NumRecords=2147483647,@RecordCount=@p8 output
select @p8

returns the correct result as well. So you might have been mixing between the metafield (TrackingNumber instead of ErpOrderNumber) and the data. 

#190431
Apr 11, 2018 7:45
Vote:
 

Hi Quan,

nice to know that. I verified my queries and I have forgotten to use proper ApplicationId so all queries works as expected. My fault, I am sorry.

I have verified the C# method code and found it magically working. I hope it was not working due some connection issue or because some relation between real data and dev DB but I am not sure what happened that time.

I shall delete this thread after your reply since there is no question/solution information.

#190502
Apr 11, 2018 13:57
Vote:
 

No you don't have to, when a thread is created it should be there. Even if there was no real question, solution then it still might contain useful information. For example the code you posted might be beneficial for the one looking for an example - as we confirmed that it was written correctly. 

#190503
Edited, Apr 11, 2018 14:00
Vote:
 

Ok, as you wish.

#190504
Apr 11, 2018 14:02
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.