"Jon Gutiérrez" wrote:

I guess I don't understand why you are using a script. Why not set up
the relationships and portals in such a way that you can create a new
record as desired directly in the portal? I don't understand what you
are trying to do clearly enough to give you step-by-step intructions,
but it should be easy to do it this way. Then you won't have any looping
scripts that get you into trouble, and the creation of new records will
be quick and easy.

To take a stab at your situation, with the information you gave, it
sounds like DB1 contains the info needed to create the invoice. It
sounds like DB3 contains the invoices. It's not clear to me what DB2 is for.

If all the info needed for invoicing is in DB1, and the invoices are in
DB3, a simple approach would seem to be to define a relationship between
DB1 as master and DB3 as related. You would need to have a unique
identifier field for each of the DB1 records, and an identifier field of
the same type in the DB3 records, with the relationship defined using
this key identifier field. This allows a one-to-many relationship, with
each record in DB1 having many related records in DB3. Define the
relationship to allow creation of related records. Set up a portal from
DB3 in a layout of DB1. Put appropriate fields from DB3 in the portal.
Then, when you click on the first empty row of the portal and enter
data, a new related record is automatiaclly created in DB3.

If DB2 contins information that you need for invoicing, or if the
situation is more complex, let us know and we will try to help.

Bill

Dear Bill,

Thank you for your reply.

DB2 keeps the related records for both DB1 and DB3. It is a
one-to-many relationship (one record in DB1, several related records
in DB2, and the same thing for DB3). Think about it this way: DB1 hold
the statement info (client name, order number, etc.). DB2 holds the
item requested (it contains the keys to DB1 and DB3 and info like item
price, item quantities, etc.). DB3 is the invoice. It contains more or
less the same info as the related record in DB1, but also contains
some info not present in DB1, and has its own counter.

Not really.

DB1 to DB2 = one to many
DB3 to DB2 = one to many
DB1 to DB3 = one to one

The script is run from a button in DB1. It creates a new record in
DB3, copies the key from related record in DB1, copies all the
necessary info from DB1 to DB3. THEN, and this is the problem, loops
from record 1 in DB2 to record n in DB2, assigning to them the key
from the newly created record in DB3. The issue here is that in a
network environment, it is likely that somebody could be modifying one
of the records in DB2 that the script has to lock to add the key from
the new record in DB3. In fact, it happens many times that somebody in
modifying one of the needed records in DB2 (for instance, somebody
could be updating the item price, adding the stock number, etc.). If
this happens, the loop does not know what to do and for some reason
ends up creating several hundred blank records in DB2, until the user
(or myself) stops the script.

I need a way to "tell" the scrpipt: "Hey, if you find a locked record,
send the user a message to get out from that record" or maybe "Hey,
before you run the script, check whether somebody is using any of the
related records in DB2, and if it is so, either send them a message or
void the whole script and tell the user to run the script later on.

First thing:

- I do not know what error number in FileMaker tells me that somebody
is using the record that the script is trying to modify

Second:

- Even if I knew this error, the only thing I could do is to stop the
script at that point (that would be a terrible solution!) or send a
message to the user (not a good solution either!). The best solution,
to my knowledge, would be to check availability of related records,
lock them before doing anything else, and then runnig the script. That
would take care of the locking problem and make sure that the script
is run from beggining to end without problems.

Any ideas anybody there?

Thanks

Hi Bridget,

Thank you very much for the info you provide in your reply. I have
checked the articles and they seems very interesting. I am going to
try and see if I can apply some of the ideas to solve my problem.

I am not really keen on stoping a loop until the record becomes
available, because sometimes this could be several minutes more than
the user is willing to wait. Besides, if this happens several times a
day, then it is likely to generate frustration in the user. I am more
into checking record availability and locking records prior to running
the script, and if they cannot be ALL locked at once, then avoid the
whole process and tell the user to run it later on. This might sound
drastical, but it might be better to keep the database integrity after
all.

Jon



On Thu, 31 Jul 2003 10:11:37 +1000, Bridget Eley
<bridgeteley (AT) ihug (DOT) com.au> wrote: