UDF query

[Lartey]

Hello Experts,

I need some help on this scenario.

I have to do a query and use it in a user defined formatted searches to divide the LineTotal by the Quantity on the INV1 table. I need this done on screen during invoice creation.

Can anyone help me with the script writting?

Thank you.

John

[Former Member · Accepted solution]

If you insist on getting the query, here you are:

IF $[$38.11.Number] > 0
SELECT $[$38.21.Number]/ $[$38.11.Number]

[Former Member]

Hi John,

I have no idea why you need this UDF in the first place because linetotal is calculated by system fields. You can get the same from those system fields, what is this UDF used for?

Thanks,

Gordon

[felipe_loyolarodriguez]

John

save the query even if error and run it from the Invoice

Remember that the amount of items must be greater than 0

Atte.

FLR

[Lartey]

Hi Gordon,

Stock items are received into stock in a foreign currency i.e. Euro. As a result when invoicing the unit price is displayed in Euro irrespective of the currency you select. We do not want to receive the stock in local currency nor change the prices manually to local currency since we'll have difficulty getting last purchased price being suggested in Euro during POs.

This UDF and formatted search is therefore required for informational purposes so that the user has an idea of the unit price in the local currency. I have actually set an auto refresh on change of Quantity.

Hope this clarifies.

John

[Former Member]

I see. That is ok then. Have a nice weekend!

[felipe_loyolarodriguez]

Hi

Try This

SELECT (SELECT $[$38.21.Number])/(SELECT $[$38.11.Number])

FLR

Edited by: Floyola on Feb 24, 2011 10:37 AM

[Lartey]

Hello FLR,

Your script worked but anything the value is computed i get the error below and thus unable to save invoice:

Invalid numeric value (ODBC -1030) [131-183]

After assigning the query to that field, i always get this error anytime i launch the AR Invoice:

1). [Microsoft][SQL Native Client][SQL Server]Divide by zero error encountered.

'Withholding Tax' (OWHT)

Please help!

John

Edited by: John Lartey on Feb 24, 2011 3:07 PM

[Former Member]

Hi John.......

Try this....

Select (Select $[INV1.LineTotal.Number])/(Select $[INV1.Quantity.Number])

Apply above FMS in UDF so that it will divide Line total by Qty.....

Regards,

Rahul

[Lartey]

Hi Rahul,

I tried it but got this error:

1). [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near ')'.

2). [Microsoft][SQL Native Client][SQL Server]Statement 'User-Defined Values' (CSHS) (s) could not be prepared. 24/02/2011 13:33:56 -1

As a matter of fact i did this same script ealier.

Any suggestions why it's not working?

John

[Former Member]

Hi John Lartey ,

you can try this.

 SELECT $[$38.21.0] /  $[$38.11.0]

if AR is service type. Quantity = 0 => error.

thanks

H2

Edited by: Hoe Pham Huy on Feb 24, 2011 3:00 PM

[Former Member]

Hi John....

This will definitely work for you.....

Try this.....

declare @var1_Tot as nvarchar(255)
declare @var2_Qty as nvarchar(255)
declare @var3 as float
declare @var4 as float
set @var1_Tot=$[$38.21.0]
set @var2_Qty=$[$38.11.0]
set @var3=cast( substring(replace(@var1_Tot,',',''),0,len(replace(@var1_Tot,',',''))-4) as float)
set @var4=cast(substring(replace(@var2_Qty,',',''),0,len(replace(@var2_Qty,',',''))-3) as float)
select @var3 / @var4

Regards,

Rahul