10 Replies Latest reply on Aug 26, 2011 8:49 AM by LaRetta_1

    Calculation Flag

    MikeEdwards

      Title

      Calculation Flag

      Post

      Is there a calculation I could use to raise a red flag for all customers with a history of at least one rejection (Value in value list for the status field) The idea is that the calculation would be run once a customer code is entetred in a new record and the calc field would display a warning if that customer has a history.

      Is this the best way to approach this?

        • 1. Re: Calculation Flag
          philmodjunk

          This sounds possible, but I can't get very far with telling you how to set it up as you don't indicate how this "history" is recorded.

          Is this all in one table or do you have a related table for this "history"?

          Does the status field store multiple values (typical of a checkbox group) or just one value?

          Exactly what value or values marks it as a "rejection"?

          • 2. Re: Calculation Flag
            LaRetta_1

            "The idea is that the calculation would be run once a customer code is entetred"

            Status then should reside in your Customers table.  If you only want it to display if the Status = "Hold" for instance, just use conditional formatting - no need for a calculation field at all.  What do you want the warning to say?  I assume it isn't just to display the status or you would simply place the Status field on the layout.

            Anyway, try this for an example ...

            Create straight text on your layout (using the text tool).  Make it say whatever is the warning you wish to coney to your User when they are creating this record.  Let's use for example "Warning - customer issues! Contact Office Manager."  Make it red and large.

            Now attach conditional formatting to it FORMULA = Customers::Status ≠ "Hold"

            When this evaluates to true then the formatting you specify below will apply.  YOu want to make this text invisible when customer not on hold so go to Font (in your conditional calc) and set font size to 500.  Some color the text background to same as layout background to 'hide' text but, if using on some monitors or through Remote Access or Citrix, the text can show as a ghost (not exact matches) and it looks terrible. 

            • 3. Re: Calculation Flag
              MikeEdwards

              I could do this within the table or from a seperate table dealing strickly with rejected jobs.

              There are 10 different value headings in the status field. I wanted to flag a any new job in which the customer entered has a history of at least one job with the status value = "R - Rejected"

              • 4. Re: Calculation Flag
                MikeEdwards

                 The conditional formatting idea is good however the formula has to reach beyond the individual record to all records with that specific customer code and at least one instance of that "R - Rejected" status.

                • 5. Re: Calculation Flag
                  philmodjunk

                  Sounds like you have at least two tables here and this relationship:

                  Customer::CustomerID = Jobs::CustomerID

                  Make a new occurrence of Jobs (Select it in Manage | Database | relationships and click the button with two plus signs). Rename it JobsRejected and link it in like this:

                  Jobs::CustomerID = JobsRejected::CustomerID AND
                  Jobs::constRejected = JobsRejected::Status

                  Define constRejected as:

                  "R - Rejected"

                  set to return text. (Make this value exactly the same value as the rejection value in your value list.

                  Now you can use LaRetta's conditonal format technique with this expression:

                  Not JobsRejected::CustomerID  // I'm assuming CustomerID is a number field here

                  • 6. Re: Calculation Flag
                    MikeEdwards

                     Customer ID or Customer code as we call it is an 8 letter deliniator.

                    • 7. Re: Calculation Flag
                      philmodjunk

                      CustomerID is best implemented as an auto-entered serial number. You can keep your customer code in your system, it's just not the best method to use for linking your records in a relationship.

                      Not IsEmpty ( JobsRejected::CustomerCode )

                      will work for a text field.

                      • 8. Re: Calculation Flag
                        LaRetta_1

                        "The conditional formatting idea is good however the formula has to reach beyond the individual record to all records with that specific customer code and at least one instance of that "R - Rejected" status."

                        If Status refers to individual job status and not customers status then, using existing primary relationship between Customers and Jobs, add this calculation in the Customers table - no additional table occurrences necessary:

                        Let ( list  = List ( Films::Status ) ;
                        Case ( not  IsEmpty ( FilterValues ( list ; "R - Rejected" )) ; "Customer has rejected job" )
                        )

                        I am unsure of your FM version, if 11, you can still use this calc in conditional formatting (but reverse the boolean) and it would need to be triggered to refresh.

                        • 9. Re: Calculation Flag
                          MikeEdwards

                           I'm on FM 11.  The Let Case formula above is be be conditionally on a text field or through a calculation field?

                          • 10. Re: Calculation Flag
                            LaRetta_1

                            Calculation:

                            Let ( list  = List ( Films::Status ) ;
                            Case ( not  IsEmpty ( FilterValues ( list ; "R - Rejected" )) ; "Customer has rejected job" )
                            )

                            Conditional format:

                            Let ( list  = List ( Films::Status ) ;
                            not  IsEmpty ( FilterValues ( list ; "R - Rejected" ))
                            )