Prev
Next
Forums Integration / Customization IPA/ProcessFlow

PF IOS API Builder - One to Many query

 3 Replies
 0 Subscribed to this topic
 52 Subscribed to this forum
Sort:
Author
Messages
Karen Sheridan
Veteran Member
Posts: 142
Veteran Member
6/24/2011 6:23 PM
    We are trying to query purchase order and po line data

    PROD=TESTSCM&FILE=PURCHORDER&FIELD=COMPANY;PO-NUMBER;POLINE.LINE-NBR&SELECT=PO-NUMBER=20126588&OUT=CSV&DELIM=~&OTMMAX=20

    But, the call is only returning one line; this po has 3

    I added the OTMMAX parameter to see if that would help and it doesn't

    Is there a limitation or a switch or another parameter?

    Our SQL query returns the correct data; the API does not

    TIA,
    Karen

    Karen Sheridan
    Veteran Member
    Posts: 142
    Veteran Member
    6/24/2011 7:14 PM
      So, If I change the call to this:
      PROD=TESTSCM&FILE=PURCHORDER&FIELD=COMPANY;PO-NUMBER;POLINE.LINE-NBR&SELECT=PO-NUMBER=20126588&OUT=CSV&DELIM=~&OTMMAX=600&OTMDELIM=*

      I get:
      COMPANY~PO-NUMBER~POLINE.LINE-NBR
      1~ 20126588~1*2*3*4*5*6*7*8*9*10*11*12*13*14*15*16*17*18*19*20*21*22*23*24*25*26*27*28*29*30

      I was expecting a new row for each line number.
      Karen Sheridan
      Veteran Member
      Posts: 142
      Veteran Member
      6/24/2011 7:18 PM
        In the Excel Query wizard - I do get one row for each line number

        PROD=TESTSCM&FILE=PURCHORDER&FIELD=COMPANY;PO%2DNUMBER;POLINE%2ELINE%2DNBR&SELECT=PO%2DNUMBER%3D20126588&OUT=CSV&DELIM=%09&NOHEADER=TRUE&OTMMAX=100&OTMDELIM=~&NEXT=TRUE&MAX=600
        David Williams
        Veteran Member
        Posts: 1127
        Veteran Member
        6/25/2011 12:41 AM
          If you make POLINE your primary table, you should get all of the lines.
          David Williams