cancel
Showing results for 
Search instead for 
Did you mean: 

Formatted Search

Former Member
0 Kudos
141

Hi Experts,

Can any body explain the syntax about how to create Formatted Search for Purchase Order..?

below is my query based on Screen field

$[$88.1.0]  --(Serie) 

but its not working.

SELECT nnm1.seriesname str(opor.docnum) from
opor inner join nnm1 on opor.series = nnm1.series
where opor.series = $[$88.1.0]
and opor.docnum = $[$8.1.0]

i appreciate your help..

Thanks and Regards,

kaviprashu

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Kavi......

The syntax you discussed is for Matrix Level.

And you are asking about Header Level FMS which is of Document Series.

For Header/Row Level, Syntax always should be

$[TableName.FieldName.DataType]

Above Syntax works in both header and row level case..........

Regards,

Rahul

Former Member
0 Kudos

Hi,

Thanks Rahul,

the query is working fine

where opor.series = $[OPOR.SERIES.NUMBER]  AND      -- no exception
OPOR.DOCNUM = $[OPOR.DOCNUM.NUMBER]

but in my user_field Query is not displaying Series and DocNum...

SELECT  NNM1.SERIESNAME.CHARACTER +  str(opor.docnum) from
opor inner join nnm1 on opor.series = nnm1.series
where opor.series = $[OPOR.SERIES.NUMBER]  AND      -- no exception
OPOR.DOCNUM = $[OPOR.DOCNUM.NUMBER]

i appreciate your help..

Thanks and Regards,

kaviprashu

Former Member
0 Kudos

Hi Kaviprashu,

Try this first:

SELECT  T1.SERIESNAME+ str(T0.docnum)
from opor T0
inner join nnm1 T1 on T0.series = T1.series
where T0.DOCNUM = [%1] AND T0.series = [%0]

Thanks,

Gordon

Former Member
0 Kudos

Hi,

Thanks Gordon,

your query is working fine...

but i not able to execute this query in Formatted Search for user defined Screen Field of Purchase Order.

is there any alternative way to create Formatted Search..?

i appreciate your help..

Thanks and Regards,

kaviprashu

Former Member
0 Kudos

Try this then:

SELECT T1.SERIESNAME + str(T0.docnum)

from opor T0

inner join nnm1 T1 on T0.series = T1.series

where T0.DOCNUM = $[$8.0.number\] AND T1.series = $[$88.0.number\]

Former Member
0 Kudos

Hi,

Thanks Gordor,

the Query is throwing following exception..


1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near '$8.0.number'.
2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement 'Document' (RDOC) (s) could not be prepared.

but if i write


SELECT NNM1.SERIESNAME + STR(OPOR.DOCNUM)
INNER JOIN NNM1 ON
NNM1.SERIES = OPOR.SERIES
WHERE OPOR.SERIES = $[OPOR.SERIES.NUMBER]

the above code is executing,.. but query is returning null value hence

user defined field 'U_ORDER_NO' is not retrieving Series and Docnum...

is there any alternative way..

i appreciate your help..

Thanks and Regards,

kaviprashu

Former Member
0 Kudos

Try:

SELECT T1.SERIESNAME + str(T0.docnum)
from opor T0
inner join nnm1 T1 on T0.series = T1.series
where T0.DOCNUM = $[OPOR.DOCNUM.NUMBER] AND T1.series=$[OPOR.SERIES.NUMBER]

Former Member
0 Kudos

Hi,

Sincere Thanks to all of you SAP Gurus for assisting me on Formatted Search, my problem is solved..

Thanks and Regards,

kaviprashu

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi,

Try this method

SELECT  NNM1.SERIESNAME.CHARACTER +  str(opor.docnum) from
opor inner join nnm1 on opor.series = nnm1.series
where opor.series = $[$88.0]  AND      
OPOR.DOCNUM = $[$8.0]

Edited by: Thillai on Jan 17, 2012 7:35 AM

Former Member
0 Kudos

Hi,

Thanks Thillai,

following exception is showing..

[Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot call methods on nvarchar. 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement 'Withholding Tax' (OWHT) (s) could not be prepared.

i am writing the Formatted Search for Purchase Order for user defined Screen Field, 'U_ORDER_NO',

my requirement is while creating new Purchase Order,

on change of Vendor code, Screen Fields, Series No. and Document Number should show in 'U_ORDER_NO'

i appreciate your help..

Thanks and Regards,

kaviprashu.

former_member682029
Contributor
0 Kudos

Try

$[$88.0.0].

The second digit is 1 only valid for grid / matrix.

Thanks

Former Member
0 Kudos

Hi,

Thanks Anoop, but syntax

$[88.0.0]

is not working

below is the query,

SELECT  NNM1.SERIESNAME.CHARACTER +  str(opor.docnum) from
opor inner join nnm1 on opor.series = nnm1.series
where opor.series = $[88.0.1]  AND      -- runtime exception, incorrect syntax near '88.0.1'
OPOR.DOCNUM = $[8.0.1]

but if i write

where opor.series = $[OPOR.SERIES.NUMBER]  AND      --  no exception
OPOR.DOCNUM = $[OPOR.DOCNUM.NUMBER]

but the below query is not returning Series num and Docnum in userfield..

SELECT  NNM1.SERIESNAME.CHARACTER +  str(opor.docnum) from
opor inner join nnm1 on opor.series = nnm1.series
where opor.series = $[OPOR.SERIES.NUMBER]  AND      -- no exception
OPOR.DOCNUM = $[OPOR.DOCNUM.NUMBER]

i appreciate your help..

Thanks and Regards,

kaviprashu