cancel
Showing results for 
Search instead for 
Did you mean: 

Approval Query based on Department

0 Kudos

Hello Experts!

I'm currently creating a query for my Approval Template that will be dependent on the Department (Dimension 2) entered by the user on the Purchase Request document.

Per department will run through a separate Approval Template. Initially I have created a query for Department 201 but it doesn't seem to work. What am I missing in the query?

SELECT DISTINCT 'TRUE' FROM OPRQ A INNER JOIN PRQ1 B ON A.DocEntry = B.DocEntry WHERE B.OcrCode2 = '201'

Would really appreciate your help, Experts. Thank you!

Accepted Solutions (1)

Accepted Solutions (1)

former_member233854
Active Contributor
0 Kudos

You should use

SELECT 'TRUE' WHERE $[$38.COLUMNNUMBER.0] like '%;201%'

- Please, check your column number and replace on the query.

0 Kudos

Hi Mr. Danilo Kaspparian!

Thanks for your query! I tested it our but it didn't work at first then I modified the code and removed the ; from '%;201%'. Now the query I used is this SELECT DISTINCT 'TRUE' WHERE $[$38.2003.0] like '%201%'.

Thank you for your assistance!

0 Kudos

Hi Mr. Danilo Kasparian!

How about if I would add another condition where the DocTotal of the Purchase Request is 1,000 as well? Should it be structured this way SELECT DISTINCT 'TRUE' WHERE $[$38.2003.0] like '%201%' and $[OPRQ.DocTotal] > 1000?

Thanks!

former_member233854
Active Contributor
0 Kudos

Hi,

I didn`t test but I would use the field ID as well, at least you re-use the same query for other documents.

This should help you.

https://blogs.sap.com/2013/10/30/syntax-of-formatted-search-in-sap-business-one-version-for-sap/

See Syntax #2: Using Field Item UID in Screen

Answers (0)