Evaluate a formula contained in a string

[ColinGregory]

Hi everybody,

I've constructed a string from various fields to build an equation (ie 5.0 <= 7.0, or 99.0 <= 100.0 <= 105.0).

Is there a way to evaluate the string to find whether it is true or false?

Thanks in advance for any help.

 

[Naith]

The latter half of that formula doesn't really make any sense:

99.0 <= 100.0 <= 105.0

But if you placed a section of the equation in a formula with nothing in it other than:

5.0 <= 7.0

when displayed on the report, the field would be boolean - returning either True or False - in this case; True. If you lump all parts of the equation together (it isn't clear if all 3 parts are part of the same equation, or 3 seperate examples of the equation) then only the last section would be reported as True or False.

Naith

 

[synapsevampire]

I don't think that you can drop the entire contents of the string and have it evaluated, if it's truly a string, it will just display the string.

You may be thinking of some programming languages which allow you to pass dynamic code from a string to be avaluated, sometimes termed a macro substitution. I don't think that CR supports this, perhaps in the basic syntax it does, I haven't seen it though.

You should use the elements that you're concatenating to build this string as separate parts of a formula:

{MyTable.MyField1} <= {MyTable.MyField2}

the second formula you're trying to create should be something like:

({MyTable.MyField1} <= {MyTable.MyField2}
AND
MyTable.MyField2} <= {MyTable.MyField3})

You can reference formulas from within other formulas, so it's easily as powerful.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[ColinGregory]

Thanks Naith and Synapsevampire,

A few more details..

To construct my equation I follow the following steps.

I start off with a field containing variations of either &quot;Result <= MAX&quot; or &quot;MIN <= Result <= MAX&quot;.
I REPLACE MAX and MIN with the actual value from the MAX and MIN fields converted to text (eg 99.0 <= Result <= 105.0)
I then REPLACE RESULT with the value from the result field converted to text (eg 99.0 <= 100.0 <= 105.0)

Unfortunately I end up with a string which I can't do anything with. I want to include on the report details for all instances which fail the equation.

Any ideas??

Thanks for taking time out to reply.

 

[synapsevampire]

Naith alluded to your formula construct of X<=Y<=Z, which won't work in Crystal.

I think that this formula:

({MyTable.Min} <= {MyTable.max}
AND
MyTable.Result} <= {MyTable.Max})

Is the same as your string formula.

The point is, you're doing it the hard way, just use a formula to evaluate the values and return true/false.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

I agree with SV. Just find the values of min and max and the sbuject the result to a boolean test like this:

if result >= min and <= max then
<do something>

this test obviously can be made better and more complex but you get the idea Jim Broadbent

 

[ColinGregory]

Sorry everybody - I think I may not be explaining my problem properly.

The equation I'm starting off with is specified by the application (LabWare LIMS) so I'm stuck with it as a starting point. The equation is used to test whether a result is within limits. What I'm trying to do is substitute the actual values into the equation and determine whether the result is in spec or not, but as the starting equation is a string I have to convert my actual values to string too.

 

[synapsevampire]

Sorry, this makes no sense to me.

If you need a formula to derive something, just build it, I don't understand why you have to use this other construct.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[JoeCrystal]

If I understand what you the following formula maybe a start:

Dim LMS As String
Dim LMSValues(10) As Number
Dim i As Number
Dim j as Number

LMS = &quot;5.0<=7.0&quot;

While LMS <> &quot;&quot;

i = Instr(LMS,&quot;<=&quot
If i <> 0 Then
j = j + 1
LMSValues(j) = ToNumber(Mid(LMS,1,i-1))
LMS = Mid(LMS,i+2)
ElseIf LMS <> &quot;&quot; Then
j = j + 1
LMSValues(j) = ToNumber(LMS)
LMS = &quot;&quot;
End If

Wend

Formula = LMSValues(1) <= LMSValues(2)

This formula seperates the values from the string and converts them to numbers than makes the comparison. Is this what you are trying to do?

 

[ColinGregory]

You're right in guessing what I am trying to do. The trouble is the string can be any one of the following:
MIN <= Result < MAX
MIN < Result < MAX
Not Equal to MIN
Equal to
Result is Like
Equal to MIN
Result <= MAX
Result >= MIN
MIN <= Result <= MAX

I've decided to do a SELECT CASE type statement and use variations of your code for each variation. Not very elegant unfortunately.

Thanks very much for the reply(s)