1 2 Previous Next 17 Replies Latest reply on Sep 8, 2015 7:41 AM by richardsrussell

# Sequential Numbering of Records

I'm trying to create unique numbers for line items but I can't figure out a solution.

Example:

- I create a new record and it's called Order #795

- On this Order, the client buys multiple products (which are lines items)

- I need each line item to have a sequential number (01, 02, 03) that I can use to create a unique number for each product purchased (the unique serial number is the order number and the line item number.... i.e 79501, 79502, 79503)

- The line item number needs to continue on if a previous line item is deleted (if three line items (01, 02, 03) are added to the order, then one line item is deleted, the next line item should be 04)

- Also, each new record/order should have line items that start at 01.

I hope this makes sense. It seems like such an easy thing but I'm just not finding the correct way to add a sequential number.

• ###### 1. Re: Sequential Numbering of Records

Michael -

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.

HTH

Mike

• ###### 2. Re: Sequential Numbering of Records

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?

• ###### 3. Re: Sequential Numbering of Records

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.

• ###### 4. Re: Sequential Numbering of Records

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.

• ###### 5. Re: Sequential Numbering of Records

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!

• ###### 6. Re: Sequential Numbering of Records

I'm going to try this and see if it works. Thanks for the info.

• ###### 7. Re: Sequential Numbering of Records

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.

• ###### 8. Re: Sequential Numbering of Records

This will not work if the latest line item is deleted. It's also vulnerable to duplicates.

• ###### 9. Re: Sequential Numbering of Records

Mike_Mitchell wrote:

This will not work if the latest line item is deleted.

Or any, for that matter.

• ###### 10. Re: Sequential Numbering of Records

Not technically true. It will still work (since Max will pick up the latest version). It's just risky.

• ###### 11. Re: Sequential Numbering of Records

Well, using

Max ( Product::ID )

doesn't make sense anyway, so let's not get into this.

• ###### 12. Re: Sequential Numbering of Records

Hi Michael,

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).

Regards,

Ray

• ###### 13. Re: Sequential Numbering of Records

That's very cool, Dr. Ray. Didn't know about that behavior.

Unfortunately, it breaks when a record is deleted.

• ###### 14. Re: Sequential Numbering of Records

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.

1 2 Previous Next