I need to create a script or calculation (not sure which) that will look at the number in one field, and then enter a value in a second field.

For example...If the number in field A is 8,7, 75,25, 40, 8, then in field B enter "Z". If the number in field A is 54, 56, 64, 34, then in field B enter "Y". I have 28 of these sets.

Right now, I can get it to work for one set, but then it keeps putting the result for the first set into all field B's, regardless of the numbers in field A. (Make sense?)

Thanks for your help!

Hi Dezza,

Does field A have a single integer at a time (i.e. 8) or the whole set (i.e. "8,7,75,25,40)? It doesn't seem like there is a mathematical pattern to your sets (if there is, let us know), which might make this easier , so...

If just integers in field A, then you're may be best off using the Choose() statement.

I would look something like:

Field B =

Choose(Field A, "Z", "Y", "X", "Z", "Z", "Y", "X", "X")

Where Z could be = {0, 3, 4}, Y = {1,5}, X = {2,6,7}

Check out the online help for the Choose() function for more info.

Good luck!

[ November 03, 2001: Message edited by: droid ]

The "Case" function seems to be what you need. It replaces nested "If-Then-Else" functions.

Hi Dezza,

If you're looking to map a bunch of integers to letters, I'd have to recommend against Case(). This is what Choose() is all about. For example, if you use Case(), it would like this:

code:

---

FieldB = Case( FieldA = 0, "Z", FieldA = 1, "Y", FieldA = 2, "X", Field A = 3, "Z", etc...)

---

Choose, as I posted before, is much simpler to write and read:

code:

---

FieldB = Choose(Field A, "Z", "Y", "X", "Z", etc...)

---

Over the course of 100 or so integers, you would essentially eliminate the need to write "FieldA = n" 100 times. If you're not mapping a sequential set of integers though, then Case() might start to make sense.

HTH!

Thanks for your feedback!

Dezza

You need two "B" fields.

The first is a simple text entry field, and it lies underneath the second (calc) field. Also, make sure the calc field doesn't allow user entry (go to "Field Format").

The calc field returns the value of the text entry field, unless it's empty, in which case it returns the value based on field "A".

Clear as mud? Good.

Dan

OK,

The advice above worked very well when I made the field a calculation field. However, there may be times when I need to enter a different value in Field B. I've tried doing the same above in a script, so I can leave Field B as a text field, but I haven't been succesful. Any suggestions. Thanks again for helping a novice.

Dezza

[ November 07, 2001: Message edited by: Dezza ]