How do I halt a script if a field value already exists?

[jaybutler1234]

I'm working on a database that was created in an old version of FM. There are several files, one per table. It has since been upgraded to FM7.

There is a file for Jobs and one for Invoices.

I've created a script to create invoices using a number from Jobs.

The script copies a number from the Jobs file. It then opens the invoice file and launches a script. The invoice file's script creates a new record and pastes the number in the proper field. 

The problem is that if the number already exists, I end up with a blank record and "INVOICE NUMBER” is defined to contain unique values only. You must enter a unique value.

How can I tell the script to end if the number already exists?

Thanks!

[eos]

You're probably trying to set the wrong field.

 

I'm assuming that your business rules allow multiple invoices for a single job, and that jobs and invoices are related via a foreign key field for jobs in Invoices.

 

That field would be the one to set via script (to make the invoice related to the job); it's probably named along the lines of jobID, FK_jobID, jobID_FK etc.

 

The Invoices table/file should have its own primary key field, set to auto-enter a serial value and being validated as unique – and from the message text it seems you're erroneously trying to set that field (when actually you shouldn't touch it at all).

 

On a side not, if you need to transfer data, don't use copy/paste – pass the jobID as a script parameter to the other script, where you can use it with e.g.

Set Field [ Invoices::jobID ; Get ( ScriptParameter ) ]

That has the benefit – among others – that the contents of your clipboard will remain intact.

[comment]

The simplest way to solve not only your current problem but also many others is to define a relationship between the two tables. It would be even simpler if you consolidated the two tables into a single file, but it's not mandatory. 

 

Once you define a relationship in the Jobs file between the (local) Jobs table and the Invoices table, you can create a related invoice directly from the Jobs table by setting the matchfield in the Invoices table (provided the relationship is set to allow the creation of new records on the Invoices side). If you also add a layout (or layouts) of Invoices to the Jobs file, you can do everything from a single file, and abandon this awkward configuration.