More efficient way to match all found records
Hey all, so for the purposes of understanding, I have two tables in my Filemaker database:
Our "Products" table has over 50,000 items, these are SKU's. Not all of this 50,000 SKU's are in stock, so we have another table for "Inventory". When an a product gets in stock, it is added to the inventory table, and daily, "0 inventory" items (aka, items that have sold out), are purged.
Having said that, my Inventory table usually floats around 3000 items.
So I'm working in the Products table and I'm working on a script that lets me find items that have been marked "High Priority", but I only want to find items that are in-stock.
So originally, I setup the script a little like this:
- Enter Find Mode
- Input text "High Priority" into "Products::Priority" field
- Input value ">0" into "Inventory::Stock" field
- Goto Layout - List view
So, essentially this script starts with all 50K products, then matches ones that are "high priority", which lets say is 10K of them, then it goes and finds ones that in are in stock (based upon the calculation of that stock value being greater than 0). But that calculation has to go through all 50K products. Then the two results are referenced, and I get my output.
As you may have noticed, this seems incredibly inefficient, especially since I know that there are only about 3K products actually in stock.
Any recommendations to limit to the query to only these items in stock (by using a value from the "Inventory" table) without having to do a calculation for all 50K?