1 2 Previous Next 20 Replies Latest reply on Dec 10, 2015 9:15 PM by IanRenwick

    This should be a simple calculation....but?

    MacAddict4

      Included is a screen shot that should show what I am trying to do.........but for the life of me, I cannot get this configured correctly:

       

      Sample_simple calculation.png

      1.  As shown, I created a "Purchase Price" field, a "Tax" field, a "Shipping" field and finally a field titled, "TOTAL"

      Although I managed to create a calculation that calculates sales Tax (but, for some reason it didn't include the currency symbol which I want it to show)  In this example the tax is calculated on 6.25% of the Purchase price ( Purchase price * .0625). The Shipping charge was entered manually.

       

      So now my primary dilemma. I cannot (as you see in the example) figure out why (how) to create a summary total of the three charges. I keep getting an error message saying: "This field cannot be used here because it would cause a circular definition".   * I use the same "setup" in an Excel spreadsheet and it works fine so what I am doing wrong here?

       

      If someone would be so kind as to enlighten me as to "exactly" how to rectify the foregoing issue then I have one more question. Can I (or how would I) configure my "Tax" field in such a manner that in the event the sale tax should vary and I would need to enter a different calculation percentage, in such a manner as to not affect ALL the tax percentages throughout my entire database?

       

      I feel that if someone could assist me in just getting off dead zero with this issue, I would (hopefully) begin to understand the total concept and be able to move forward.

       

      Thank you.

        • 1. Re: This should be a simple calculation....but?
          siplus

          Can you attach your file for us to gnaw at it ?

          • 2. Re: This should be a simple calculation....but?
            MacAddict4

            I'm sorry but I am not sure exactly what you are asking. "Attach my file"?  I could create a screen shot (which I did) but I don't understand how I would go about attaching my file. Sorry, maybe I am a total dunce

            • 3. Re: This should be a simple calculation....but?
              alquimby

              Click "Use advanced editor" on the upper right. Then you will have the attachment button (paper clip). Click that button to select the attachment.

              • 4. Re: This should be a simple calculation....but?
                fitch

                I wouldn't think that Price + Tax + Shipping would create a circular definition. But I'm fairly sure that will be taken care of in answering your question about changing the tax rate.

                 

                What you want to do is NOT use a calculated field for the tax amount, but rather make Tax a plain number field that auto-enters the amount based on your calculation. That way your existing records won't be modified when you update your tax rate, and your circular error should go away as well.

                • 5. Re: This should be a simple calculation....but?
                  IanRenwick

                  I have attached a simple version of your database (MacAddict4.fmp12).

                  I have added a Tax Rate_global field. Use this field to enter the current tax rate you want to use for new records.

                  When you add a new record the new Tax Rate field grabs the current Tax Rate_global value.

                  TheTax field is now an auto enter calculation that multiplies the Purchase Price by the Tax Rate value.

                  The TotalPurchase Price + Tax + Shipping

                  screenshot_386.jpg

                  If you change the Tax Rate_global value the historical Tax amounts and Tax Rate stay as they were until you change the individual record's Tax Rate figure.

                   

                  I have added a value list to the Tax Rate field based on the values used across all records.

                   

                  In relation to formatting the number as a percentage or currency, it is a matter of selecting the field in layout mode and under the Data tab of the Inspector there is a Data Formatting / Format option to select Currency or Percentage (plus other options) and specify the number of decimal points etc.

                  screenshot_385.jpg

                  • 6. Re: This should be a simple calculation....but?
                    keywords

                    I agree with Tom. Circular definitions are sometimes not obvious, but it will likely be in the definition of a feeder field rather than in the TOTAL calc.

                    • 7. Re: This should be a simple calculation....but?
                      MacAddict4

                      This is indeed very slick and works perfectly - but I don't understand how you linked the Tax rate fields with this Tax Rate_global field and wouldn't I still need to make the Tax field a calculation field and enter a calculation (like:  Purchase Price * Tax Rate) in order to arrive at a numerical value in the Tax field? But when I had made the Tax field a "calculation field" I saw no option to designate that field as currency.

                       

                      Is there a particular "mode" that I can enter so I would be able to actually view (either by double clicking on a field or some other means) what operators you entered to create these calculations (ie: the Tax Rate * the Purchase price)?

                       

                      And so you're saying that to arrive at the Total, one would need to enter a calculation (within the Total field:  Purchase Price + Tax + Shipping and that in doing so this would not result in getting a circular definition?  (btw- I need to get the proper "lingo" defined when discussing databases. What is the proper terminology for a line of fields (left to right) as shown in our sample layouts. Is this designated as a "column" or what?

                       

                      You and everyone have very helpful and I sincerely appreciate all your efforts and explanation but if I could just burden you a bit more, could you please provided an little more explanation to my questions above? I apologize for seeming so ignorant (or perhaps being so stupid but I am the type of person who needs to understand things from front to back and top to bottom before I fully grasp the concept. Thanks so much.

                      • 8. Re: This should be a simple calculation....but?
                        keywords

                        No need to apologise—we are all learners, just at different points along the road.

                        In your original screenshot you showed your database in Table mode. In this display, each line is a Row. Column refers to a vertical unit in a table, a horizontal unit is a row. More specifically, each ROW displays fields from a single Record in the database table that this layout is based on.

                        As regards your calc, the way you describe the setup now should work: make your TOTAL field a calculation field with the definition Purchase Price + Tax + Shipping. The suggestion by Tom is that you make the Tax a number field with an auto enter calc set (Purchase Price * Tax Rate). That fixes the tax amount as a standard number derived from a calc.

                        • 9. Re: This should be a simple calculation....but?
                          fitch

                          ...wouldn't I still need to make the Tax field a calculation field and enter a calculation (like:  Purchase Price * Tax Rate) in order to arrive at a numerical value in the Tax field? But when I had made the Tax field a "calculation field" I saw no option to designate that field as currency.

                           

                          To answer your first question, no you don't use a calculated field, but you do use a calculation, and here's where:

                          Go to Manage > Database. Double-click on the Tax field (or select it and click Options...). The first tab of the field's options is Auto-Enter. Next to where it says "Calculated value" click Specify...

                           

                          To answer your second question, you don't designate a field as currency at the Manage > Database level. There is no currency "type" of field, only Number fields. You can display a field as currency by going to Layout mode and using the Inspector's Data Formatting options.

                          • 10. Re: This should be a simple calculation....but?
                            MacAddict4

                            Thanks Tom. Didn't mean to ignore your helpful information but just my brain (what's left of it) is "swimming" trying to disseminate all the other information coming at me. Jeez, I feel like I'm back as a college Freshman and in need of 6-7 professors rather than just one. (btw - I'm 71 but hopefully not senile yet

                            • 11. Re: This should be a simple calculation....but?
                              dtcgnet

                              keywords wrote:

                               

                              No need to apologise—we are all learners, just at different points along the road.

                               

                              Couldn't agree more with this. Keep asking questions.

                              • 12. Re: This should be a simple calculation....but?
                                IanRenwick

                                Go to File > Manage > Database and choose the field tab at the top. Double-click on the Tax field. You can the click on the Specify...(calculated value) button. This will then display the calculation and enable you to edit if required.

                                screenshot_388.jpg

                                In relation to the Tax Rate field, when a new record is created a "calculated value" corresponding to the number in the Tax Rate_global field is entered. The Tax Rate field also has the "Do not replace existing value of this field" checked. Whilst you can manually update it a change to the Tax Rate_global field will not, in this instance, trigger a change to the existing Tax Rate field.

                                You should also note that the Tax Rate_global field is a "global" field and this holds one value for ALL records (click on the STORAGE tab on the Options dialog box to view how this works).

                                You will also be able to view the TOTAL field - which is currently a calculation field but could be an auto enter calculation field like TAX.  No circular reference....

                                screenshot_389.jpg

                                To answer another of your queries, the records are displayed in LIST view. (Go to menu VIEW and you will see 3 view options; Form/List/Table)

                                • 13. Re: This should be a simple calculation....but?
                                  MacAddict4

                                  Well Hallelujah, I was finally able, with of course the excellent help of all you fine gentlemen, to successfully complete a correct and workable computation of the; Price, Tax, Shipping and Total dilemma.  Now, again with your expert and gracious help and information, hopefully I will be able to set up my database as in your "MacAddict4.fmp12" sample which you so graciously created and allowed me to download.


                                  I do believe that I have taken more than enough of everyone's time and attention. I want to express my sincere thanks for everyone's unselfish time, attention and expert advice given me. This has certainly been a humbling experience - for years I had believed that I possessed a fairly good analytical mind and had excellent reasoning abilities - then along came FileMaker!  I hope this post stays on-line and available because I strongly suspect I will have to refer back to all this excellent information quite often......until I am able to "take off and fly solo". Thanks very kindly gentlemen and especially to IanRenwick who has gone the extra mile to assist me and answer all my inane questions.


                                  Oh now I see that this thread can be saved as a pdf - this is great!  I strongly suspect I will need to save and refer back to this......


                                  Roger Beltz   

                                  1 2 Previous Next