Forum Stats

  • 3,770,408 Users
  • 2,253,104 Discussions
  • 7,875,445 Comments

Discussions

Rounding value to nearest multiple

K.A.S
K.A.S Member Posts: 8
edited Sep 30, 2019 4:53PM in APEX Discussions

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.

cormaco

Answers

  • Ora_Learner890
    Ora_Learner890 Member Posts: 368
    edited Sep 26, 2019 7:23AM

    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
    M Tajuddin Member Posts: 505 Bronze Badge
    edited Sep 27, 2019 3:41AM

    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
    Rick-Pick Member Posts: 81 Bronze Badge
    edited Sep 27, 2019 7:57AM

    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
    ascheffer Member Posts: 1,929 Gold Trophy
    edited Sep 27, 2019 9:43AM

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

    cormaco
  • Alexandra Robin
    Alexandra Robin Member Posts: 1,676 Blue Ribbon
    edited Sep 27, 2019 1:05PM

    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
    Rick-Pick Member Posts: 81 Bronze Badge
    edited Sep 28, 2019 12:52PM

    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
    ascheffer Member Posts: 1,929 Gold Trophy
    edited Sep 29, 2019 6:37AM

    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
    Alexandra Robin Member Posts: 1,676 Blue Ribbon
    edited Sep 30, 2019 4:53PM

    Thanks, Rick. Almost!