Page 1 of 1
Filter/Restrict Lookup Results?
Posted: Sat Jul 17, 2021 10:23 pm
by pmjd
Hello,
Is there a way to restrict which records a Lookup field returns?
So if I have a table with a field that indicates if each record is either "Active" or "Obsolete".
How can I restrict a lookup field in a form to only show the records from the table which are marked as "Active" to be shown for selection?
Thanks,
Paul
Re: Filter/Restrict Lookup Results?
Posted: Sun Jul 18, 2021 6:43 am
by kev1n
Hi,
Use
nuSetProperty().
See an example
here
Re: Filter/Restrict Lookup Results?
Posted: Sun Jul 18, 2021 10:26 am
by pmjd
Thanks kev1n, tried that by putting
Code: Select all
nuSetProperty ('iri_status', 'Active');
in the js section of the lookup but it didn't seem to work as the lookup showed all the results. Not sure if it's the wrong place for it but wasn't sure where else it would go.
However, tried another approach of cloning the form that had the info I needed in it and adding
Code: Select all
SELECT * FROM ink_reference_information where iri_status = "Active"
to the SQL of the cloned form in the
Browse section of the form's properties. This seems to be working as it gave me th filtered results I was looking for.
Thanks for your help
Re: Filter/Restrict Lookup Results?
Posted: Sun Jul 18, 2021 10:32 am
by kev1n
Set the Hash Cookie in the onfocus event:
Code: Select all
nuSetProperty ('iri_status', 'Active');
Then in the Lookup form:
Code: Select all
SELECT * FROM ink_reference_information where iri_status = "#iri_status#"
Re: Filter/Restrict Lookup Results?
Posted: Mon Jul 19, 2021 12:08 am
by pmjd
Tried that but doesn't work for me either. Don't worry about it, happy enough with the other method I found fo using the cloned form (got the idea from one of your previous suggestions on cloning and editing the user management forms for other users) as it allows me to filter the info out that I don#t need.
What I did was:
Set the onfocus event in the lookup object Custom Code, then added the SELECT statement to the SQL in the Lookup tab of the lookup object. on using the lookup on the form it's part of it still displays all records. The form I'm using to look up the records is the same one used for data entry, so creating the clone of it and using a select statement to limit the info displayed seems to work fine.
Re: Filter/Restrict Lookup Results?
Posted: Mon Jul 19, 2021 1:00 pm
by kev1n
I created a small example "
Popup Filter"
Maybe you'll discover what I did differently from you.
Re: Filter/Restrict Lookup Results?
Posted: Tue Jul 20, 2021 1:39 am
by pmjd
Cool, now see what you meant.
You have: end user can select filter option, then search via lookup. I needed a non-end user selectable pre-filter (using the restricted form) so that no choice of what can be filtered is possible.
Sorry if I didn't explain the requirements properly but interseting to see that option available too