Selective auto-generate numbers

Support
,
1

Hi everyone,

I am struggling with auto numbering for particular situation. I’ll try to explain here. There is an “Order number” field that must be unique and sequential.

We have 2 options for our products:

If field “Type”: “New Business” is selected, we do auto numbering for Order number in format 1{1,date,YY}{0,number,00000}, so 12500001, 12500002, 12500003…

If field “Type”: “Endorsement” is selected, we need to pass old Order number (done with helper field). And add E{0,number,00} and set it as Order Number. So 12500001E01, 12500001E02…

The problem is that i can generate “Order number helper” field AND endorsement number with IF function based on what is selected - Endorsement or New Business and pass to “Order Number”.
However “Order number helper” is still generated and skips the sequence for next Order number (because it is saved in database).

Example:

We have existing order numbers:

12500001,
12500002,
12500003.

We do endorsement for 12500001, so next Order number is 12500001E01.

I need next “New Business” order number to be 12500004, but it will be 12500005, because 12500004 is captured within 12500001E01 entry.

If i do another endorsement for 12500001, it should be 12500001E02.

So required sequence example would be:

12500001,
12500002,
12500003,
12500001E01,
12500001E02,
12500004,
12500005.

But instead we have:

12500001,
12500002,
12500003,
12500001E01, (captures 12500004)
12500001E02, (captures 12500005)
12500006,
12500007.

Any ideas on how to achieve this selective auto numbering in 2 ways for 1 field?

I’ve tried writing a script that calls for auto-generate function based on “Type” field and load last Order number to proceed, but with no luck. Stuck here for 4 days already. Please help.

2

Hello!

This is Kate from Ragic Support. Thank you for your detailed explanation — it’s really helpful.

Before diving into a solution, I’d like to clarify the data structure with you. From what you’ve described, it seems that an “Endorsement” is not an independent order, but rather a modification or extension of a specific “New Business” order. In other words, each New Business order may have multiple related Endorsements. Would you agree with this interpretation?

If so, from a database design perspective, “New Business” and “Endorsement” should be treated as data on different hierarchical levels. An auto-generated number field in Ragic counts records at the same level — that is, every time a new record is created in the same sheet, the auto number increases by one. This is why Endorsements are currently taking up New Business sequence numbers — they’re all being stored as separate records on the same level.

To address this, we recommend restructuring your form as follows:

  1. Use a subtable under each New Business record to store related Endorsements. Here’s a quick guide if you’re new to subtables.
  2. In the subtable, add a Sequence field to represent the Endorsement number. You can follow this guide to set up subtable sequence numbering.
  3. Then, create a calculated field for the Endorsement Order Number using a formula that combines the main order number with the Endorsement sequence, such as:
    A1 + "E" + TEXT(A5, "00")
    (Assuming A1 is the main order number and A5 is the Endorsement sequence.)
    (Learn more about how to use TEXT formula here.)

This setup allows you to maintain a clean, uninterrupted sequence for New Business orders, while keeping Endorsements logically and visually linked to their parent orders — without consuming main sequence numbers.

Hope this direction is helpful! Feel free to test it out and let us know if you have any questions or need assistance with the setup.

Warm regards,
Kate
Ragic Support Team

3

Hello, Kate, and thank you for your reply. Yes, puting Endorsement under New Business record was an option i first thought about. But it’s only viable from a technical standpoint, however from business point of view - “Endorsement” and “New Business” is of equal importance and in the final reports they can’t go as a dependant subtable values. They are both of equal importance and should fall into same list. For example endorsement can be created 2 years after initial “New Business” was created, and we do a monthly reports - we only care about contract as Endorsement for the moment. I think we will have to keep skipping numbers as it is best option so far.

1 Like
4

Hi rytis,

Am I correct in understanding that Endorsements are a variation of a New Business record? Basically Endorsements and New Business records have the same fields, just that Endorsements are like a newer version of the previous New Business record.

If so, then it would be a similar setup I’ve created for my quotation database, where there are versions of the original quote. (i.e. Q12345, Q12345A, Q12345B)

What you will need to do is to actually to manually track the endorsement number for each New Business entry using additional fields and convert records action button. These are the steps:

  1. Keep the original Auto Generate field for Order Number (i.e. 1250001, etc.), but rename it to ONumber or something else.
  2. Add these fields Endorsement Number, Current Count, Previous Count, and Order Number.
  3. Set the default value for Current Count to 0 and the formula as [Previous Count]*1+1, set their Format Type to 00

4.In the Endorsement Number field set the formula IF([Current Count]==0,“”,“E”+[Current Count])
5. In the new Order Number field set the formula to [ONum]+[Endorsement Number]
6. Setup Convert Records button to convert back to the same sheet, you have to match all of the corresponding values except Revision Count to Previous Count.
7. Name the button as New Endorsement.
8. Use that button only for new endorsements, and new entry only for actual new company
9. if it works you can hide all the fields above except Order Number

*replace the [… ] with the actual field reference number.