An auto-enter calc that uses the max() across the relationship and adds 1 should do it.
When you say "serial #", don't ever use those as primary or foreign keys of course...
Hi Wim thank you for your quick reply !
I looked into the MAX function but don't really understand it, this was what filemaker online says:
If the users of your solution will need to reset the values of your serial numbers, a more user friendly approach to allow this is by creating a script. The script step that you will need to use is the "Set Next Serial Value." The "Set Next Serial Value" script step resets the next serial value in an auto-entry number field. This script step allows you use any calculated expression to determine the next serial value of a field. For example:
Set Next Serial Value [Invoices::Invoice ID; Max ( Invoices::Invoice ID ) + 1]
In the example above, the MAX function is used to calculate the next available invoice number for the field "Invoice ID"
But in this example i believe it will only act as an serial number + 1 but i need to exclude the possbility that another customer gets that serialnumber.
In that case the numbers in the portal for that specific client aren't consecutive anymore.
So when i use the standard serial i could get :
Portalrow1 = 2014001
Portalrow2 - 2014003
Portalrow1 = 2014002
Portalrow2 = 2014004
Portalrow3 = 2014005
Where as i would like it to in consecutive order per client. I think i have to adress something like (in dumb english )
When a new portalrow for this client is made -- > then look at the number from last portal row + 1
Any idea is welcome !!
Ps not using it as key...thanks
Trying to use a true serial # using the FM feature is going to be too complex I think. An auto-enter calc should be much easier.
Yes i want to use an autocalc field but don't know how to get the trigger previous portal row for that
Create a self-join for ProductionRecords by clientID = clientID. In the auto-enter calculation, use Max() via that relationship to get the highest existing serial# for that client and add 1.
This is on the relationship level, not the display level, so it doesn't matter if you create the new record in a portal or by other means.
(Or – with the usual caveats – use ExecuteSQL to do it without a new TO.)
Thank you for you reply, tried this but ran into the same problem, perhaps another idea which would work is to count the number of portalrows for that client and build a consecutive number upon that information is that possible.
Something like aClient has 4 portalrows so the next portal row would have number 20140005 in the serialnumber field ?
Don't know where to begin, Portal keep giving me problems hahah
this seems to be a timing problem. Check this out; note that “Do not replace …” is deactivated; it seems that otherwise the initially empty value won't be overwritten with the calculated one.
Also, if you want to start over yearly, you need to tweak the calculation a bit.
Thanks now i understand it !!!
Special thanks for your example, super !!!
I'm definitely going to check out erolst's example file. I have a similar situation with Jobs (think "Line Items") on a parent JobTicket, where we want each job to have a line number. (Actually a line letter, but that's another thread!)
The solution that I had landed on was that in my scripts for adding, duplicating, or deleting line items, I have a "Reset Job #s" subscript that runs. The basics of it are:
GTRR [Show only related records (from current record only); From table: Jobs; Using layout: Jobs; New window]
Sort records by Job ID field (or by creation date if you are using UUIDs)
Go to Record [First]
Replace Field Contents [No dialog; Jobs::JobPart#; Serial numbers]
Close window [Current Window]
Some other error trapping before and cleanup after, but you get the idea.
It's been working fine, but this method is probably much more efficient and less error prone.
The solution that I had landed on was that in my scripts for adding, duplicating, or deleting line items, I have a "Reset Job #s" subscript that runs.
Duplicating is OK, but if you're deleting records and want to avoid gaps, you will still need a script.
Of course, if the numbers aren't really business-critical and their purpose is merely cosmetic (and this seems to be the case if you're allowing deletion), you could as well use a calc field instead of auto-enter.
Calculate the record's relative position within a sorted list of “similar” (by client, year etc.) records, which would automatically update when you delete one of its “siblings”.