Multiple Searches and Export

[Rui]

Hey Guys,

 

This is my first time on this site. I am 'ok' at scripting (ok is probably an exaggeration!!), and I am struggling on a particular issue. After scanning this site all morning, I am still at a loss (though I picked up some great tips for other pieces of my solution for later work).

 

This is my problem.

 

I am working on a Filemaker 8 Pro DB. We have around 30K records. We get orders for our data, and often we will get an excel spreadsheet of around 10K orders+. We have to search from the list of orders in our DB to see if we have the data.

 

Sounds pretty easy, right?

 

Well... I have to search through data 3x, as there is no unique key, and we have to come up with 'best matches'. No unique key because some records are partial, but the customers requesting the data want the information behind it. I import that file into a table in the DB, importing only the data required for the searches, and into fields named the same as the table that contains all our data.

 

So here goes:

 

My script (that doesn't work) goes as follows:

 

Allow user abort[on] (will remove it once it works)

Set Error Capture [on]

Go to layout ["Import_Customer_Requet"(Import_Customer_Request)]

Go to Record/Request/Page [First]

Loop

Go to Layout ["Data_Hold" (Company_DB)]

Enter Find Mode []

Set Field [import_Customer_Request::API]

Perform Find []

Export Records [no dialog; "Temporary_Export.Fp7";]

Go to Layout ["Search_Results"(Search_Results)]

Import Records [No dialog; "Temporary_Export.Fp7";]

Go to Record/Request/Page [Next; Exit after last]

End Loop

 

I deleted most of the script steps involved already. For instance, I have a variable to look up for error 401, and to search by an additional search terms.

 

Questions:

 

1) BASIC question.. I used to always use 'copy and paste'. Set fields is the way to go. Am I using it correctly here? It seems to be going down the records, and I assume it is correct.. but would love a yes or no

 

2) After the script is running, it exports the first record it finds. Then, when I go to import it (add new records) it tells me the "temporary_Export..." is severly damaged or was not created by filemaker. Why?

 

3) Any advice on how to do this would be greatly appreciated. Am I on the right track? Should I use the export/import steps? I tried using the "copy all records" script step.. but since there is no 'paste all records' found that script step to be a dead end, especially when it copies it all into one field, making the data useless.

 

The 'logic' behind what must be done in English is this:

 

1.) We get an order for data that we own.

2.) We have to go in and compare the data request vs. what we have in our DB.

3.) No unique key, so we have to search by their unique key (of which we have about 20% in our database). If we don't find it by the unique key, we search by county/state/township. If not, then we search by another 3 fields using constrain data. Up to 3 searches per every customer order - often totalling above 10K orders.

4.) I decided to import the Excel order, and I want it to search the DB and once a record matches up, copy it to another table, and then repeat the process until all the customer orders are searched and all relevant data we have can be found on one table, and then exported back into excel and sent off to the customer.

 

Make sense? I am putting in 14 hour days for the last 2 weeks, and I am burned out.. and though I am getting WAAAAY better at Filemaker and scripting.. my solution seems to be shrinking and getting worse Opps!

 

Any help would be GREATLY APPRECIATED.. I really need the sleep

[AHunter3]

Let's look at your loop, and comment it up a bit, OK?

 

Loop

Go to Layout ["Data_Hold" (Company_DB)]

Enter Find Mode []

Set Field [import_Customer_Request::API]

Comment ["What was that Find request? The field itself is Import_Customer_Request::API but what did you set it to?"]

Perform Find []

Comment ["Where are you now? You are on record ONE of a found set consisting of all records matching your Find request. Whatever that was. Note that you are IN A LOOP. Next time you are right here you will AGAIN be on record ONE of this same found set. See why that's a problem?"]

 

 

Continuing right along...

 

Export Records [no dialog; "Temporary_Export.Fp7";]

Go to Layout ["Search_Results"(Search_Results)]

Import Records [No dialog; "Temporary_Export.Fp7";]

Comment ["Where are you now? You are about to go to the next record. Where you are is the FIRST record in a found set consisting of the records you just imported, on the Search_Results screen. Note again that you are IN A LOOP. Just before the next time you are here, you will again have JUST IMPORTED so you will be on the FIRST record, once again, of a found set consisting of your newly-imported records. Newly RE-imported records, I should say."]

Go to Record/Request/Page [Next; Exit after last]

End Loop

[Rui]

Thanks for your repy and critique. I think I got a bit too excited in removing pieces of the script to see why it was crashing, and I definitely missed some logic altogether!! At any rate, I revised the script. I wasn't sure how to use Set Field properly, so I substituted copy/paste in the script, and it is finding search #1, but is running VERY slow and has crashed every time, though it is finding more records each time I run the script (though I do delete the search results before re-running the script). Any ideas on how I can optimize this a little bit? I still have to include the following that I removed to test:

 

set variable [$foundset0; Value: Get(LastError)]

and then include an IF statement to use it when it doesn't find a record so it can search that particular record it is on from the customer import table to the available data table. Something like:

 

If [$foundset0=401]

 

I plan to have it in the loop so as it is search each record, and comes up with a record not found, it will continue to search for that record based on other fields as not every field matches up (or even contains data). A little tough to explain, I know.

 

Here is my revised script that is taking FOREVER to run and keeps crashing (and I think it is due to the copy/paste - unless I am missing something).

 

Allow User Abort [On]

Set Error Capture [On]

Go to Layout ["Import_Customer_Request" (Import_Customer_Request)]

Show All Records

Go to Record/Request/Page [First]

Loop

Go to Layout ["Import_Customer_Request" (Import_Customer_Request)]

Copy [select; Import_Customer_Request::API]

Go to Layout ["Data_Hold" (Company_DB)]

Enter Find Mode []

Paste [select; Company_DB::API]

Perform Find []

Export Records [No dialog; "temporary_export.fp7"; Unicode (UTF-16)]

Go To Layout ["Search_Results" (Search Results)]

Import Records [No dialog; "temporary_export.fp7"; Add; UTF-16)]

Go to Layout ["Import_Customer_Request" (Import_Customer_Request)]

Go to Record/Request/Page [Next; Exit after last]

End Loop

 

It seems to find the records, but takes FOREVER to run, and every time it crashes shutting down the Filemaker app. Surprisingly, it doesn't give me a message asking me to recover the DB (yet).

 

How do I use Set fields to substitute copy/paste? I tried it, and it ended up importing ALL the records from the DB. The most records it found was 10, and that was roughly half way searching the DB at a rate of 1 hour and 15 minutes. Considering this is just the 1st of a multi-search script, I am a little worried.

 

Any suggestions? Thanks in advance. I am playing with the set-fields now that I know the rest of it is 'somewhat' working properly. Just not sure if set fields is appropriate to use in this case when I am looking for one record at a time like this..

 

Rui

[AHunter3]

a) how to use Set Field, for anyone accustomed to using Copy / Paste

 

Copy/Paste is how we moved data around by script in the heyday of FileMaker 2.x; anyone using FileMaker 3.0 or later should, as a general rule, not be using Copy or Paste in their scripts. (there are occasional reasons for still doing so but they're rare).

 

Think of Set Field as the replacement for Paste. It does not replace Copy.

 

That means you need to have stored the value that in the old days you would have Copied. At the point you would previously have Copied, you either ARE or ARE NOT on the same record of the same table as where you want to paste. For example, if you were on Joe Schmoe's record in Clients, and you were previously doing this:

 

Copy [Clients::Field A]

Paste [Clients::Field B]

 

... you would now simply do this:

 

Set Field [Clients::Field A, Clients::Field B]

 

If, on the other hand, you were switching context — moving to a different record and/or a different table altogether — you might have done something like this using Copy & Paste:

 

Copy [Clients::Field A]

Go to Record [Next]

Paste [Clients::Field B]

 

... to do that using Set Field, you would need a temporary "clipboard" of some sort to hold onto the value of Field A, because once you leave that context you can't just set Field B to it as in the first example above. You could do it this way, using a variable:

 

Set Variable [$Field A, Clients::Field A]

Go to Record [Next]

Set Field [Clients::Field B, $Field A]

 

... or you could do it this way using a global field:

 

Set Field [Clients::g.GlobalTextField, Clients::Field A]

Go to Record [Next]

Set Field [Clients::Field B, Clients::g.GlobalTextField]

 

But hey, (you may be thinking), what's the advantage of that if it takes as many script steps as Copy and Paste would have?

 

Try it with four fields instead of just one:

 

Copy [Clients::Field A]

Go to Record [Next]

Paste [Clients::Field B]

Go to Record [Previous]

Copy [Clients::Field C]

Go to Record [Next]

Paste [Clients::Field D]

Go to Record [Previous]

Copy [Clients::Field E]

Go to Record [Next]

Paste [Clients::Field F]

Go to Record [Previous]

Copy [Clients::Field G]

Go to Record [Next]

Paste [Clients::Field H]

 

versus:

 

Set Variable [$FieldA, Clients::Field A]

Set Variable [$FieldC, Clients::Field C]

Set Variable [$FieldE, Clients::Field E]

Set Variable [$FieldG, Clients::Field G]

Go to Record [Next]

Set Field [Clients::Field B, $FieldA]

Set Field [Clients::Field D, $FieldC]

Set Field [Clients::Field F, $FieldE]

Set Field[Clients::Field H, $FieldG]

 

Quite aside from which, Copy and Paste are limited to moving data as it is; with Set Field and Set Variable, you can run calculations on your data as well as (or in the process of) moving it around:

 

Set Variable [$RemainingDays, Invoice::Due Date - Get(CurrentDate)]

Go to Record [Next]

Set Field [Clients::Field B, $RemainingDays]

 

you simply can't do that with Copy and Paste.

 

Finally, suppose you have 115 fields in the Clients table, but only about 40 of them are on the Client Info layout. You can't copy from or paste into any except those that are actually ON the layout that you are on at the time. But you can use Set Field to set the value of a field whether it's on your current layout or not, as long as you have appropriate access to the TABLE it is in. So if you are on Data Entry Layout and the field ReferredBy is not on that screen, you can still do Set Field [Clients::ReferredBy, Get(AccountName)], no problem. And, similarly, you can set a variable or a global field to Clients::Serial Number even if serial number is not on the screen anywhere.

[AHunter3]

Now, I have a question: why are you methodically doing a Find for data that's in your file in ONE table only to export it and then turn around and import it into ANOTHER table?

 

Why are you copying data from one table to another?

[Rui]

Well, I am doing it this way as it is the only way I could think of to gather all the data needed, and then export it.

 

The request was this:

 

1) Our DB holds > 20K records of data that we sell.

2) Some customers will send a list of 10K + worth of data requests at any time. From these requests, we will have a certain percentage of their request, and need to find them fast.

3) There is no universal 'key' to matching the records. There is actually, but we don't have it, and we would need to buy it, which we won't do (it is VERY expensive, just to get that one little piece of data). So, we have to best-guess the matches. Right now, we have a person that is going through every single customer request and doing multiple searches. I am trying to free up her time and do what she does in a script.

 

I know it doesn't make much sense to copy data from one table to another, but once it is done, we export it, send it to the customer as "We have this data" and then we work out the price for it. Once we export it, we delete the found records that we did for the client (as we don't need them anymore).

 

That is the general logic behind it. It was the only way I thought of doing it to get all the fields for a particular record out so that the customer could verify that indeed the datasets match what info he wants to buy from us. Make sense? I am sure there was probably a better way to do it, but for now, that is the only way I could think to come up with a solution.

 

Thanks for your posting above! I am going to try and implement it now before I build any more of the script (the multiple field searches are next using constrain - and I have two sets of 3 fields to implement in the script - which makes your solution above that much more appealing!!).

 

Thanks so much for your help!!

 

Rui