keep the same original workorder number but add (-1) to it
Chiefsmokin, what are u smokin' ?
Lol let me try again.
We survey land, we attached workorders to lot #'s
client A buys lot 10 workorder = 16-001
client B buys lot 28 workorder = 16-002
the auto-serial numbering works great for new workorders
Client C buys lot 10 from Client A workorder = 16-001-1 for new client. I copy the 16-001 workorder, change the info on it. once i save the workorder the auto-serial replaces field with 16-003. Maybe i just need to search the the next serial number and type it in myself.
Presumably you have a Work Order table and a Clients table. All Clients should have their own ID Number. Then a work order would use that Client ID.
So, Client A is 16-001 and Client B is 16-001. Then for your WorkOrder ID field, you'll want a calculation that uses a relationship to count the existing work orders for that same client.
WorkOrder ID = Client ID & If ( Count ( related::WorkOrders ) > 1 ; "-" & Count ( related::WorkOrders ) - 1 )
(this assumes you don't delete work orders. if you do, you'll need to make adjustments)
Of course, you should have an actual UUID field for both Clients and WorkOrders that is used for relationships and not use the Client ID or the Work Order ID field for that. Those are for display only.
Do whatever you have to do to make this human-readable serial number what you want it to be. But please—don't use this as your uniqueID for FM purposes.
could a script be written that would read the work order field and if there is data in that field have the script add a (-1) to it. Example workorder 16-098 duplicate record; newly created work order is now 16-098-1? Or if the field has 15-095-3 would the same script be able to duplicate the record with 15-095-4? The work order numbers are not the id of the record for FileMaker the work orders are not necessarly locked to a client, the workorders are locked to the lot #.
I tried this but nothing was renamed
Client C buys lot 10 from Client A workorder = 16-001-1 for new client.
So do you want if
Client D buys lot 10 from Client C workorder = 16-001-1-1
or ignoring "from" part then
16-001-2 and so on this particular lot # no matter how many clients buy, order, request work on that particular lot.
It makes more sense to me to have a Lot # field ( 16-098) and a field for "Work Order Sub ID" = 1 or 2 or nothing.
Then a third field that combines them (16-098-2) if needed.
I'm not sure if Lot # is really how it works, but that would be the general idea. All the 16-098s (whether there's 1 or 10) share this quality. The -1 or -2 differentiates them from each other.