SAP B1 Query

[former_member366557]

Dear Sir/Mam

Please help me to fix this query, what is wrong with this,  i am so sory iam new with regards to queries...

SELECT (T1.OcrCode )

set T0.U_FeederVessel = T1.[OcrCode]

FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.DocNum = $[$8.0.0]

Group by T1.OcrCode

All i want for e.g. i have a value from INV1.OcrCode  for example "AAA" then i want it to set this value into UDF "FeederVessel"

thank you

regards

Silent

[former_member186712 · Accepted solution]

Hi Silent,

Why do you need this value in a UDF if it's already in the lines only the field is different?

[former_member366557 · Accepted solution]

august thank you for your response, i need this on my report parameter

can you fix this query  sir

currently it works but all i want is  automatically put the result on the UDF with thicking any fields

SELECT (T1.OcrCode)

FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.DocNum = $[$8.0.0]

Group by T1.OcrCode

[SujG · Accepted solution]

After you link the formatted search, define Auto Refresh When Field Change and selected which is your trigger to auto populate the value.

[former_member186712 · Accepted solution]

This below query retrives the OCRCode from the line.

I'm not sure if this is what you need.

Select $[$38.2004]

[former_member227598 · Accepted solution]

Hi Silent,

Please try below FMS Query....

SELECT PrcName FROM OPRC  where Prccode =(Select $[$38.2004])

Rgds,

Kamlesh Naware

[former_member366557 · Accepted solution]

currently it works but all i want is  automatically put the result on the UDF WITHOUT  thicking any fields

SELECT (T1.OcrCode)

FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.DocNum = $[$8.0.0]

Group by T1.OcrCode

[former_member366557 · Accepted solution]

sir your query is working, but how can i set in the UDF to put this value automarically without thicking any other fields.. i want it to automatically  update my UDF with this value..

thank you

regads

silent

[former_member227598 · Accepted solution]

Hi Silent ,

After you link the formatted search, define Auto Refresh When Field Change and selected which is your trigger to auto populate the value.Select the dimension fields.

1. Goto the UDF (or) any Default(Original) Field and Click ShiftAltF2.

2. Select the SEARCH BY SAVED QUERY.

3. Assign the FMS Query.

4. Select the AUTO REFRESH WHEN FIELD CHENGES.

5. Select the require Auto refresh field

6. Check the Refresh Regularly (or) Display Saved Values.

Rgds,

Kamlesh Naware

[former_member366557 · Accepted solution]

Thank you i think it works now.

[SujG]

Hi Silent,

Are you trying to add a formatted search to invoice document line ?

In your query second line has syntax issue, set is not a valid command in select

If you are trying to add a formatted search on the field press ctrl+alt+tab and link your saved query.

Thanks,

Sujit