3 Replies Latest reply on Mar 19, 2012 12:10 PM by philmodjunk

    Case Calculation with multiple fields.

    BERGSTEN

      Title

      Case Calculation with multiple fields.

      Post

      I currently have the following calculation that tells me from an Invoice layer whether or not a record appears in a linking table (our schedule). This field is called "OnShowdayField" (Showday being our schedule). Workorders is the linking table between the Invoices and the Showday Schedule.

       

      Case ( WorkOrders::OnShowday; "On Showday"; WorkOrders::OnShowday="0"; "NOT ON SHOWDAY"; Invoice_Status="CANCELLED"; "CANCELLED"; "NOT ON SHOWDAY"; Rental_Type="SALES"; " ")

       

      The problem I'm having with this calculation is that when something is marked as either Invoices::Rental_Type="SALES" " or Invoice_Status="CANCELLED," if I try to switch it back from Sales to Rental or from Cancelled to Final Invoice/Quote/Workorder,etc , it won't mark "OnShowdayField" as "On Showday" or "NOT ON SHOWDAY" 

       

      Any ideas? I was thinking it had to do with the fact that I'm referencing multiple fields in the Case calculation, but I'm not sure what the best way of fixing it is.

        • 1. Re: Case Calculation with multiple fields.
          philmodjunk

          Did you copy and paste this expression or might you have accidentally made a mistake posting this expression?

          Reformatting it to make it easier to read:

          Case ( WorkOrders::OnShowday; "On Showday";
                      WorkOrders::OnShowday="0"; "NOT ON SHOWDAY";
                      Invoice_Status="CANCELLED"; "CANCELLED";
                      "NOT ON SHOWDAY"; Rental_Type="SALES";
                      " ")

          The text shown in red appears to be incorrect. The logical expression should come before the result returned if it is true and you seem to have them reversed.

          What kind of field is OnShowDay? If it's a calculation field, what is its return type?

          How does changing the invoice status field affect the value of WorkOrders::OnShowday?

          How does Rental_Type change the value of WorkOrders::OnShowday?

          • 2. Re: Case Calculation with multiple fields.
            BERGSTEN

            Yeah, I copied and pasted it, sorry.

             

            OnShowdayField is a Calculation field with Text return type.

             

            Changing the invoice_status or Rental_Type doesn't have any effect on Workorders::OnShowday.

             

            WorkOrders::OnShowday is a Calculation field returning a number with the following : not IsEmpty (WorkOrders::Invoice_ID)

            • 3. Re: Case Calculation with multiple fields.
              philmodjunk

              Copy and paste is what you should have done. Your expression appears to have an error in it as I pointed out in my last post.

              A case function evaluates from top to bottom and returns the value of the expression associated with the first expression to return true.

              If: WorkOrders::OnShowday is True, then that value is returned and the results of any other logical expressions that come after it are ignored. Thus, if changes to invoice_status or to Rental_type doesn't change how this expression is evaluated, then those values will not affect the result returned by this case funcion

              I'm also not sure that WorkOrders::OnShowday="0" will evaluate correctly. I'd use:

              WorkOrders::OnShowday=False

              or

              Not WorkOrders::OnShowday

              Please note that OnShowDay has only two possible values: True or False, thus, everything after the second logical expression will be ignored as one fo the first two expressions will always evaluate as true.