13 Replies Latest reply on Jul 9, 2015 11:38 AM by DylanMajka

    Difficulty with Portal Filtering



      Difficulty with Portal Filtering


      I'm currently working in a database that is used for taking orders.  The orders database is where all of the information about the particular orders are stored, but the information about the customers are stored in a separate database.  I would like to set up a system in the orders database where you can search for a customer through keywords when they place an order so that you can find their unique ID without having to open the customer database.

      My first attempt was to create a drop down menu whose values were populated with a list I created from the field values from the customer name field in the customer database.  From here, it would populate the customer ID field based on which customer name I selected.  This worked, although if you didn't start from the first word in the customer name it wouldn't pop up in the list.  I wanted something that functioned more similarly to the find feature where you could use minimal keywords.

      I then attempted to do create a portal that you could filter based on the customer's name.  I used a global field as a relationship between both databases so that all of the customer's names would populate the portal, and then you could proceed to filter them by name.  This worked by using PatternCount() > 0 in for the portal filter, but because there are 25,000 customer names the database had to sort every time which took a while to find the name I was looking for.  I then tried to filter by a relationship between the two solutions using the search criteria and customer name, which worked but again you would have to be precise in the search.  Anybody have a way to make the portal filter faster, or an entirely different solution that I may not have thought of yet?

      Thank you!

        • 1. Re: Difficulty with Portal Filtering

          Portal filters on a portal using a Cartesian Join (X operator) relationship will be very slow with large numbers of records as Filemaker has to evaluate the portal filter once for each related record. Anything that you can do to reduce the total number of related records could help speed up the filtering action. Is there any way to reduce the number of related records such as:

          Marking customer records that have not placed a new order in a specific time as "inactive" and then setting up a relationship that matches only to customer that are not "inactive".

          Grouping customer records into categories and specifying a category before searching by name.

          It might be simpler to enter text into a global field and using a script to perform a find. The script can return a list of ClientIDs to display as related records in the portal for further filtering or just so you can click one to select it in the case of multiple matches.

          • 2. Re: Difficulty with Portal Filtering

            Is there a way to mark a large amount of the customer records all at once?  I found that of the 25,000 only about 3,000 are actually still active.  Is there a way to set a field for only the 3,000 all at once?  Also would this be significant in increasing the speed?

            • 3. Re: Difficulty with Portal Filtering

              Cutting the number down from 25k to 3k sounds very promising but you'll have to test and see.

              Replace field contents can update the value of a field for every record in a found set.

              • 4. Re: Difficulty with Portal Filtering

                I was going to try and use replace to cut the records down to 3,000 but it won't let me replace an empty field.  The reason it's going to be empty on every record is that it's a brand new field that I added just for the purpose of narrowing the records down.  Is there a way to find/replace a field when the find criteria is nothing?

                • 5. Re: Difficulty with Portal Filtering

                  Do NOT use FIND/REPLACE. Use REPLACE FIELD CONTENTS. This option is found in the Records menu not the Edit menu.

                  The names are similar, but they function quite differently. There should be no issues with an empty field if you use the correct tool here.

                  • 6. Re: Difficulty with Portal Filtering

                    So I narrowed the fields down to 3,000 but it still takes much too long to be practical.  I think it may be time to scrap my original idea and try something completely different.  I know I previously explained what I am looking to do but I'll explain again in more detail just to be thorough.  With the current order system in place, when a customer calls to place an order the employee creates a new record in the order file, and then looks up the customer ID in the customer file using a find with the customer's name, and then types that ID into the ID field in the order file.  There is a relationship in place between these two files based on the customer ID, so the rest of the customer info fields in the order file are set to auto-fill when an ID is entered.  A similar process must be done for the product ID.  The customer says what product they want, the employee must find it by name in the products file and enter the ID into the order file for it to auto-fill.  My boss has asked me to create a layout that will function more in the way a website would for placing orders.  All on one page, she wants some kind of search or pull down menu for the customer name, and the same for product so that the only things that need to be filled out for the rest of the order are quantity and color selection.  She want something that is straightforward enough that a customer could use it.  With thousands of customers and products in each of the other two solutions, does this sound like something that's doable?

                    • 7. Re: Difficulty with Portal Filtering

                      A find can be scripted such that the employee enters data into a global text field before the script executes. If the script finds only one record, it can automatically add that record's ID to your invoice. If multiple matches are found, it can display a list where the user can click to select the specific customer needed for the new invoice. If desired, the script can but * wildcards both before and/or after the search text thus entered.

                      You might also consider using the customer's phone number in your search. With caller ID, you might already have the customer info up on your screen before you answer the call. (But my experience shows that you can easily get contacts with the same phone number so you still have to build in a system that can allow for multiple matches.)

                      • 8. Re: Difficulty with Portal Filtering

                        I'm assuming that I would have to write this script into the Orders solution right?  Even with this being the case, the script would be able to perform a find on the Customers solution without a problem? Also, what would the script step be for returning the ID from the Customers solution back to the Orders solution after the find is performed?  Thanks for all of your help!

                        • 9. Re: Difficulty with Portal Filtering

                          Hi PhilModJunk,

                          I was wondering if you had any further information on the script steps yet.  I have searched through the forum and the web for more information on this but I've had some difficulty because I'm not sure exactly what I should be searching for.  When you get around to it if you could point me in the right direction that would be awesome!

                          • 10. Re: Difficulty with Portal Filtering

                            Having two database files instead of one that combines the tables for both customers and orders complicates the process, but it can be done even with the data in two files.

                            The simplest is to add the needed table occurrences(s) from your customers file to your relationship graph in Orders. Then you can set up layouts and perform finds on this data in Orders just as though the tables were part of your Orders file.

                            How to add a TO for a table from another file:

                            Open Manage | Database | Relationships
                            Click the far bottom left button to add a new Table Occurrence to your graph
                            In the dialog that opens, use the drop down to "add FileMaker Data Source".
                            Use the resulting dialogs to first find your customers file, then select a table defined inside it as the table to use as the data source for your new Table Occurrence.

                            You now have a Table Occurrences that references data from another file and you can link it in to your database solution and set up layouts and/or portals based on it just like you can with any other table occurrence in your relationship graph.

                            If you need help scripting the find, the examples found here might help: Scripted Find Examples

                            • 11. Re: Difficulty with Portal Filtering

                              The relationship between the customers and orders file already exists, because this is how the database knows where to get the rest of the customer information from when a customer ID is entered upon taking an order.  The way in which the two databases are connected are the unique customer ID field.  The way things are done right now is that each record in the orders database is an individual order, and each record in the customers file is an individual customer, and when an order is taken in the orders database, the employee who took the order must lookup in the customers database the customers ID manually, so that they can enter it into the order so that the order has a customer connected to it.  I was asked to find a way to eliminate that step to make order taking easier and quicker.  My boss would like the procedure to be: 1.  New Record  2.  Without leaving that New Record, we want to be able to type in some kind of search field part of the customers name or keywords that will produce the customer ID on the record so that the employee doesn't have to go searching for it.  I think this may be where a global field needs to come into play, but I'm a little confused as to how the orders file will tell the customers file to do a find using that criteria, and return to the orders file the correct customer ID.  Sorry for any confusion or redundancy, I know I explained what I'm looking to do already but I figured it might help if I could be even more concise.

                              • 12. Re: Difficulty with Portal Filtering

                                Then all you need do is perform the search on a layout based on that customers table inside your orders file. You can use a global field as your search field such that you can enter data into it and then click a button to perform the script.

                                I often set up a popover that opens to show several global container fields (such as one for a name and one for a phone number along with a button for performing the find. I sometimes install a custom menu so that trying to enter find mode keeps the layout in Browse mode and opens this popover instead.

                                Please see the link that I have previously provided for examples of scripts that use data entered into global fields while in Browse mode to perform a find.

                                • 13. Re: Difficulty with Portal Filtering

                                  Ah perfect!!!  I didn't know that you could make a layout in a file based on a table from another file.  I think I know exactly where to go from here.  Thank you ver much!