WHY does your boss insist on a manually entered number? That seems like a recipe for really frustrating a user as they try possibly many times to enter a unique number and fail repeatedly. Could you use a portal to list existing values in sorted order so that the user can enter a value not on the list?
Am I correct that you have this relationship between table 1 and 2?
Table1::ID_LookUp = Table2::ID#
If so, then this expression:
IsEmpty ( Table2::ID# )
will be true if the number in ID_LookUp does not match any value in any record of Table2::ID#.
For some reason the people working in the other department argued that their people have been doing it for years already and don't want to change anything and my boss bought it. My boss gave me a layout design that the other department wanted to have and there's no space for portal. The relationship between the two table is Table1::ID_Num = Table2::ID#, I'm just using the ID_LookUp field in the interface. The idea is to check first the value entered by the user in the ID_LookUp Field in the GUI exist already in Table2::ID# and if not it will bring the user to the Layout where they can continue entering data and Table1::ID_Num will automatically populated by the value entered by the user (by Set Field(Table2::ID#; $ID_Temp).
Make a new table occurrence of table 2. Link it to Table 1. so that you have:
Table 1::ID_lookup = Table 2|LookUP::ID#
Then you can use the method I have recommended by using:
IsEmpty ( Table2|LookUP::ID# )
to check for an existing value.
You might want to demo an alternative to this if you think office politics will give you a reasonable chance to do so. Sometimes SEEING the change in action will change minds otherwise locked on "we've always done it this way".
Thank you Phil, I'll sure do what you told me about the alternative so that other people would really see it clearly how things work. Again, thank you so much!