Gap in serial number

[NateJames]

http://docs.360works.com/index.php/MirrorSync_advanced_topics#Configuring_MirrorSync_creates_gaps_in_serial_numbers

Hello Mirrorsync

So I understand now why mirror sync creates these gaps in the serial numbers, ( I originally asked for the clarification on why this does this). However what happens to the internal database if I go back to these serial numbers and adjust them to be the next sequential number. Or better yet, why doesn't the sync tool on setup capture what the next sequential number should be, do its testing and than write back to the database the captured value so our files do not end up with these sequential gaps?

I need you to consider what happen if a company gets a visit from the taxman during an audit and they question comes up why the gaps in invoice numbers. Where invoices deleted, is the company hiding something etc,etc. The other fact is let's face it we are not pro mirrorsync users when we set this tool up, we make mistakes, we don't understand the relationships correctly, we add something to tables we want to include. All these things we do as we are learning greatly accelerate these serial numbers within our file every time we run the sync configuration. I personally have an estimate table that only has 700 records, but serial number wise because of mirror sync is showing my next serial number as est1200.  

In my learning of the tool I recently realized that when giving my remote users a full copy of the database, even though I was not syncing my invoice table with them, the remote user did In fact have all the invoices from date of them downloading there working copy. I have now learned to make the script run on startup, checking if the device has an active connection or not, and remove all invoices from there device, but, from now on anytime I run the configuration I am also going to elevate those invoices numbers. And yes I realize that I can distribute a different file, and not include those tables in the iPad version, but now I have to maintain 2 different files if we go that route.

 

As I said I am very new to the FileMaker world, and maybe there is something I don't understand still about why this happen, and maybe it's critical to the internal database integrity. Can I change the numbers back? 

OR, if I can ask for anything this upcoming Christmas from 360works.....

 

Thanks again

Nate

[webko]

Don't use your PrimaryKey as the user visible serial number. PrimaryKeys do not have to be sequential (or even numbers, as per using a UUID instead) - but an Invoice Number is just a user visible identifier. That you can set to be truly sequential with scripting and some care...

Personally, for my smaller systems, I make the PrimaryKey increment by a prime number because I can, and all it has to be is unique.

[NateJames]

Thank you for the reply,

And in the future when I build something I will definitely go that route you suggested, its great advice

Unfortunately for this solution that we are using that would mean going through all the relationships and changing out the primary keys, including all the scripts etc etc to change the dependancy on that user number. (we have a heavily modified version of FMSP). 

We have used the write back solution on the estimate module and it works incredibly well for our offline users (for the very reason you suggested it being a user visible serial number), keeping everyone on the same page when discussing the estimates in progress and keeping track of the offline versus online file. 

What are your thought on actually keeping data off the offline file.

• If you give the users a full copy than they get everything at once (we do this as mention and than do a first sync to remove the invoice we don't want on there device, causing the elevated invoice numbers). (full offline file is around 400mb )
• if we give them an empty file it takes 10 minutes to sync all the products and estimates and staff etc that we do want them to have (empty offline file is 40mb) But the user never gets an invoice record, there is no sync_invoice layouts, and the invoice ID will than never elevate
• Have you had experience with pre-populating the data, (personally I am a little uncomfortable to always have to make that additional file all the time any major changes happen, and than having to remember to delete all the records and such. Seems like a pain

I guess what I am getting at is that I understand why mirror sync creates these gaps, what I don't understand is why mirror sync doesn't take corrective action to fix what there tool does to the file. Do you know what happens if change the numbers back myself ? Does that affect the Apache tomcat database keeping track of the sync, because the last thing I want to do I cause sync issues to a live file that we are using to run our business on.

Thanks webko for your insight

Nate

 

[Jesse Barnum]

Hi Nate!

First of all, thanks for using MirrorSync - I'm very happy to hear that it's working well for you.

On your first point about can MirrorSync set the serial number back to what it was before it created the test record - no, unfortunately, that is neither possible nor safe. The only way to change the serial number ordering is by using the 'Set Next Serial Number' script step, and when we're doing the setup using JDBC (such as when using FileMaker Cloud), we can't run scripts. It's also not safe, because let's say that we create a test record with primary key 100, meaning that our next serial is 101. Let's say that our setup finishes in 5 seconds. Even if we could set the next serial back to 100, there's no guarantee that somebody else didn't create a new record on the server within that 5 second period, which means there will be a duplicate primary key for the next person after that.

Most of our customers are not that bothered by gaps in serial numbers. Since it's a very important thing for you, I would strongly recommend that you follow Tim Webko's advice to separate your invoice number from the primary key. What I would do after separating those values is create a separate table (call it 'invoice number'), with a single auto-enter serial number. Whenever the user creates a new invoice, go to this table, create a new record, and grab that serial number, then switch back to the invoice layout, create a new record, and set that serial number as the invoice number (which is not the same as the primary key). This approach guarantees that primary keys will always be unique, sequential, and in an unbroken sequence.

Regarding having to delete all the invoice records, have you considered using the MirrorSync feature to download an empty clone instead of the full file? This will probably make your initial sync take longer, because it will need to download all of the records on the initial sync, but it's a 'cleaner' way to get started since you're not having to script a deletion process at startup.

[NateJames]

I have considered it, and tested that option. It takes 10 minutes do to the products and image files associated with the database. 

 

But because MirrorSync is so awesome, i have been testing the setup with a pre - populated and different mobile file.

By different file I mean a clone of the original, and than i deleted all the tables i do not want the user to have (i.e. invoices). I left everything else within the database, and than setup a synch relationship using the separate mobile file option (Please see the other topic, and problems I had setting that up).

But i can report that it does work nicely and I do not get any gaps in the serial numbers on the office version anymore quite simply because we dont sync it. The other tables can elevate the serial numbers as it is not critical for an accounting auditing purpose. In the future I will definitely follow Tim and your advice and solution

 

[Jesse Barnum]

Great! Sounds like a good solution to me.