11 Replies Latest reply on Aug 7, 2015 5:59 AM by philmodjunk

    "Detecting" text

    tays01s

      Title

      "Detecting" text

      Post

      I'm using some 'if' and/or 'case' functions in calc fields where if the value is a number/calc it gets evaluated, if there is a missing value (".") I can detect it easily and provide a standard answer. However, where the text is a variety of text, I want to detect it as text (but not ".") and provide an answer.

      How do you detect text (or a blank, ie. "") value? I've tried giving a range of numeric values to capture text by deduction, but it doesn't work and I'm not sure how FM 'values' text.

        • 1. Re: "Detecting" text
          philmodjunk

          I'm not entirely sure that I fully understand your question.

          IsEmpty ( FIeldReferenceHere )

          will be true if there is no data in the field.

          Length ( Field ) = Length ( Filter ( Field ; 9.012345678 ) )

           will be true if the only characters in field are the digits 0 through 9 and the decimal point. You can use quotes and include the minus sign if negative numbers are possible.

          And in other circumstances both Patterncount and Position are useful parsing tools that check a field for the presence of psecific text. Position can search for a particular instance of text and search from either beginning to end or from end to beginning of the text as well as returning the character position of the text if the search text is found.

          • 2. Re: "Detecting" text
            tays01s

            I'd like to differentiate text from numeric.

            • 3. Re: "Detecting" text
              philmodjunk

              Length ( Field ) = Length ( Filter ( Field ; 9.012345678 ) )

              will be true if the only characters in field are the digits 0 through 9 and the decimal point. You can use quotes and include the minus sign if negative numbers are possible.

              Would appear to do exactly that.

              Or is there something more specific with this possibly numeric text that you need to do?

              • 5. Re: "Detecting" text
                tays01s

                Hi both,

                Apologies, intermittent internet access at the moment.

                Thank you the syntax you provided worked in deducing numeric from text; I’ll look into how this works.

                A residual problem is that though I can now deal with record values that are empty, “.”, text or a number, if it is a text equation that requires an evaluate function, it won’t show the numeric answer if I set the calculation field to ‘text’; equally text values remain blank if I set the field to ‘number’.

                Is there a way to get either or will I have to go through an intermediate calculation?

                • 6. Re: "Detecting" text
                  philmodjunk

                  Please provide an example of what isn't working for you.

                  • 7. Re: "Detecting" text
                    tays01s

                    Here's the calc:

                    Case(
                    Reqs::Carb=".";"No Std";
                    Length (Reqs::Carb) = Length (Filter (Reqs::Carb; 9.012345678));Round(Evaluate(Reqs::Carb);0);
                    Reqs::Carb
                    )

                    and the field is currently set to text so everything works fine if Reqs::Carb is "." or numeric but not if it's say: IO 2::kg*2. If I set the field to number, the latter works but not the former.

                    • 8. Re: "Detecting" text
                      philmodjunk

                      If Carb holds the text kg*2 it "falls through" the case function and is returned as kg*2. That's what this case function is designed to do.

                      You don't have a test here that identifies the text as an expression to be evaluated.

                      Perhaps you could use:

                      Case(
                      Reqs::Carb=".";"No Std";
                      Round(Evaluate(Reqs::Carb);0)
                      )

                      If the only data in Carb is a number, Then Evaluate ( Carb ) will return that number so you can use evaluate to get a numerical constant or evaluate an expression.

                      • 9. Re: "Detecting" text
                        philmodjunk

                        You might try this approach:

                        Let ( v = Evaluate ( Reqs::Carb ) ;
                                Case ( Reqs::Carb = "." ; "No Std" ;
                                             v = ? ; Reqs::Carb ;
                                             v
                                            ) // Case
                                ) // Let

                        • 10. Re: "Detecting" text
                          tays01s

                          Both appear to work (if I replace ? with "?" in the latter) for ".", number or text calculation. I'll do a bit more testing.

                          Is there any inherent advantage to either?

                          • 11. Re: "Detecting" text
                            philmodjunk

                            Yep, leaving out the quotes around the ? was an error on my part.

                            To me, the second expression is "cleaner" and easy to read, but if both produce the desired result, then there is no major advantage of one over the other that I can see unless perhaps you perform this calculation in a batch update over huge numbers of records. Then the first might be slightly faster as it doesn't call evaluate for every record.

                            There is small a difference in what is returned when the Carb field contains text that does not evaluate correctly. The first will return a ? result to show an evaluation error. The second will return the actual text in the Carb field so that you can try to spot what error exists in the expression.