A midsummer nights Filemaker challenge

[spongebob]

Hello World 😃 !

Its just great to be here!

Here’s a good one for all you Pros out there; I program Filemaker since 30 years and this one is such a gem, it gave me a few great sleepless nights.

Maybe you guys can come up with a better solution or idea.

We want to create records in a simple flat database for different kinds of items (whooohooo! Easy so far!).

Any item we put in is either in Group1, Group2 or Group3 and so on.

Lots of people will enter items, each Item has its own group.

Each Group should be numbered sequentially.

For example, when a person wants to create a record, first I ask him for which group.
The person chooses Group1, Group2 or Group3 and clicks GO.

Then the System goes away, and finds the next sequential Number for that Group and uses that.

The records should be numbered sequentially for each Group, so the database Records might, for example look like this:

Group1 No 1
Group1 No 2
Group1 No 3
Group1 No 4

Group2 No 1
Group2 No 2

Group3 No 1
Group3 No 2
Group3 No 3
Group3 No 4

So in this example, 

 if a person wants to add a record to Group1 the next number would be 5.
 if a person wants to add a record to Group2 the next number would be 3.
 if a person wants to add a record to Group3 the next number would be 6.

Okay so what we want to do is to track the next sequential number for each group.

Okay so I have a Table called “GroupsRunningNumbers” which in this case has three records, keeping track of the next number to be used in each group.

Problem:
But its not that simple…(it never is right? *sigh*) if two people now simultaneously click on create a new entry for “Group1” for example, often they may get the same number, because the scripts actions to add one to the relevant record in “GroupsRunningNumbers” are not indivisible,

So how would I do that?

Of course what I want is also to add more groups at will later. Later I want introduce new groups 4 5 & 5 in the table “GroupsRunningNumbers” for example.

How do I write the code to make the “allocating of the next number of the group” so, so that the same number cannot be given to two users who happen to click on the button to create at virtually the same time?

I was thinking using the setserial number idea, BUT that means for every group I create afresh I would need to create a new field in the Database. I don’t want to need to do that. I want the user to be able to add more groups (ie more records in the “GroupsRunningNumbers” Table) without having to edit the structure, and the system then managing the sequential allocations on its own.

So the Part 1 question is: how best to program this, so that if many people click the same add button for say Group1, they all get different next numbers?

Thats Part 1.

For EXTRA Brownie Points there’s more!

Part 2 :

Now if a person starts filling in say the record Group3 No 6 and he doesn’t finish because he didn’t fill in all mandatory fields, ie iF HE REVERTS the Record and it ceases to exist, then I want the number Group3 No 6 to become available again for future use.
That way gaps in the numberings will be avoided.

Any thoughts?

Anyone?

With love
Spongebob.
“And off I am to the crusty crab to grab a burger while I wait”

 

 

 

[comment]

As you have discovered, this is not trivial. Are you sure you really need this? From what I've seen, such requirement usually comes from a desire to emulate an existing paper-based system, with no actual purpose being served by such numbering. IOW, no one cares if the numbers are sequential or have gaps.

If you do really need this, consider a method where a user committing a new item runs a script that starts by locking the parent Group record. If successful, it increases the serial number stored in this record and passes it to the Item record being created. Then it commits both records. OTOH, if the parent Group record is locked by another user, have the script try again after a pause (perhaps several times) and if the record continues to be inaccessible ... well, you can abort the process of creation or ask the user to wait.

Keep in mind that records can always be numbered dynamically using a summary field - though such numbering is not permanent and depends on the current found set and sort order.  

 

Edited July 22, 2021 by comment

[Ocean West]

Perhaps this method may be of use:

https://scalefm.com/sequence-generator/

 

[spongebob]

you guys are great! It will take me a few days to try all this. And yes I really need this; in fact I need it in several databases, thanks

 

 

 

Edited July 22, 2021 by spongebob

[spongebob]

hey dudes,

just for info I tried Comments approach to try to lock the record and if that succeeds proceed, if not wait a random time.

Sofar this works like a charm. I did several Multi-User tests with multiple machines clicking on "create record" "at the same time" and it works really well.

In 200 Tests not a single failure.

I extended on this idea that I use a specially created parent table to the table I wanto change (the one with the group entries and the running number for each group). By trying to lock the parent record and the subrecord, we get the situation that all remains locked and I can make multiple changes to fields in the subrecord, before committing and freeing the whole thing up again. This way the record remains locked through all changes until I commit. (It makes the changes undivisible; the only way Filemaker supports that concept as far as I know.)

This method also allows me to track when a number should be reused. If a user REVERTS the record during entry, I have a revert script. That script checks if the number of records before the revert is the same as after the revert. If not, it stores the number for reuse in the group record that also keeps the latest running number. From there it can be reused.

Sofar this seems to work well.

Thank you Ocean West also for your suggestion.

Have a good time everyone! If something goes wrong I will keep you posted! lol ;)

Luv

Spongebob

Edited July 27, 2021 by spongebob

[comment]

The idea is to lock the parent record only for the duration of issuing a number. This would happen when user attempts to commit the record, not before. Otherwise a user taking their time over creating a record will prevent other users from doing their work on other records belonging to the same parent.

This is similar to issuing an auto-entered serial number, with the option to generate the number 'On commit' selected. This is what one would use when generating invoice numbers and other series that aren't allowed to have gaps.

In both scenarios, a number once generated should NEVER be reused. 

 

[spongebob]

"The idea is to lock the parent record only for the duration of issuing a number."

ja cool thats what were doing.

"In both scenarios, a number once generated should never be reused.  "

Not quite I think; in my scenario if a user does not finish his editing of the record (ie he abandons it by reverting the record so the record disappears); the number of the reverted record is stored for later re-use.

That actually works well; and it is a further advantage of the method with the auto-entered serial number.

ja?

I just noted I didnt actually say thank you to you Comment. Whoops. Thank you for the help, it was much appreciated.

best

[comment]

I am afraid I don't fully follow your description. If you want to store numbers of reverted records for later reuse, then you need a separate system to keep such numbers and a mechanism to override the default "numerator" routine described above in case a number is available for reuse. That's a lot of moving parts and I would not feel confident that there isn't a scenario in which this will fail. I would use either one method or the other, not both.