• Search Forums
    Advanced Search Topics Posts
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 Send Private Message
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 Send Private Message
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 Send Private Message
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 Send Private Message
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