Prev
Next
Forums Integration / Customization IPA/ProcessFlow

Leaving a query before last record is reached

 7 Replies
 0 Subscribed to this topic
 52 Subscribed to this forum
Sort:
Author
Messages
Jamie
Basic Member
Posts: 9
Basic Member
8/27/2009 2:29 PM
    Hello, I'm wondering if anyone has found a way to leave a query before all records have been read. Similar to a "do while". I have a query that reads through lines on an invoice and I want to move on when a certain criteria is met instead of reading through all remaining records. Now I can do this with a branch within the query and just exit instead of going to the end of the query, but the problem comes when it comes back around to the beginning of that query again. It just stops. Any suggestions would be greatly appreciated!
    Derek Czarny
    Veteran Member
    Posts: 63
    Veteran Member
    8/27/2009 4:12 PM
      If I understand your question correctly, I think I've done something like this before.  I wanted to know if a certain condition existed in a query, if it did, I wanted to move on to the next thing.  I used a &MAX=1 in my query parameter, so when it found my condition, it would end the loop.
      Jamie
      Basic Member
      Posts: 9
      Basic Member
      8/27/2009 7:48 PM

        I don't think I did a good job of explaining. &Max=1 will return only 1 record. I still need to have all records pulled. Inside the query I have a calculation being done in an assign node. If for the current record the result of the calculation is the desired value I would like to stop the query. If the desired value is not found I would like it to continue on through the remaining records until it is found or not found at all. I'm thinking it would save on system resources if not all records needed to be read through. Some invioces have hundreds of lines and if the 5th record (line) is the correct one it would save on a lot of processing. 

        Derek Czarny
        Veteran Member
        Posts: 63
        Veteran Member
        8/28/2009 11:12 AM
          Are you looking for Invoice line values over a certain threshold?  If that is the case you could create a condition in DBDEF that has amount greater than $100.  Then you can write your query using that condition.  I was having a similar problem where the processflow would fail becuase of a performance problem.  When I created the condition, it didn't loop through all the unneeded records.  If you don't think creating a condition would work, can you be more specific on exactly what you are trying to do?
          David Williams
          Veteran Member
          Posts: 1127
          Veteran Member
          8/28/2009 11:37 AM
            I'm not sure why you can't build your criteria into your DME call...
            Another option, if you are using PFI, is to use the SQL Query. My last client wanted to compare the results of two fields within a query but with a DME query you can't do that (except with a Branch).
            In a SQL query you can add a condition like field1 = field2 (from two different joined tables).
            David Williams
            Derek Czarny
            Veteran Member
            Posts: 63
            Veteran Member
            8/28/2009 12:01 PM
              You can probably do it in the DME criteria, but I found from performance aspect the database condition made it much better.
              Jamie
              Basic Member
              Posts: 9
              Basic Member
              8/28/2009 4:18 PM
                Ok Here' s what I'm trying to do. It's a lot easier to see in the screen shot. I'm not familiar with creating a condition in DBDEF. After seeing this, do you think this is something that could be done with that?
                We are still on 8.0.3 environment so we don't have PFI.


                1.                   “Invoice Line” query pulls all the detail lines on the invoice.
                2.   Then “PO Line” query pulls the info for the current invoice line for the PO.
                3.   In the assign node, using the information from both querys it calculates to see if there is missing receiving. There is actual subrtaction taking place, not just comparison. 
                4.   Then the branch sends it to the end of the query to retrieve the next invoice line if the line is not missing receiving. If it is missing receiving I would like it to stop processing and continue on. Right now I just have it flipping a flag that at least one line is missing receiving.





                Demi
                Veteran Member
                Posts: 67
                Veteran Member
                9/16/2010 1:15 PM
                  Jamie, Did you ever resolve this issue?