bombdesign Posted October 31, 2011 Posted October 31, 2011 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 Posted October 31, 2011 Posted October 31, 2011 You should just use an auto-entered serial, not some scheme to set a serial.
Vaughan Posted October 31, 2011 Posted October 31, 2011 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 Posted November 1, 2011 Posted November 1, 2011 Another option is to use another table as a "numerator" - and create a related record there whenever you need a new serial number.
bcooney Posted November 1, 2011 Posted November 1, 2011 I respectively disagree on promoting these techniques. Come on, guys!
comment Posted November 1, 2011 Posted November 1, 2011 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 Posted November 1, 2011 Posted November 1, 2011 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 Posted November 1, 2011 Posted November 1, 2011 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 Posted November 1, 2011 Posted November 1, 2011 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 Posted November 1, 2011 Posted November 1, 2011 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 Posted November 4, 2011 Posted November 4, 2011 "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 Posted November 4, 2011 Posted November 4, 2011 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 Posted November 4, 2011 Posted November 4, 2011 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 Posted November 4, 2011 Posted November 4, 2011 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 Posted November 7, 2011 Author Posted November 7, 2011 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 Posted November 8, 2011 Posted November 8, 2011 All records accounted for does not preclude the use of an auto-entered serial, imho.
Recommended Posts
This topic is 4782 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now