Script to find records in another database

[Quartekoen]

Once again, this is probably one of those things that is easy to do, but I just can't figure it out.

I have two databases. One named Order and one named Bought. The order database stores records for each item being ordered, and keeps track of what order they go to by a specific ID. The Bought database keeps records of each item that has BEEN ordered and sold. Basically, an invoice is filled out with items, and as it's filled out, the items are stored on the Order Database. Once the invoice is printed, they are removed from Orders and stored on Bought.

A customer may end up purchasing a particular item more than once per month (sales are broken up by month in the Bought database) so the script that moves records from Order to Bought needs to check if any sales already exist for that item in that month.

Here is where my problem comes in. I can't seem to figure out how to search my Bought Database from a script I made in Orders. I have a step to run an "Enter find mode" script found on Bought, but I can't get the information over to search. Set Field[] won't set the fields in Bought. Copy and Paste won't set them, and I can't figure out how to do it, much less perform the find once it works. This is all being controlled from a script in Orders, and the databases are related by item number. Any help would be greatly appreciated. Thanks.

[Fitch]

I'm guessing that you're trying to set some fields via a relationship and the problem is that the relationship becomes invalid in Find mode. To work around this, you could set the search criteria into global fields, which are accessible in Find mode. The global fields could be in either file, doesn't really matter. Then you can enter Find mode and set your search fields to the contents of the globals.

Note that global variables won't work for this scenario because they are file-specific. However, we have a number of techniques for passing multiple variables via script parameter. I'd recommend you look into that just for learning.

Not sure what the problem is with performing the find. You're already on the right track: Enter find mode, Set Field(s), and then... Perform Find.

[Quartekoen]

Global fields may be a solution to this, but since the script is designed to loop through all items on the invoice, they would have to be separate fields, and if I can avoid creating fields solely for the purpose of searching, I'd like to. Here's a snippet of my code. Maybe it will give some insight into my problem.

Open File ["Bought"]

Perform Script ["Enter Find Mode" from file: "Bought"]

Set Field[bought::Item; 1005]

Perform Find []

As you can see, I've even tried setting the field to a definite number, instead of a variable or a field reference, and still, it refuses to put the number in that field. Still not sure why.

Plus, the fact that I have to have a script in Bought just to enter find mode tells me that I won't be able to perform the find from this script either, and that I would have to have another script in Bought just to perform it as well.

UPDATE: I did some more experimenting, and you said that perhaps my relationships were becoming invalid once I entered find mode, so instead, I tried staying in browse mode, creating a new record and putting the item number in there instead. That doesn't work either, nor does it work if I tell it to Set Field[1005], without specifying which field exactly (and to just use the currently selected field). What's going on here?

Edited June 25, 2008 by Guest

[Quartekoen]

Anyone else have any suggestions of how to set fields in one database with values from another?

[Fitch]

Here's how to do this, you'll have this script in Order:

/* Pass the search term as a script parameter */

 Perform Script( "Find my Item" from file: "Bought"; Parameter: ItemID )

Note that the [color:blue]Open File ["Bought"] line is not needed.



Now here's the "Find my Item" script in Bought: 

Enter Find mode

 Set Field( Bought::Item; Get(ScriptParameter) )

 Perform Find

You may want to do other stuff like go to layout, error trapping etc. but that is the essential part.

[skingjack2]

Hello,

I'm following this thread as I believe it may assist in solving a problem I'm encountering. However, I am having issues getting the set field line to reflect the example provided by Fitch. I've created the script up to the "set field" point but I cannot get the "get(scriptparameter)" to show; it's not an option when I specify the set field as a calculated result under the function menu. Is there something I may be overlooking or performing incorrectly?

TIA for your help.

Jack

[bcooney]

Just type Get (Scriptparameter) in the specify calculation dialog.

I would also put a Go to Layout before the Find so that you are definitely on the correct TO.

[skingjack2]

Thanks Bcooney! Solution was much too simple :P