8 Replies Latest reply on Sep 18, 2015 1:45 PM by dtcgnet

    Conditional formatting: intermittent failure

    tays01s

      I've set a field (Rx) to a blue fill if Calcs::Rx < Calcs::Req. For most of the records, it does as it should, but misses a few. I have trield reversing the < to > and the reverse happens.

       

      Are there any good checks to determine the cause of this kind of failure?

        • 1. Re: Conditional formatting: intermittent failure
          tays01s

          Answering my own Q: It was because I was using text fields, so whenever the 'number' was larger but the string contained lower denomination integers, it would be evaluated as a smaller number.

           

          There remains a problem because some records need to appear as text. Is there a way to have a text field but evaluate 'numeric' values as numbers?

          • 2. Re: Conditional formatting: intermittent failure
            electon

            GetAsNumber ( text )

            • 3. Re: Conditional formatting: intermittent failure
              tays01s

              That works, also I found you could use Evaluate(text expression).

               

              Is one likely better than the other??

              • 4. Re: Conditional formatting: intermittent failure
                dtcgnet

                It depends on what data you might get in that field.

                 

                Things to keep in mind:

                GetAsNumber ( text ) will return "25" in each of the following instances:

                25

                number25

                2X5

                25isthisnumber

                 

                These records which need to appear as text, can you give an example of what the values might be?

                 

                Also, in your conditional formatting, do you want the blue fill ONLY when both Rx and Req are numbers and RX<Req?

                • 5. Re: Conditional formatting: intermittent failure
                  electon

                  Evaluate will work just as well.

                  It is a powerful function with many twists and turns.

                   

                  It should produce the desired output in most mathematical and logical operations.

                  Also it seems it has quite some smarts.

                   

                  Let's take two parameters both stored as text

                  a = "2"

                  b = "4 + 4 - 2"

                   

                  Evaluate ( a + b ) = 444        evaluates b as number 442 + 2 = 444

                  Evaluate ( a & b ) = 26          evaluates b as its own calculation 2 & 6 = 26


                  Probably more elegant to use Evaluate instead of 2x GetAsNumber in this case.

                  • 6. Re: Conditional formatting: intermittent failure
                    dtcgnet

                    Let's say you have:

                    Rx = "25"

                    Req = "2 but NO MORE THAN 6"

                     

                    GetAsNumber (Rx) would give you 25.

                    GetAsNumber (Req) would give you 26.

                    25 would be less than 26, so the cell would be turned blue using your conditional formatting formula. That seems dangerous.


                    It would probably be wise to compare only valid Rx numbers to valid Req numbers, and if that is what you want, then you could use something like this:


                    GetAsText (GetAsNumber (Rx)) = Rx      and      GetAsText (GetAsNumber (Req)) = Req      and      Rx < Req

                     

                    In the above example, the pieces would evaluate to:

                    GetAsText (GetAsNumber (Rx)) would be "25"

                    Rx would be "25"

                    Rx would be recognized as a valid number.

                     

                    GetAsText (GetAsNumber (Req)) would be "26"

                    Req would be "2 but NO MORE THAN 6"

                    Req would be recognized as NOT being a valid number.

                     

                    So as a result, the condition would recognize that one of the values wasn't a number and the condition would evaluate as false, the cell would not turn blue, and you wouldn't be comparing apples to oranges. This would also correctly handle cases where Rx = 25 and someone entered Req = 025, for example.

                     

                    From what I can see from your initial follow up to your post, you want to only compare numeric values to numeric values. By comparing in the bolded way above, I think your conditional formatting would be more "trustable".

                    • 7. Re: Conditional formatting: intermittent failure
                      tays01s

                      I agree, I'd need to guard against 'GetAsNumber' sifting numbers from what should remain a text string. Although I don't have text records containing numbers, it's possible, so not worth the risk.

                       

                      However, as mentioned above, would Evaluate(text expression) not do the job without tripping?

                      • 8. Re: Conditional formatting: intermittent failure
                        dtcgnet

                        Would your conditional expression then become:

                         

                        Evaluate (Calcs::Rx ) < Evaluate (Calcs::Req)

                         

                        You'd still get some things turning blue that shouldn't turn blue, and you might still be comparing a number to some text. If Rx contained 50 and Req contained "Hello", you'd be comparing 50 to Hello. But you know your data better than I do. Maybe that wouldn't be a problem, maybe it would.