Create 6 tables, one for each branch, with only 2 fields, one calc = Branch name and one invoiceID, and go create a new record in the correct table, where an autoenter on invoiceID will take care of your incremental.
New branch ? Add a 7th table.
-- OR --
have a unique table where you create invoice numbers and a script that takes as parameter the branch name, and does
• SetVariable[$Inum; ExecuteSQL("Select MAX(InvoiceID) from InvoiceNumbers WHERE BranchName = ?";"";""; Get(ScriptParameter)) + 1]
• New Record/Request
• Set Field [BranchName; Get(ScriptParameter)]
• Set Field [InvoiceID; $Inum]
• Commit Records/Requests
• Exit Script [$Inum]
The second solution (there are other solutions as well) looks better but when you have 100'000 invoices per branch the first solution will be f'ing faster.
You might also consider the data model — maybe you only have one table for invoices, but you have a separate .fmp12 file for each branch, plus one .fmp12 file for data shared between them, whatever that might be. I'd be inclined toward that myself, since it scales a little easier if you add more branches.
Yes, I already have one invoice table that all of our branches share and this invoice file is related to other databases. I also have
a separate branch file set up that is related to multiple databases already. So hopefully there is a way this can be done rather simply.
The more tables I set up the more complicated the relations have to be.
Each branch has been entering their own invoice number sequence manually but I would rather this be handled automatically when the new
invoice record is created. The invoice record is actually created from a related “Quoting” database.
Any thoughts you have will be appreciated.
We started at separate tables per branch and moved on to separate files? I just threw up in my mouth a little.
There's at least several ways of doing this that don't result in Edgar Codd rolling over in his grave.
How about a self-join relationship between invoice tables based on Branch ID, and the Max()Last() function with a relationship sort? Using a script to search the current branch record, sort by invoice number and going to last record? Even storing the last invoice number used in each branch in a single separate table (where each *record* is a branch) is preferable to adding tables or files.
As a general principle, I would like to see more often people who are asking questions giving us information about their "Datenmenge" i.e. actual and projected number of records.
Doing a quickfind on a branch number might be instantaneous, but sorting 1'000'000 invoice numbers then going to the last record to get its number and add 1 to it might be a solution granting Ed Codd's peace but not the happiness of actual users.
There are solutions to a problem which look awful but will work well with a huge data set; should we stick to strictly normalized, pure solutions ? I don't think so, but YMMV.
If everything is up for grabs, what would keep my own bile down would be to just relax about per-branch sequences. On the one hand, gotta get the customer what they want. On the other hand, what difference does the invoice number sequence make? Increment the number company-wide and get on with life.
Without dismissing the OP's comments out of hand, however, I was thinking that if separate tables was remotely an option, going further to a separate file for each branch would have an additional advantage of being distributed and isolated, ensuring some level of operation if the WAN link went down, for example, and allowing for running the server locally in the first place, with only the shared data over the WAN.
It sounds like there are branch-specific files in operation anyway, so maybe the idea has some merit. Moving the invoices table out to each of the branch-specific files is not likely to be trivial, but it could be a reasonable solution.
Assuming the requirements are valid in the first place, that is.