Formatted Search

[former_member264311]

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

[former_member264311]

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

[azizelmir]

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]

[azizelmir]

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

[agustin_marcoscividanes]

Hi

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

Kind regards

Agustín Marcos Cividanes