Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Calculated field Rounding Issues....

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Calculated field Rounding Issues....

    Hi All...

    So I have a UX with a calculated field. The field is dollar field that is set to round precision 2 with the round directed set to default.

    Screenshot from 2018-05-09 13-40-46.png

    The calculated field expressions is:

    if(STAMPING_FEE>1.00,(FEE_AMOUNT*TAX_PERCENT_AMNT)+STAMPING_FEE,FEE_AMOUNT*(TAX_PERCENT_AMNT+STAMPING_FEE))

    This formula works with one exception. If the dollar amount calculated comes to $1.88. You save the record. Close the UX that has the calculated field on it. Re-open that UX. It now rounds the amount from a 1.88 to a 1.90 and makes the UX dirty.

    Now...I don't know if it is the calculated field expression that is running again when the UX is opened or it is rounding again when it is open.

    My thought process was to maybe take the calculated filed expression off and put it on a button but I cannot figure out how to get that to work. I've tried many variations of this:

    Code:
    var stampfee={dialog.Object}.getValue('STAMPING_FEE');
    var feeamnt={dialog.object}.getValue('FEE_AMOUNT');
    var taxprcnt={dialog.object}.getValue('TAX_PERCENT_AMNT');
    if(stampfee>1.00) {calc= (feeamnt*taxprcnt)+stampfee}
    	else if(stampfee<1.00) {calc feeamnt*(taxprcnt+stampfee)};
    //var calc=if(stampfee>1.00,(feeamnt*taxprcnt)+stampfee,feeamnt*(taxprcnt+stampfee));
    {dialog.Object}.setValue('TOTAL_FEE',calc );

    Been trying to follow the examples on W3 Schools...

    Any ideas or suggestions are welcome.

    Running version v4.5.2
    Version: 12.4.5.2, Build 4770
    Build 5018

    Thanks
    Joe
    Never take a ride to the edge of your mind unless you've got a ticket back - Jon Oliva - Savatage.

    #2
    Re: Calculated field Rounding Issues....

    Joe - see if this quick video helps. Play Video
    Insanity: doing the same thing over and over again and expecting different results.
    Albert Einstein, (attributed)
    US (German-born) physicist (1879 - 1955)

    Comment


      #3
      Re: Calculated field Rounding Issues....

      I don't quite understand what is going on with the "STAMPING_FEE". Given the following expression:

      Code:
      if(STAMPING_FEE>1.00,(FEE_AMOUNT*TAX_PERCENT_AMNT)+STAMPING_FEE,FEE_AMOUNT*(TAX_PERCENT_AMNT+STAMPING_FEE))
      It seems like STAMPING_FEE is treated as a flat rate to be added to the value of (FEE_AMOUNT*TAX_PERCENT_AMNT) when STAMPING_FEE is > 1.
      But when it is <= 1.00 it seems that STAMPING_FEE is being treated as a percent value and it is added to TAX_PERCENT_AMT before multiplying by the FEE_AMOUNT:

      FEE_AMOUNT*(TAX_PERCENT_AMNT+STAMPING_FEE)

      Is this correct?

      I would verify your inputs, as I suspect the input values might be different when re-opening the form.

      Otherwise, if everything is correct, you could look at using the xBasic ROUND function, but that can be very problematic of knowing when to apply the ROUND.

      The AA documentation does not really describe the internal storage format for variables of type "N" in xBasic. I would treat the "N" variable type like numbers in JavaScript that are stored in some sort of floating point notation, like IEEE 754. These formats are terrible for dealing with money amounts were it is expected that all results will be rounded correctly to the penny. Google this: "money floating point numbers".

      Comment


        #4
        Re: Calculated field Rounding Issues....

        Hi Guys...

        Sorry for the delay...bad day.

        Thank you both for the input. Rich you are correct. So Stamping Fee can be a percentage or a flat fee depending on the state and/or municipality. I have a table that the UX looks up the fee based off of the state and/or municipality. If it is a percentage it is a value less the 1. Ex. 5% would be .05. When it is a flat fee say $15 it would be a value greater than 1. So the "if" statement I have works except when you save the value to the table then open up that UX component it rounds it again. Once it is calculated and saved I don't want Alpha to round anything. The calculation and rounding is done already. So my perfect example is the tax comes out to a $1.88 after the calculation is done. Save the record. Open the UX that has that value on it and Alpha rounds it to a 1.90 now. Not good...

        My thought was can I do all of that on a button and remove the rounding from the Alpha format definition. Then the only time a rounding and calculation would occur would be when a user intentionally clicks a button.

        If I can get the IF statement working on an onclick event then I can work on the rounding but I have not been able to even get that working yet.

        Thanks again...
        Joe
        Never take a ride to the edge of your mind unless you've got a ticket back - Jon Oliva - Savatage.

        Comment


          #5
          Re: Calculated field Rounding Issues....

          PS...the fields are all numeric with two decimals in a MariadB database.
          Never take a ride to the edge of your mind unless you've got a ticket back - Jon Oliva - Savatage.

          Comment


            #6
            Re: Calculated field Rounding Issues....

            What are some sample values for FEE_AMOUNT, TAX_PERCENT_AMNT, STAMPING_FEE that lead to this condition?

            Comment


              #7
              Re: Calculated field Rounding Issues....

              The fee amount could be anything...$50, $100, $250, etc. The Insurance Carrier charges/sets the fee. So an example would be NY - Tax is 3.6% (.036 in the table) and Fee is .0017.
              Never take a ride to the edge of your mind unless you've got a ticket back - Jon Oliva - Savatage.

              Comment


                #8
                Re: Calculated field Rounding Issues....

                Hold the phone...think I just found a problem. My table is truncating the percentage and rounding .0017 to .02. dB must be doing that as I don't have rounding setup on that field.
                Never take a ride to the edge of your mind unless you've got a ticket back - Jon Oliva - Savatage.

                Comment


                  #9
                  Re: Calculated field Rounding Issues....

                  Perhaps you need four decimal places in the DB to hold your tax rate.

                  Comment


                    #10
                    Re: Calculated field Rounding Issues....

                    Or, store as the percentage (3.60 and 0.17) and divide by 100 when using in calculations.

                    Comment


                      #11
                      Re: Calculated field Rounding Issues....

                      Hi Joe,

                      As a rule, I'm far more likely to use xbasic than javascript, but every once in a while I will challenge myself.
                      I didn't bind this to a table, but I think it works properly in an on-click event.

                      Code:
                      var stampfee={dialog.Object}.getValue('STAMPING_FEE');
                      var feeamnt={dialog.object}.getValue('FEE_AMOUNT');
                      var taxprcnt={dialog.object}.getValue('TAX_PERCENT_AMNT');
                      
                      
                      if(stampfee>1.00) {var calc= (feeamnt*taxprcnt)+(1*stampfee)}
                      	else  {var calc= feeamnt*(taxprcnt+stampfee)};
                      //var calc=if(stampfee>1.00,(feeamnt*taxprcnt)+stampfee,feeamnt*(taxprcnt+stampfee));
                      {dialog.Object}.setValue('TOTAL_FEE',calc );
                      Gregg
                      https://paiza.io is a great site to test and share sql code

                      Comment


                        #12
                        Re: Calculated field Rounding Issues....

                        I reworked the code a bit.

                        I think the code below actually gets the calculations done right.
                        I added the *1 to the var lines to force javascript to consider them numbers instead of strings.

                        Code:
                        var stampfee={dialog.Object}.getValue('STAMPING_FEE')*1;
                        var feeamnt={dialog.object}.getValue('FEE_AMOUNT')*1;
                        var taxprcnt={dialog.object}.getValue('TAX_PERCENT_AMNT')*1;
                        
                        
                        if(stampfee>=1.00) {var calc= (feeamnt+stampfee)*taxprcnt+feeamnt+stampfee}
                        	else  {var calc= (stampfee*feeamnt+feeamnt)*taxprcnt+(stampfee*feeamnt+feeamnt)};
                        //var calc=if(stampfee>1.00,(feeamnt*taxprcnt)+stampfee,feeamnt*(taxprcnt+stampfee));
                        {dialog.Object}.setValue('TOTAL_FEE',calc );
                        Gregg
                        https://paiza.io is a great site to test and share sql code

                        Comment


                          #13
                          Re: Calculated field Rounding Issues....

                          Like the video said
                          Number({dialog.Object}.getValue('STAMPING_FEE'));
                          Insanity: doing the same thing over and over again and expecting different results.
                          Albert Einstein, (attributed)
                          US (German-born) physicist (1879 - 1955)

                          Comment


                            #14
                            Re: Calculated field Rounding Issues....

                            Sorry for the delay in response. I changed the table to allow for more decimals. It was rounding the tax percentage so when I would do the lookup and put the right amount in the field everything worked as expected. Then when I saved it to the table the dB was rounding it. That is why when I opened the UX again it would change the amount. The original % changed. Sorry...my stupid mistake.

                            Gregg...the fields are all numeric. So I don't think I need the *1 in the formula. Either way I will play with it and see if it works. If it does then I have to add an additional clause to it. This is where the FEE_TAX_TYPE field = TAX. That field is a drop down. The value TAX is saved as the ID from the TAX_TYPE table. "TAX" is ID 13 in the table.
                            Never take a ride to the edge of your mind unless you've got a ticket back - Jon Oliva - Savatage.

                            Comment


                              #15
                              Re: Calculated field Rounding Issues....

                              JavaScript does some funky stuff.
                              Whether you use the 1* or the Number() that PeteConway showed us,
                              it will make all the difference.
                              Gregg
                              https://paiza.io is a great site to test and share sql code

                              Comment

                              Working...
                              X