Circular lookup ?

[richardb]

I have 2 fields in my database. One is a number and the other a description. A list of the numbers and descritions exist in another realted file. I want the user to choose the value they want for either field as a value list. My problem is that I need the flexibility that if a user wants to enter the number, the description will automatically be looked-up and entered OR if they want to look at a list of descriptions they can click on that field and when they select the one they want, the number will be looked-up and entered into the number field. I can get it going one way easily using look-ups, but can't get it to work the other way around at the same time. Any help much appreciated.

[zincb]

The conditional lookup feature you desire should be added to the "feature request" section of FM Forums. There are workarounds with scripts and global fields on an inital data entry screen, but here is a simple way to give the *appearance* of what you describe:

Create two new fields, named Desc_Lookup and Num_Lookup.

Desc_Lookup should point to YourOtherFile: Description, via a relationship between YourOtherFile:Number and ThisFile: Number.

Num_Lookup should point to YourOtherFile: Number, via a relationship between YourOtherFile: Description and ThisFile: Description.

Arrange these two new fields so they are "behind" the original Number & Description fields and give them an opaque white fill color. Give the original fields a transparent fill color. Stack the original Number field on top of the Num_Lookup field & do the same for the description fields.

To the original Number field, apply a dropdown menu that displays the Number Value List (which should use External File: YourOtherFile: NumberList). To the original Description field, apply a dropdown menu that displays the Description Value List (which should use External file: YourOtherFile: DescriptionList).

Now, when a user enters a value in Number or Description, the Description or Number (respectively) will appear to be filled, but it is actually the Lookup field that gets filled. To copy lookup data into the "correct" (original) field can be done with an exit script:

Show All

Loop

If (isempty (Description) and not isempty (Description Lookup) )

Set Description = Description Lookup

EndIf

If (isempty (Number) and not isempty (Number Lookup) )

Set Number = NumberLookup

EndIf

Go to Next Record, exit after last

End Loop

Alternatively;

Enter Find Mode

Set (Description Lookup = "*")

Perform Find

Loop

Set Description = Description Lookup

Go to Next Record, exit after last

End Loop

Enter Find Mode

Set ( Number Lookup = ">-1")

Perform Find

Loop

Set Number = Number Lookup

Go to Next Record, exit after last

End Loop

Finally:

Set the validation of your Number field to:

Validate by calculation: isempty ( Description ), strict, with error message, "Please clear the Description field before filling the Number field. Press the Revert Field button to exit this message."

Set the validation of your Description field to:

Validate by calculation: isempty ( Number ), strict, with error message, "Please clear the Description field before filling the Description field. Press the Revert Field button to exit this message."

The validation checks won't permit the user to enter data into both fields, which would result in words on top of words, and numbers on top of numbers, on the layout.