Lookup function issue

[Rick Whitelaw]

Hi,

I've set up a calculation field to use for display on a contract form. It's working fine except for one detail. Lookup(musicians::street_address)&" "&Lookup(musicians::postal code_zip) is returning the correct data, but the postal code is returning A1a 1a1. I can't get it to return as upper case. I've tried Lookup(Upper(musicians::postal code_zip)) to no avail. In the table containing the fields being looked up, the postal code is formatted as upper case. I've even tried to change the postal code field (text) to auto enter Upper(self) which doesn't work, and probably shouldn't . . . I was surprised FM accepted the calculation at all.

Ideas?

Rick.

Happy holidays.

[Wim Decorte]

Not sure why the casting it explicitly to UPPER wouldn't work, but LOOKUP is not the function to use here.  LOOKUP is pretty old and there are better ways to achieve the same thing that will give you more control.  LIke  a scripted SET FIELD.  By using a calculated field you risk destroying any historical data when you change the calc or change any piece of data that the calc depends on.

[comment]

I've tried Lookup(Upper(musicians::postal code_zip)) to no avail.

 

Why don't you try the more logical =

 

Upper ( Lookup ( musicians::postal code_zip ) )

 

In fact, you don't need the Lookup() function at all; you could simply make the field auto-enter the calculated value =

 

musicians::street_address &" "& Upper ( musicians::postal code_zip )

 

This would also eliminate the danger Wim spoke of. *

 

 

 

 

I've even tried to change the postal code field (text) to auto enter Upper(self) which doesn't work,

 

Well, it does work. The real question here is when does it work. If you want to modify existing data in bulk, you should use Replace Field Contents[]. Auto-entered corrections only work during data entry, i.e. when a referenced field - Self, in this case - is modified.

 

 

 

---

(*) This is assuming you want to preserve the historical data, i.e. that your calculation is stored.

[Rick Whitelaw]

Of course. Auto enter calculation. Thanks. I thought using the Lookup function allowed the result to be stored.

[comment]

I thought using the Lookup function allowed the result to be stored.

 

It does, but that's only half the story. Consider, for example, what will happen if you need to move your data to a new file. You cannot import into a calculation field, so all your "lookups" will be either re-evaluated using the most up-to-date data or left empty. Either way, you will lose the original historical values.

[Rick Whitelaw]

Another lesson learned. I've converted most of my Lookup Calcs to auto-enter by calculation and indexed. Don't remember why I started using the Lookup function. Thanks.