Rounding value to nearest multiple

K.A.S

I have a value that always needs to be rounded to a nearest multiple, usually 4 or 6 (based on another value). We usually deal with values in the hundreds, but this number must always be rounded to a multiple. As data entry is manual, this often gets messed up.

Is there a way to automatically round the number up, on submit? Or even better, is it possible to have a dropdown appear under a number field that will show the rounded up number as a user types, so that they can click it?

Think, someone ordering a bunch of individual items, that come in bundles of 4, or 6.

Ora_Learner890

we are using a Setup table which contains different values to round to and we are using a simple query ( within PL/SQL code ) to select from this table for Rounding, you can setup it and use it which will give you peace of mind hopefully.

you can create a function, pass your value to round it with Your_Search_Criteria and return the Rounded Value.

declare  mDecimal  number;  mVal  number := 150.784950198;begin  select dec_val into mDecimal from decimal_setup where dec_type = "Your_Search_Criteria";  mVal := Round(mVal, mDecimal);end;

M Tajuddin

Cannot you use a modulus and round function together to come up with a solution? Please give us an example of a data entry what are you exactly trying to achieve?

Rick-Pick

Hi K.A.S,

Yes you will have to write a function to return the next multiple. It sounds like the number to round to - the multiple -  depends on the item. In that case you obviously will have to hold that number in the table containing details of the items. I guess you probably already have this in the record you are working with. The best way would be to create a packaged function in the database which you call from APEX when needed, for example in the page process which saves the record or from a dynamic action.

As suggested by M Tajuddin you can use the MOD function. MOD takes two number arguments and returns the remainder of the first number divided by the second. In this case the first argument is the number entered by the user and the second is the size of the multiple. If the entered number is a correct multiple the result will always be 0.

So if "m" is the multiple and "e" is the entered number, the formula:  x = m + e - MOD( e, m ) , does what you want - almost. It rounds to the nearest multiple except that it does that even if the entered number is an exact multiple. Without resorting to program logic, you could get round this by subtracting 1 from the entered number, so: x  =  m + e  - 1 - MOD( e - 1, m).

Rick

ascheffer

Or simply x = round( e/m ) * m

Alexandra Robin

Not sure if this is kosher, but this is what I did:

I created a table called TEST_TABLE

TEST_TABLE_ID

ENTER_NUMBER

RESULT

Then created a form on a table with report with two items

ENTER_NUMBER is a database column

RESULT is SQL Query (Return Single Value)

SELECT CASE WHEN ENTER_NUMBER <= 6 THEN 6WHENENTER_NUMBER > 6 ANDMOD(ENTER_NUMBER,6) = 0THEN ENTER_NUMBER      ELSE        6 * TRUNC(ENTER_NUMBER/6) + 6       ENDFROM AA_TEST_TABLEWHERE TEST_TABLE_ID = :P47_TEST_TABLE_ID;

Basic Insert/Update Processes

INSERT INTO TEST_TABLE (TEST_TABLE_ID,ENTER_NUMBER,RESULT)VALUES (:P47_TEST_TABLE_ID,:P47_ENTER_NUMBER,:P47_RESULT);

UPDATE AA_TEST_TABLESET ENTER_NUMBER = :P47_ENTER_NUMBER,RESULT = :P47_RESULTWHERE TEST_TABLE_ID = :P47_TEST_TABLE_ID;

Enter any number into the ENTER_NUMBER field, click SAVE/CREATE and it will round up to the nearest multiple of six.

Rick-Pick

Yes, you're right, except that would round would round down as well as up. In this case he/she is dealing with bundles of 4, 6 or 100 etc items. With a multiple of 6 and an entered value of say 7, the result should be 12 not 6. However, your formula works if you use "ceil" instead of round.

So I think x = ceil( e/m ) * m is probably the simplest and best answer. Thanks!

ascheffer

I know the difference between round up, round down and round to. It seems you know the difference between round up, round down and round to. So why assume that OP means round up when he says "this number must always be rounded to a multiple"?

Alexandra Robin

Thanks, Rick. Almost!