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.
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?
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.
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?
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.
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?
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.)
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!
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!
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
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.
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.
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!