Delete record in related file

[wowmom]

I have a database with a field that contains licenses numbers. There are a limited amount licenses that we have purchased. I have created two related files: a "used license" file and an "available license" file that contains the availabel licenses. I have written a script that displays only the available licenses in a pop up menu when entering license numbers into the records. The script then creates a record in the used license file containing the selected license and the current user name. The problem is in deleting the selected license number from the available license file. I've tried three different external sub scripts and each time, the wrong license record is deleted. The funny thing is, is that the sub script works if I perform in on the used license file (without running it from the main file). I have created the appropriate global field on the available license file that is given the value of the license that is selected in the main file.

I've created a looping script that compares the stored global license value with the (now used) available license field, and then deletes the matching record.

I've created a find script that sets the find criteria to the global license value, and then deletes the matching record.

I've even tried to create a new record with selected license number; then perform a search for duplicates; and then delete all.

------------------

[Vaughan]

I'll agree with Chuck here... the data structure currently being used is not the most efficient.

Whenever I have solution that requires fancy and tricky scripts to perform tasks I begin to wonder whether my underlying information design could be improved -- it usually results in a simplification.

[wowmom]

I'm really new at this. You both have me heading in a much more efficient direction.

Cheers

------------------

Go Al go!

[Chuck]

Why not create a relationship between the used and available files based on the license number and use the Delete Related Record script step?

I'm not sure why the other solutions you tried didn't work. It could be the the field wasn't on the layout you were trying to search from. Without seeing the scripts, I wouldn't be able to troubleshoot them for you.

I would solve it in a manner like this:

Create a relationship from used to available licenses.

In the available licenses file, isolate the record that you want to assign to someone (while it is the current record, Show All, Omit, Show Omitted).

In the used licenses file, import the single found record in available licenses, then delete the related record in available licenses.

Also keep in mind that there are other ways to get this kind of job done. If you're using FileMaker 5, you could keep both the available and used licenses in a single file, each record of which has a flag field set to 1 if available or 0 if used, and build a relationship from the file where licenses are chosen to the licenses file based on this flag and have a value list that shows only records that are related using that relationship. But perhaps this breaks something else you are trying to do.

Chuck