Integrations and APIs for Service

Get Involved. Join the Conversation.

Topic

    Kevin Ngo
    Issue with limitation of number records returned using...
    Topic posted April 30, 2010 by Kevin NgoRed Ribbon: 250+ Points, last edited October 29, 2011 
    2866 Views, 23 Comments
    Title:
    Issue with limitation of number records returned using execute report
    Content:

    Hi,

    I am aware of issue about limitation of number row returned from a report, and I get around it by making multiple calls to a report based on a sorted and filter unique column and using condition where ID > max ID.  However, is there a way to deal with Prod2Quote table which has no primary key or ID, and it has mulitple quoteID and product ID.  Please help

    Thanks,

    KJ

    Comment

    • Ryan McCullough

      KJ, in November '09 and later, the Prod2Quote table has a unique primary key ID.

      Until then, you will need to filter on "where quote_id >= $last_quote_id".

      And then you will need to filter out the product_id items you have already received.

    • ryszard antonczyk

      KJ,

       

      Since (as you mentioned above) one quote_id could have multiple records with the same product_id, the better way to filter items you have already received is to use seq - which should be unique for a given quote_id.

      Also please keep in mind that this new unique primary key ID (p2q_id) is NOT exposed through RN Connect Data Integration (neither quote_id), therefore your report will need to return all the Prod2Quote info you need.

       

      Ryszard

    • Kevin Ngo

      Ryan,

      The problem is that there might be mulitple same product ID for a quote. 

      Ryszar,

      I don't see seq field exposed in analytic report, so I canot execute a report using RN Connect. BTW, our RN version is May 2009.

      Thanks,

      Kevin 

    • Ryan McCullough

      KJ, yes, that is why I said "And then you will need to filter out the product_id items you have already received."

      So, let's say the last item you receive is quote_id 25000 and the last product_id you received is 500. If you filter for where quote_id >= 25000, you will receive all prod2quote items on quote 25000, so you will need to filter out the prod2quote items you have already received. I know it is not ideal, but typically there are not more than a few dozen prod2quote items on a quote (if that many).

      -Ryan

    • Ryan McCullough

      Btw, Seq is only relative to the prod2quote items on a quote. It does not apply to all prod2quote items.

      I don't know that you could filter on where product_id > $last_product_id or seq > $last_seq because once you move to the next quote, the seq will start over at 1 and it could have products with a lower ID than the last one.

    • ryszard antonczyk

      Ryan, the filter could be: "(quote_id = $last_quote_id AND seq > $last_seq) OR quote_id > $last_quote_id" assuming that records are ordered by compounded key (quote_id.ToString() + seq.ToString() - or something similar).

       

      KJ, you can always create your own, custom report (start with copying the existing one) and add seq to it.

       

      Hope it helps.

      Ryszard

    • ryszard antonczyk

      KJ,

       

      Ryan just pointed out to me that seq is not guaranteed to be unique for one quote_id neither. Since the server does not validate uniqueness of seq - it is the user's responsibility to make it unique. Therefore, chances are, it is not perfect.

      So you have only few options:

      • upgrade to November'09 (or later) release and use p2q_id for filtering,
      • use ExecuteReport with "where quote_id >= $last_quote_id" filter and then inside your application eliminate duplicate records (by comparing ALL returned fields, including product_id, seq, etc) for records  "where quote_id = $last_quote_id",
      • write application to retrieve all quotes' (using ExecuteReport  with "where quote_id > $last_quote_id" filter) IDs then get each quote and its prod property (which is the list of prod2quotes).

      Let us know what did you decide and how it worked out for you.

      Thanks,

      Ryszard

       

    • Kevin Ngo

      Ryszard & Ryan,

      I can't see or access seq field in Report Analytic.  Seq is for productID or Seq is for Quote ID, so please tell me which scenario.  I don't know if I can use other fields for filtering because value for other fields might be changed by users when I try to do 2nd hit.

      Scenario 1: QuoteID, ProducID, Seq

       1, 1,1

      1,1,2

      1,2,1

      Scenario 2:

      1,1,1

      1,1,2

      1,2,3

      Thanks,

      KJ

    • Ryan McCullough

      You need something like the following (pseudo-code):

      object[][] Results = null;
      int ReportId = 12345;
      int LastQuoteId = 0;
      RNOWAcFilter[] Filters = new RNOWAcFilter[1];
      Filters[0].FilterId = 1; // quote_id filter
      Filters[0].Operator = RNOWUtil.SearchOperator.GREATER_THAN_OR_EQUALS;
      do
      {
        Filters[0].Value = LastQuoteId;
        Results = OF.ExecuteReport(FilterId, Filters);
        if (Results != null && Results.Count > 0)
        {
                for (int i = 0; i < Results.Count; i++)
                {
                        // do processing
                }
                LastQuoteId = Results[Results.Count - 1][quote_id_column];
                // ignore/remove all of the quotes where quote_id = LastQuoteId
      } } while (Results != null)
    • Kevin Ngo

      Ryan,

      Because there might be multiple quoteID and multiple same productID for the same quoteID in returned result record set,  and other property fields for product or quote could be changed by the time when I run execute report for subsequence calls, so is there other key field can reference it?  Ryszard mentions about seq field, but I couldn't see in Report Analytic.

      Returned example data set:

      //this could caused be end user.

      quoteID, productID

      1,1

      1,1

      1,1

      2,1

      2,1

      Thanks,

      KJ

    • Ryan McCullough

      I would have to investigate further, but I think your best bet is to do something like this:

      Query for quotes + prod2quotes.

      loop through results

      When you reach the last result, save the quote_id and ignore any quotes in the current result set with that quote_id

      query again where quote_id >= last quote id.

      So, lets say you run the report and get quote_ids 1-100 back. You will then ignore the records with quote id = 100 since it could contain incomplete prod2quote items.

      Then you query again, including quote_id 100 in the results. Now you will get all of the prod2quote items for quote_id 100. Repeat until you receive less than the max record limit (10k?).

    • Kevin Ngo

      Ryan,

      I completely understand about using primary key, quote_id, to get a first set 1-100 and then get the next set 100 to max record limit. It will work for quote table, but it probably doesn't work for prod2quote table because the quote_id, 100, can be duplicated multiple time in returned dataset.  If I include quote_id 100 again in my next returned dataset then is there an unique field, which users are not allow to update it, so I can filter out and not import the same record gain.

      Thanks,

      KJ

    • Ryan McCullough

      If you run the report and receive something like this:

      quote_id, product_id
      1,1
      1,2
      1,3
      ...
      99,6
      100,1
      100,2

      You do not know if you found all records where quote_id =100, so, ignore the records where quote_id=100, then re-run the report filtering for quote_id >= 100.

      100,1
      100,2
      100,3
      101,5
      ...

      Now you get all of the prod2quote items for quote_id 100.

      Does that make sense? This assumes that you will not have more than 10k prod2quote items on a quote, which should be a safe assumption.

      If you still have questions, I think it would be best for you to engage RN Professional Services. They offer RN Connect coaching.

    • Kevin Ngo

      That make sense. You are great. Thank you.

    • Ryan McCullough

      Glad I could help KJ. Let us know how it works out.