Desktop application
I have a database that has a field that identifies a unique inventory location (rack, section, slot), and a field that identifies the status of the slot location (unoccupied, occupied, pending, archived).
There are hundreds of locations to track, and we want to save historical data. For any location in the single table we do not want the status (occupied, unoccupied, pending) to exist at the same time, or at least we want an error message before record entry. Using keyunique have been able to prevent the same value from duplicating. For example, an alert that prevents another record from being entered with status occupied when there is already a record that shows the slot occupied. But we also don't want to enter a record with status occupied while status is showing unoccupied or pending. The correct procedure would be to change or add data values to the existing unoccupied or pending record, not add a new one.
We need to prevent a set of specific different values from existing for a given location number simultaneously. is there a script or formula that can be used in data validation similar to with keyunique but where x,y, and z cannot exist at the same time. This would be easy if there were a table for each separate location. Alas, there is not. We do not want hundreds of tables in the database.
The logic would go something like this: For x, do not allow x if "x, y, or z" exist in the field. For y, do not allow y if "x, y, or z" exist. For Z, do not allow z if "x, y, or z" exist.
There may be a better way to do this. But it actually works well using filters and sorts by location except for preventing these few data entry errors that might happen.
Open to any ideas.
Thanks
I have a database that has a field that identifies a unique inventory location (rack, section, slot), and a field that identifies the status of the slot location (unoccupied, occupied, pending, archived).
There are hundreds of locations to track, and we want to save historical data. For any location in the single table we do not want the status (occupied, unoccupied, pending) to exist at the same time, or at least we want an error message before record entry. Using keyunique have been able to prevent the same value from duplicating. For example, an alert that prevents another record from being entered with status occupied when there is already a record that shows the slot occupied. But we also don't want to enter a record with status occupied while status is showing unoccupied or pending. The correct procedure would be to change or add data values to the existing unoccupied or pending record, not add a new one.
We need to prevent a set of specific different values from existing for a given location number simultaneously. is there a script or formula that can be used in data validation similar to with keyunique but where x,y, and z cannot exist at the same time. This would be easy if there were a table for each separate location. Alas, there is not. We do not want hundreds of tables in the database.
The logic would go something like this: For x, do not allow x if "x, y, or z" exist in the field. For y, do not allow y if "x, y, or z" exist. For Z, do not allow z if "x, y, or z" exist.
There may be a better way to do this. But it actually works well using filters and sorts by location except for preventing these few data entry errors that might happen.
Open to any ideas.
Thanks
Comment