Comparing Unlike Fields

[product26]

I have a situation where I need to do a Comparison on two fields that are not alike. One is a number field, and one has mixed text and numbers.

when i use:
{orders.orderid} <> {orders.external}

 

[MarsianStar]

I think you have to get a common denominator for these to work. Which one is the number and which is the alphanumeric?

 

[product26]

sorry, for some reason it cut off my post...
I keep getting errors about tgml code... i don't understand. The forum is probably not compatible with Safari 3.

orders.orderid contains all numbers
orders.external contains the same numbers as orders.orderid only with "TN" as the first two characters in every entry.

How would I make this comparison?

 

[lbass]

What is the comparison you are trying to do? If one field is a number and the other contains letters and numbers, are you trying to see whether the number field is found in the other field? Please show examples of each, including as much variation as you can see in the text/numeric field.

-LB

 

[product26]

if {orders.orderid} = {orders.external} this is considered New
if {orders.orderid} <> {orders.external} this is a rerun.

i need to show only new in one summary and only reruns in another.

so for a rerun i need to show every result where orders.orderid (12345) is greater or less than orders.external (tn12345)

for a new booking i need to show every result where orders.orderid (12345) is equal to orders.external (tn12345)

 

[lbass]

You didn't show a selection of samples, but if the extrenal field always starts with two letters, you can create a formula {@external}:

val(mid({orders.external},3))

Then compare this formula with Orders.orderID.

-LB

 

[product26]

I think you have helped me get on the right track.

Thank you much!