Name	Points
Greg Moeller	4184
David Williams	3349
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1377
Roger French	1315
mark.cook	1244
Chris Martin	825

PF IOS API Builder - One to Many query

3 Replies

0 Subscribed to this topic

53 Subscribed to this forum

Sort:

Author

Messages

Karen Sheridan

Veteran Member

Posts: 142

New Poster

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

New Poster

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

New Poster

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

New Poster

6/25/2011 12:41 AM

If you make POLINE your primary table, you should get all of the lines.