7 Replies Latest reply on Sep 3, 2013 12:44 PM by CeciliaBaldwin

    Trouble With Calculations

    CeciliaBaldwin

      Title

      Trouble With Calculations

      Post

           I'm trying to make a couple of calculations, and I'm not sure what I'm doing wrong in the script. All my fields are in the same table, but when it goes to make the calculations, it either uses one field with reference or does nothing at all. I'm not certain if I also need to a georefresh button so that's it is certain to wait until all necessary information is included. (This is being done in a portal, in a TO table).

            

           First calculation is division -both fields are number fields - and read: 

           "Price per Unit" = Div ( price per quantity ; units per )

           Then, I want to take the average of the 6 most recent entries of the same field .I was originally going to make this field a summary field, selecting average of, but , as I need this field (called mean for z) to be referenced in a different portal, (say new field "portal 2 m" = look up of "mean for z" , but it will not read that value. If i do it as a regular number, it will; just not as a summary. 

            

           Again, I'm not sure if I need a refresh button or what is the best way for going about this. Appreciate any help!

            

        • 1. Re: Trouble With Calculations
          philmodjunk

               I can't see enough detail in how you've set up your tables and relationships to offer much help. ON the one hand you say "all my fields are in one table", but then you describe a portal and an average of values in a portal--which means you have a relationship and possibly a portal filter involved here.

               Div ( price per quantity ; units per ), BTW, is integer division. It returns the portion of the answer to the left of the decimal point and any fractional/decimal value is discarded.

               Div ( 5 ; 2 ) = 2 It does not return 2.5. To get 2.5 you would use 5/2 instead of the Div function.

          • 2. Re: Trouble With Calculations
            CeciliaBaldwin

                 Yes to many to many relationships - I'm trying to set up a recipe database that links ingredients with vendors and recipes .. I have 3 TOs to and have been confirming that my ids and references are correct. 

                 Regarding the division - below is a picture of the rudimentary file & portal. I'm trying to make the $ per unit = div ( $ per quantity ; units per) . And all that it does is add zero ... (The $1 was because I wrote that in). And thank you for pointing out that it is integer division ; is there any way to make it a preference to leave the decimal value? 

                  

                  

            • 3. Re: Trouble With Calculations
              SteveMartino

                   So you basically want to make $ per unit divide the $ per quantity by the Units Per?

                   If so, simply change the "$ per unit" field in Manage Database>Fields to a calculation.  Make the calculation under 'Specify Calculation' as flollows:

                     Click the '$ per quantity' field, click the '/' operator, click the 'Units Per field'  (no quotes). Make sure calculation result is 'Number', and click the "Do not evaluate if all referenced fields are empty".  Which looks like what you may have.

                   Then in layout mode, in the inspector>data tab, format>Currency (should already be 2 decimal places).

              • 4. Re: Trouble With Calculations
                CeciliaBaldwin

                     Thanks Steve. I did all of that but it still either puts 0.00 or ? ... I'm not sure what else to try and do. 

                • 5. Re: Trouble With Calculations
                  SteveMartino

                       Are 2 fields the calculation references both of type 'Number'?  And in Specify Calculation, the result is selected as 'Number' ?

                       You sometimes get a ? if the field isnt wide enough but I don't expect that to be the problem. 

                       Can you post a screenshot of your Specify Calculation box?

                  • 6. Re: Trouble With Calculations
                    philmodjunk

                         If you are dividing and get a ?, most likely reason is that you've divided by zero. I suggest posting the actual expression from the Specify calculation dialog so that we can see it. The details make a difference. A screen shot of this dialog would be best.

                         

                              is there any way to make it a preference to leave the decimal value?

                         Seems like you didn't read my previous post completely as I posted an example which Steve Martino has now explained in more detail on how to get the correct decimal result.

                    • 7. Re: Trouble With Calculations
                      CeciliaBaldwin

                           Actually, the problem seemed to be that I had it set up as an 'auto enter calculation'  instead of calculation and now it seems to be working perfectly! thank you both!