What i have is one table with all products listed in it and another table where quote line items are stored. We have another piece of software that works out what products are required for a job and then generates a report saved as an excel file. I have a vbscript that runs on this file and optimizes it for easy import to filemaker.
Sounds like you have three pieces of software to perform this process (presuming that "another piece of software" is neither FileMaker nor Excel). Any reason you don't simple use FileMaker for this process? It is very capable.
This script can take ages to perform even when there are only 50 lines but sometimes we will have over 200.
What is "ages to perform" mean? .5 seconds? 5 minutes? 5 hours?
Currently i have a script that loops as below.
Step 1. grab product code from first imported product- set variable
Step 2. go to products layout and perform search
Step 3. get product category for that product - set variable
Step 4. return to original layout and set category field for that product.
Can you print a copy of the script and posted it?
my first thought is that when you perform the find, you might be in a layout showing summary field(s). I believe that FileMaker will summarize each time you enter the layout in browse or preview mode. That definitely takes time.
Next, finding records with no product category - make sure you use find =. That's simply "=" in the field as the search criteria (without quotes).
Finally, consider using a relationship to "Go to related records". You might need a global field that will be related to the product table.
Actually no, check that above, regarding using = as search criteria ... My experience is that the fastest way to search a text field that is empty in a large, large table, is to first find non-empty records, then find omitted. I use find >0 as the search criteria for non-empty text fields. It's much faster than find =, in my experience.
And one more thing: If you get some basic SQL down, your chore would be a breeze - one step when utilizing the ExecuteSQL function..
1 of 1 people found this helpful
Since you are using product code as your find criteria to determine the category, it should be possible to set up a relationship that uses product code to determine the category. No find or script needed at all in that case. The relationship will allow you to link to or look up (copy over) the category.
philmodjunk you are a legend!!!! the relationship was setup by someone else when the file was first created to match category AND code. all i had to do was delete the match category and the whole process takes about 1 second!!!! maybe a little bit longer - but you get the idea!!