Filter On Decimal-ed Values

(0) Share

Report

Posted on by themrmystery

529

Is there a way in NAV 2013, to do a filter for values containing a decimal (.)

I've attempted the normal *.* (to return all values that contain a .), it did not work. Stating "You cannot enter '*.*' in Decimal."

Reasoning. We have an integrated Website with NAV, it can read our inventory, and users can enter orders into NAV through the website. This website however will not allow users to checkout with any part that does not have a whole number for qty on hand.

Thus users are getting errors such as: Your order of 2 x PART is on backorder. There are 105.3333 in Stock and 2 on Backorder. Obviously there is enough to handle it. By clearing the decimal value, the website allows the checkout.

Our product is such that nothing should ever have a decimal value, but accidents happen. With over 2000-3000 saleable product, and 10,000s+ more of components in NAV, it is tough for us to search through them all.

Any helpful tips on finding Qty's with Decimal values, would be appreciated.

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (1)

Community Member on at

Like (0)

Report

You could make a report using MOD functions.

qty test := Int(quanitiyfield);

remainder := quantityfield MOD qtytest;

Was this reply helpful? Yes No
Suggested answer

keoma 32,729 on at

Like (0)

Report

hi,

try following filter for decimals: (set a value for "int-minvalue")

>int-minvalue&<int-minvalue+1

i tested it with the items list and field "unit cost":

sample int-minvalue = 1.1

so you get the filter to get all records with a "unit-cost" value between 1.1 and 1.2

>=1.1&<1.2

Was this reply helpful? Yes No
themrmystery 529 on at

Like (0)

Report

While that works for very limited uses. Example, do I have 1.0 or 1.53, it does not work for finding all decimal values.

I was hoping to find a way to filter to all values that have anything beyond the decimal. I have Quantity on Hand on some items of excess 50,000.... I don't want to have to filter >1&<2 to find all 1.01 - 1.99 and >100&<101 to find all 100.01 - 100.99 ... I'd have to run 50,000+ filters to find them all.

I need something that ignores whole numbers and only returns values that contain decimals...

Was this reply helpful? Yes No
Verified answer

keoma 32,729 on at

Like (0)

Report

hi,

ok then. in the table you use define a new boolean field, which checks, if this amount value is a decimal or int value. so you get true/false for filtering. this new field must not be a flowfield! (flowfields cannot be used as filters). calculate the value within the amount_onvalidate trigger.

to check if the amount value is a decimal or not:

define var. IsDecimal (boolean)

IsDecimal := ((Amount div 1) - Amount) <> 0;

OR

IsDecimal := ROUND(Amount,1,'=') - Amount) <> 0;

Was this reply helpful? Yes No
Sreejith Muralidharan 20 on at

Like (0)

Report
You can use a SQL query to filter the results. e.g. For sales lines

SELECT * FROM DATABASE.Sales Line WHERE CONVERT(int,Quantity) <> Quantity

Was this reply helpful? Yes No