10 Replies Latest reply on Nov 14, 2016 3:29 PM by philmodjunk

    Create alert if calculation field value is out of range

    HawkGX

      Our company's printing quotes and order database currently calculates the profit margin for each job.  It's basically set up to allow us to quote prices for up to 4 quantities per job (Quantity Fields A, B, C and D) as well as enter our raw cost for each quantity. What we want it to do is to alert the user whenever the calculated margin for any of the Quantities falls below a certain value.

       

      For example... our User enters in a raw cost and a customer cost for Quantity Field A and Quantity Field B.  Margin Field A and Margin Field B are calculation fields that determine what our margin is for each of those quantities.  Let's say the threshold for the acceptable margin value is 0.50.  The User's manually entered customer cost for Quantity Field A results in a Margin Field A value of 0.60. That value meets our margin threshold requirements.  However, the User's manually entered customer cost for Quantity Field B results in a Margin Field B value of 0.45.  In that case, we would want the database to alert the User somehow that the price they entered resulted in an unacceptable Profit Margin for Quantity B.

       

      Does this sound possible within Filemaker Pro?

        • 1. Re: Create alert if calculation field value is out of range
          erolst

          First thing I want to is to advise you to normalize your database. Fields A, B ... is not a good structure and will be problematic down the road.

           

          Create a new line item type table with just fields for customer cost and margin (and if course, primary key and Job foreign key). Now you can add as many quotes per job as you like, or limit it to 4 quotes per job. You can display them in a portal, and if desired, control creation and deletion via script.

           

          Now it is much easier to monitor the pertinent fields, because there are only two if them, rather than 4 sets of two.

           

          For your requirement, that is absolutely possible, e.g. with a script trigger: on enter, your triggered script stores the current value, and on exit it checks the margin calculation. If the margin is below the threshold, restore the original value and issue an alert (dialog box). If you feel adventurous, let the script calculate the required value to just hit the minimum margin, and insert that.

          • 2. Re: Create alert if calculation field value is out of range
            philmodjunk

            It is possible and there are multiple options for doing so.

             

            Out of range values can change color.

            A graphic indicator previously invisible can become visible

            A text message built into the layout and previously invisible can be come visible.

            A custom dialog message can pop up when the user clicks a button to indicate that data entry is now complete if any values are out of range.

            An email could be sent out warning that a quote has an out of range value.

             

            The devil is in the details, both in terms of what type of warning works best and in how you have designed your layout and its underlying data model.

            • 3. Re: Create alert if calculation field value is out of range
              HawkGX

              Um, yeah... I'm definitely NOT an advanced user.  Been using FMPro a long time, just not at the level most of you are talking about here.  This database has been around and evolving for about 15 years now, and the 4 quantity limitation has rarely, if ever, been an issue for us.  So I'm not overly concerned with changing over to using portals or anything like that at the moment.

               

              If I'm understanding erolst' reply, then our users would enter their manual costs as they usually do. Then, once they're done entering those costs, they trigger a script that "on enter, your triggered script stores the current value, and on exit it checks the margin calculation. If the margin is below the threshold, restore the original value and issue an alert (dialog box)."

               

              Is that a correct general assessment of that method???

               

              The DB is already programmed to calculate a "suggested" customer price, but it's really only when a user manually enters and "override" price where we want them to be alerted if the margin is below the threshold. 

               

              Hope I'm making some sense here...

              • 4. Re: Create alert if calculation field value is out of range
                philmodjunk

                Take a look at conditional formatting (for color/text style changes) and Hide Object When (to make hidden indicators visible when a value is out of range).

                 

                These options can usually be implemented without any need for a script and usually don't require a major modification of your existing design.

                • 5. Re: Create alert if calculation field value is out of range
                  erolst

                  HawkGX wrote:

                  If I'm understanding erolst' reply, then our users would enter their manual costs as they usually do. Then, once they're done entering those costs, they trigger a script that "on enter, your triggered script stores the current value, and on exit it checks the margin calculation. If the margin is below the threshold, restore the original value and issue an alert (dialog box)."

                  In essence, yes, except that your users don't have to trigger anything; the triggering happens automatically, and as long as your values are in range, no dialog will appear.

                   

                  The approach you should take depends on the effort you want to invest (not really that much, but you have to do it right) and how strict you want/must enforce your business rules. A warning sign is helpful, but could be overridden.

                  • 6. Re: Create alert if calculation field value is out of range
                    erolst

                    philmodjunk wrote:

                    These options can usually be implemented without any need for a script and usually don't require a major modification of your existing design.

                    Using a script trigger doesn't require a major redesign, either. HawkGX just should be aware that whatever method he chooses, he needs to implement it for four sets of fields.

                     

                    And when it comes to not properly normalised database designs, I think the potential for optimisation by re-design should at least be mentioned. It can still be ignored.

                    • 7. Re: Create alert if calculation field value is out of range
                      HawkGX

                      Not sure I understand how to set it up so that the script triggering is automatically done.  Is it based on user data entry into a field?

                      • 8. Re: Create alert if calculation field value is out of range
                        HawkGX

                        I totally agree on mentioning steps to optimize / normalize the database in question.  While it may not necessarily be required in this instance for our particular DB, I think it's good in general to keep the idea in mind.

                        • 9. Re: Create alert if calculation field value is out of range
                          erolst

                          HawkGX wrote:

                          Not sure I understand how to set it up so that the script triggering is automatically done. Is it based on user data entry into a field?

                          There are a number of script triggers that can be attached to layout objects. Entering a field is one of the events that can be trapped.

                           

                          In your case, both an OnObjectEnter (user enters field) and OnObjectExit (user exits field) trigger could be used to trigger a script that behaves as outlined above. As long as the value is in range, this is transparent to the user.

                           

                          See here: Setting script triggers for objects

                          • 10. Re: Create alert if calculation field value is out of range
                            philmodjunk

                            erolst wrote

                            Using a script trigger doesn't require a major redesign, either...

                            I agree and apologize for creating that impression. I was trying to separate things into two different tasks: 1) providing the needed warning messages and 2) redesigning the system to better normalize the data as you recommended.

                             

                            Still, conditional formatting and hide object when don't necessarily require scripting so this can be a quicker way to get the desired warnings in place even if you do so as a temporary improvement while working out a more comprehensive, scripted approach.