Serials in multi user environment

[bombdesign]

Hi all

FMP Server 11 and FMP 11 clients.

I've got a table where I need to set two different serials, one when the record is created and the other at a later time through a script. I am using a a cartesian relationship to a one record settings table where I store and increment the serial number. The problem is when two users are pulling the serial at the same time, they get the same number. Right now the script is getting the serial number to use first and then incrementing it by one.

Is there a way to lock the field/record in the settings table so it is guaranteed that the serial will be unique or should I try a different approach?

Any help appreciated

Thomas

[bcooney]

You should just use an auto-entered serial, not some scheme to set a serial.

[Vaughan]

Ditto for Barbara's comment.

Is there a way to lock the field/record in the settings table

FMP should manage this itself. Most probably your process is incorrect. The settings table should store the last (current) number not the next. The process is then:

Go to settings table

Open the record <----------

increment the serial number

remember the new number

close the record.

The trick is the open record step: if another user is incrementing the serial then this step will fail. So trap for errors opening the record and wait until the record is unlocked.

In pseudo code

Loop

Open record

Exit loop if no error

End Loop

increment the serial number

remember the new number

close the record

It would be prudent to put a counter or timer in the loop to break out of infinite loops if something goes wrong.

For the nitpickers: yes the setting table could also store the next value.

[comment]

Another option is to use another table as a "numerator" - and create a related record there whenever you need a new serial number.

[bcooney]

I respectively disagree on promoting these techniques. Come on, guys!

[comment]

I respectively disagree on promoting these techniques. Come on, guys!

Can you be more specific? What exactly is your objection to these techniques, and what alternative solution can you suggest when it's necessary to assign consecutive serial numbers to some of the existing records - for example, quotes that turn into invoices?

[Echo33029]

To complement Comment's suggestion about using a dummy table, a simple and quick way to do this would be to have a dummy table set up with two fields, one an auto enter serial field, and one other field (type un-important). Create a relationship between the field that you want to set with your script (target field) and the ID field of the dummy table (set the relationshp to allow creation of records in the dummy table). In your script, set the target field to empty and do a Set Field (dummytable::un-important field; ""). Filemaker will create a new record in the dummy table, incrementing the table's ID field and assign that value to the target field.

Only two script steps required and this should avoid multiuser issues, but check for errors after the Set Field to ensure that the script step executed properly.

[comment]

Sorry, but that's NOT quite how it should work. Using the example of Quotes, with Invoices being the numerator table, you would indeed have 2 fields in Invoices:

InvoiceID - Auto-entered serial number

QuoteID - Number

The relationship needs to be:

Quotes::QuoteID = Invoices::QuoteID

allowing creation of records in Invoices, and the script does:

Set Field [ Invoices::QuoteID ; Quotes::QuoteID ]

This will create a new related record in Invoices if one doesn't exist (and do nothing if one does).

[Echo33029]

Actually the original post did not indicate that a new related record was required, only that a unique sequential number was needed. My method simply gave a way to do that with minimal fuss.

[comment]

Actually the original post did not indicate that a new related record was required,

Your method creates a new related record too. My method does it the conventional way, where the newly created record inherits the creating record's matchfield value - not the other way round, which relies on an undocumented quirk. I see no difference in the amount of fuss required to implement either one.

[bcooney]

"Can you be more specific? What exactly is your objection to these techniques, and what alternative solution can you suggest when it's necessary to assign consecutive serial numbers to some of the existing records - for example, quotes that turn into invoices? "

Not a requirement in the OP.

Assign consecutive serial numbers to some of the existing records? Retroactively?

If a consecutive serial number is required, then voided records are my approach.

[comment]

Not a requirement in the OP.

Assign consecutive serial numbers to some of the existing records? Retroactively?

Well, apparently that's the way both Vaughan and I read this:

I need to set two different serials, one when the record is created and the other at a later time

If a consecutive serial number is required, then voided records are my approach.

I am not sure what "voided records" are.

[bcooney]

I really don't know what that means "I need to set two different serials, ..." I don't interpret that as a req for consecutive.

Voided records. I meant that if the requirement is that an Invoice Number must not be missing in a list, let's say, then they can't be deleted and must be marked as voided.

[comment]

Well, to me "serial" implies "consecutive" - but it's really for the OP to clarify. However, your real objection was to the techniques suggested, not to the assumption they were needed in this case - and you haven't answered either one of my questions.

[bombdesign]

Hey guys

Thanks for all your replies and sorry for this late feedback. Life just got in the way. Anyways...

I tend to lean towards Vaughan's suggestion as it would require very little modification of my current scripts and setup. What I have is an order/invoice table where the serials are order id and invoice id. Customers can try out a product for a period of time (order id set) and once they decide to buy or return, an invoice is always created (invoice id set). The serial should be consecutive to keep the accounting department happy, sorry if that was unclear.

I'll try it out and report back.

Thomas

[bcooney]

All records accounted for does not preclude the use of an auto-entered serial, imho.