Skip to Content

Formatted Search

Good Day Experts.

I was create UDT for Sales Qutation, my formula is "Stock on hand" - "ending bal" = Quantity

Now i created fms for quantity :

SELECT t1.onhand-t2.u_EndB

FROM OITM T0 INNER JOIN OITW T1 ON T0.[ItemCode] = T1.[ItemCode]

left join qut1 T2 on t1.itemcode=t2.itemcode left join oqut t3 on t2.docentry=t3.docentry

WHERE T1.[WhsCode]='FGResale' and T1.Onhand>0 and T1.ItemCode=$[$38.1.0]

When I put it to UDV and run the output was 0 quantity.

thanks

capture.png (28.9 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Dec 14, 2018 at 07:30 AM

    Oops, it seems your UDF "EndB" is a non numeric field.

    You can adjust your FMS as follow:

    SELECT t1.onhand - (Select CASE WHEN Isnumeric(t2.u_EndB) = 1 THEN CONVERT(DECIMAL(18,2),t2.u_EndB) ELSE 0 END) FROM OITM T0 INNER JOIN OITW T1 ON T0.[ItemCode] = T1.[ItemCode] left join qut1 T2 on t1.itemcode=t2.itemcode left join oqut t3 on t2.docentry=t3.docentry WHERE T1.[WhsCode]='FGResale' and T1.Onhand>0 and T1.ItemCode=$[$38.1.0]

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 13, 2018 at 03:47 PM

    Dear Romel Catalogo,

    Make sure to fill a value in the UDF "EndB" because your query is using it.

    This field was not showing in the screenshot. Go to <Form Settings>, choose <Table Format> select this field and make it Visible.

    Thank you,

    Aziz El Mir

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 12, 2018 at 01:54 PM

    Hi

    if you run this query with this item do you obtain the right value?

    Kind regards

    Agustín Marcos Cividanes

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 19 at 03:28 AM

    Hi Thanks to all,

    I Already solve my problem, in that form Sales Quotation doesnt allow 0 quantity, but in A/R Invoice does.

    Thanks

    Add comment
    10|10000 characters needed characters exceeded