Would it be better if the option for "repack" did not appear in the value list for service when NJ is selected in Location?
That can be done with a conditional value list.
If you want the message to pop up, add a table, ServicesByLocation, with two fields to your database:
Enter every location and every service available for each such location. Format the service field as a checkbock field using your services value list.
Create one record for each location and check the boxes for each service option available for that location.
If you then create this relationship:
YourTable::Location = ServicesByLocation::Location AND
YourTable::Service = ServicesByLocation::Services
Then you can use an OnObjectSave script trigger to perform this script:
If [IsEmpty ( ServicesByLocation::Location ) ]
Show Custom Dialog [YourTable::Service & " is not available in this location" ]
You can also use: Not IsEmpty ( ServicesByLocation::Location ) as a validation calculation on the services field and then you can enter your message as a custom validation message.
But keep in mind that you can set up the services value list so that services that are not available for a given location aren't even listed in the value list.
thank you PhilMOD,you made a good point, by using the field value list, this option won't show up, but my only concern is this is just a policy warning which is a very tiny part of the work procedure, but i need to create another table for this relationship. Is there any way i can done in current table and doesnt need to create another one?
I don't know how your current tables are designed so I don't know if you can use an existing table or not. As long as you have a table somewhere in your system that lists locations and services, the above method can be used. You don't actually need one record per location with all services listed in the services field. It can also be a table where you have one record for each service and the locations can be a list in the location field, or you can have one record for each avialable of service and location pair. The relationship will work for each of these three possible configurations.
Trying to do this without any table could be done with scripting, but the result will "hardwire" your database to a specific set of services for each location. Future changes to the services available for each location would require rewriting your script.
I'd think you'd want a table to list locations and services anyway as a way to manage what services are available for a given location and such a table makes updating what's available a simple data entry task.