We want to create a lookup that has a criteria in it.
For example, when we go to the Architect --> Manage --> Lookups and we choose the table and the field to lookup "Search Field" we want to be able to set a criteria.
The lookup we want to create is based on a custom table myTable that has a one-to-many relationship with the ACCOUNT table (ACCOUNT --> myTable), the search field is ContractNumber but we only want to provide results for ACTIVE results, i.e. Status = ACTIVE. How can we do this without having to create another custom table myTable_ActiveStatus that would update every day so when the user uses the lookup it would only display ACTIVE results.
You could create a custom view in the database using the following steps from customerfx , then have your lookup refer to this view as the main table. If you have remote clients then you will need to consider how to push the view out to your remotes.
I can't attest to the impact it will have on your system without knowing how many records are in your table and how well defined your indexes on that table are.
It should not present an issue when you upgrade to 7.5
