Probably the easiest thing to do, given your requirements, would be to write a script to create a new line item. Couple this with a field on the parent record (Order) that stores the maximum line item number created (to save the line item number that should next be created in case of deletions).
You can then simply insert the number as part of creating the new line item. I would also consider a scripted solution because you can avoid the possibility of two users trying to create a new line item number at the same time and ending up with a duplicate (which can happen if you try to use a portal, in this case). Your script can create the new line item number and perform a Commit Record / Request script step to ensure other users get the changes so that doesn't happen.
I'm not really understanding "a field on the parent record (Order) that stores the maximum line item number created (to save the line item number that should next be created in case of deletions)."
Could you give an example?
On the Order record, have a field called "lastLineItemNo" (or whatever). Initially, it starts out with a value of zero. When you create a new line item, set the line item number to lastLineItemNo + 1. Then set lastLineItemNo = lastLineItemNo + 1. Example:
Create a portal. (Yeah, I know I said not to use a portal, but stay with me.) Use whatever means you like to cause a new line item to be created. (Button, OnObjectEnter Script Trigger, whatever.) When that happens, perform this script:
Go to Field [ lineItem::lineItemNo ]
Go to Portal Row [ Last ]
Set Field [ lineItem::lineItemNo ; order::lastLineItemNo + 1 ]
Set Field [ order::lastLineItemNo ; order::lastLineItemNo + 1 ]
Commit Record / Request
Go to Field [ lineItem::description ]
Go to Portal Row [ Last ]
If you'd rather, you can use a popover with just the line item being edited (you can use a single-row portal for this). It depends on your interface design.
There must be a relationship between Order and Products based on some primary-foreign key.
Use Max function on this relationship i.e. Max(Product::ID)
if empty, use 01 else add 1 i.e. Max(Product::ID) +1
This way is earlier record is deleted then new number or Id will have next value.
I'd like to add a couple of thoughts for your consideration.
1. Re: "I'm trying to create unique numbers for line items". I infer from that that you MAY be thinking that this "unique number"can function as a record ID for use in relationship matching. If so, I strongly advise you—DON'T! A fundamental FM rule is to not use human meaningful/useable data as a match key, ever. Your whole example suggests that this line item number is intended to be meaningful.
2. Re: "the line item number that should next be created in case of deletions". What Mike means is that if you create a line item record for a thingummybob and it is line item 02 in your sequence 79502, then realise you made a mistake, delete it and create a new line item record for a gizmo, you will want this new record to be 79502, not 79503, since the previous 02 no longer exists.
That brings up a further consideration: what if you have already gone ahead and created one or more additional line items for other bits and bobs, so already have 79503 and 79504 created before you delete and replace 79502? Will there replacement take on the 79502 serial? or will 03 and 04 both move down one, and the 02 replacement become 04?
It's a slippery slope!
I'm going to try this and see if it works. Thanks for the info.
Let me give some more info and I think this will help explain why I'm wanting to do it this way.
1. For relational matching within Filemaker (Orders, Order Line items, etc), I'm using a standard serial number. So my orders are ML3000, ML3001, ML3002. This is an additional number I'm needing to generate for an ordering process outside of our company.
We sell custom clothes. A client comes in and we create an Order (ML3000 for example) and they purchase a suit, a shirt and some pants. In order for me to get these items made by our factory, I have to send them a unique identifier for each item (this is what I'm trying to create). This identifier is made up of 3 letters ("MRH"), the last 4 digits of the order (so ML3456 becomes "3456") and a line item number (first item is 01, second time 02, and so on). So identifiers in this example are MRH345601, MRH345602, and MRH345603. This identifier is only used for this purpose.
2. To answer this question, if a Line Item is deleted, I want each line items unique identifier to stay the same. So using my example above, if our client decides to cancel MRH345602, what is left is MRH345601 and MRH345603. And if I add a new item to this order, it needs to be the next sequential number (MRH345604).
The reason I need this is because when a client places an order for an item, I have to order fabric for the garment to be made. When our fabric supplier sends out fabric to our factory, it's tied to this unique identifier. If a client decides to cancel an item and I delete this Line Item from the Order, I need all the additional line items to keep their current identifiers because fabric being sent out is already tied to that number. Additionally, if a client cancels an item and also decides to add a new item, I need that item to be the next number in the sequence.
I really hope this makes sense. Thanks in advance for all your help.
This will not work if the latest line item is deleted. It's also vulnerable to duplicates.
This will not work if the latest line item is deleted.
Or any, for that matter.
Not technically true. It will still work (since Max will pick up the latest version). It's just risky.
Max ( Product::ID )
doesn't make sense anyway, so let's not get into this.
Providing records in the Line Items table will only ever be created via a portal from a related Order record (and therefore only ever by one user at a time), you could achieve what you have described by adding a SeqNo field with the auto-enter formula:
Right("0" & Get(RecordNumber); 2)
That will return sequential numbers with leading zeros for each record in the order it's entered into the portal. You'd then be able to generate a composite line item number in the format you've outlined, using a calculation along the lines of:
OrderNo & SeqNo
(eg in an Auto-enter, replaces existing calculation field).
That's very cool, Dr. Ray. Didn't know about that behavior.
Unfortunately, it breaks when a record is deleted.
So in other words gaps in the sequence do not matter. For that reason, sequentialness is not really required, only an identifier that links the various parts together. That actually makes your task easier, as all you need is a reliable method f generating a unique, linking identifying code number that will not change after its created. In that case, mark well the method given by Ray Cologon.