If I understand you correctly you are using a drop down list to show 5000 products. Slowing down is expected behavior. That is an unusable number of items in a list. User's cannot find what they want and it is too slow for any meaningful work to be done.
I suggest using a cascading selection of values where each one limits the choices of the next. The selection process for buying copier paper might look like this:
1. Product category: Paper
Limits products to only paper
2. Product type: Letterhead
Limits to 8 1/2 by 11 inch paper
3. Product option: 12 lb.
Limits to paper weight
Now the list of allowed products has been narrowed down to a few.
You can see other examples of this on the internet, for example when buying clothes. The selection might be Shirts - Mens - size. Of course you can expand the selection process to include many more sub-groupings.
1 of 1 people found this helpful
using 'virtual value lists' / 'magic value lists' offers the freedom to select easily by sql. Google for 'magic value list filemaker' shows some example pages
3 of 3 people found this helpful
I looked at your supplied file and I see that you are using a portal along with scripts that are pulling the full list of IDs. While you may want to consider some of the options mentioned above (which are directed more to value lists), there are other simple changes you could make to facilitate things. First, you could change the relationship between Table and FoundSet to a cartesian join, which would automatically show all products. Then you can disable the Collect Found Set script on the Record Load script trigger - this is what is slowing things down as every time you make a selection the script is going through and re-creating the list of all products.
Hope that helps,
I removed OnRecordLoad script trigger only and now its work fine. Thank you Philip.
I'm glad that was helpful. That will work for now, however it is only a short-term solution. The list of IDs is stored in a global field so it won't be accessible to other users (global fields are unique for each user in a hosted solution) and won't be updated if additional products are added (unless you are running the Collect Found set script every time a new product is added). In general, I believe you will find that a cartesian join (this is where you choose an "x" as the operator in Edit Relationship) is more flexible, but perhaps there are specific reasons why you don't want to do this.
Hello Philip, I have never thought about that issue. Since i have already implemented it to my solution and on all modules such and contact, stock etc.. i have no idea on how to add the cartesian join in my solution.
I there any major modification to be done? thanks for you help in advanced.
A cartesian join (also called a cross join) is one where each record from one table occurrence is matched up with every record from another table occurrence. You set up a cartesian join by using the "x" operator in the Edit Relationship dialog box. So in your database edit the relationship between Table and FoundSet and change the operator from "=" to "x". Now each record in Table can automatically access all the records in FoundSet.
I just need to change from "=" to "x" in the Edit Relationship?
Yes, changing from "=" to "x" in Edit Relationship will change the relationship to a cartesian join relationship.
Wow Philip, you are the best. Thanks a lot for your help.
Glad to be of help.