Approval Query for Sales Order

[JeffWill]

I am trying to create a query that will fire the approval process for a Sales Order if the BP Code is C99998 and the DocTotal is greater that $10000. I'm using the query below but it is not working.

I have tried to use this as a formatted search for a UDF just to test it and it doesn't work either, I get the "Internal error (-1003) occurred [Message 131-183]" error message.

What is wrong with my query??

select distinct 'True'

from ORDR

where $[29.0.91] >= 10000

and $[$4.0.1] = 'C99998'

and DateDiff(dd,DocDate,GetDate()) = 0

[former_member312729]

Hi Jeff

Try below code

select distinct 'True' from ORDR
where 
 $[ORDR.Cardcode.0] = 'C99998'
and $[$29.0.number]  >=10000
and Datediff(Day, $[$10.0.date], getdate())='0'

Regards:

Balaji.S

[JeffWill]

Thank you Balaji but I found the error is in how the formatted search is looking at the document. The FS sees '$ 630.00' and not an integer.

[former_member312729]

Hi ,

You can also use this cast($[ORDR.DocTotal] as Numeric)>=10000

[JeffWill]

After further testing I have found that the query above is failing because the DocTotal field in the Business One interface actually contains "$ 630.00" not an integer as I would expect.

The formatted search gives the error "1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Conversion failed when converting the nvarchar value '$ 630.00' to data type int. FMS execution failed on field 'U_CRemarks' with query name 'SO Over 10K 030619''

I found a way to convert the "$ 630.00" to an integer REPLACE(DocTotal,'$ ','') but you cannot use it in the WHERE clause.

Any ideas on how I can this to work?