Creating a serial number that checks for duplicates

[Gary_Trzcianka]

I am creating a part number generator that looks at a column for module code, then a column for type, then a it grabs the last two digits of the year created finally 4 digits that increment.

I used the AI formula generator and I got mostly there.

The issue I have and maybe I need additional columns, is I do not want duplicates.

For example I have a Module code of BA a type code pf P and a date of 2023
A second one a Module code of BC a type code pf A and a date of 2024
the first part is easy and comes out as BAP23
the second one BCA24

What I am struggling with is when I add the next 4 digits and I want them to increment, I need them to check the codes and increment from there.
So if I have BAP23005 the next time BAP23 comes up it makes the next one BAP23006 when the year changes to say 2024 I would like it to start back at 0000 so I would get BAP240000

I think I am creating a lookup loop but not sure how to fix it.

Thanks for any help.

Here is my AI chat and the code:
I need the first two letters from the module code the first letter from the type the last two digits of the year from date created and then four digits after that the formula should check the previous rows and if it finds duplicate number increment the last four digits by 1 so there are no duplicates

def A(rec, table):
module_code = rec.MODULE_CODE[:2]
type_letter = rec.TYPE[0]
year = rec.CREATED_DATE[-2:]
digits = int(rec.CREATED_DATE[-4:-2])
incremented_digits = str(digits).zfill(4)

# Check for duplicate numbers in all previous rows
all_rows = Sheet1.lookupRecords()
for row in all_rows:
    if row != rec and row.MODULE_CODE[:2] == module_code and row.TYPE[0] == type_letter and row.CREATED_DATE[-2:] == year and row.CREATED_DATE[-4:-2] == incremented_digits:
        digits += 1
        incremented_digits = str(digits).zfill(4)

result = module_code + type_letter + year + incremented_digits
return result

[natalie-grist]

Hi there!

I created an example for you here: Community #4135 - Grist

First, I broke out some of the different components into different columns. As you described, I have a column for Module Code, Type and Created Date. I added a formula column to pull the Year value from the date value in Created Date.

Then, I added trigger formula column, Number, to calculate the number to assign to each record (to be used in the last 4 characters of the Serial Number). I made this a trigger formula column because once you assign a serial number, you likely don’t want that number changing. With a trigger formula column, it will only trigger on a specific set of conditions. In this example, it triggers on changes to Module Code, Type or Created Date.

The trigger formula used in the Number column is:

if $Module_Code and $Type and $Created_Date:
  all_group = PEEK(Table1.lookupRecords(Module_Code=$Module_Code, Type=$Type, Year=$Year))
  return PEEK(MAX(all_group.Number))+1

First, the formula checks to see that Module Code, Type and Created Date all contain a value. If so, then the formula uses lookupRecords() to find all records in the table where the Module Code, Type and Year match the values in this record.
Then, it finds the MAX() value in the Number column for those matching records and adds 1.

Finally, I added a formula column for Serial Number that adds all these details together to create our final serial number.

You can hide the Year and Number columns once they are set up. The calculations will run in the background as you add records.

Hope this helps! Please let me know if you have any follow up questions.

Thanks,
Natalie

[Gary_Trzcianka]

Natalie,

Thank you, this was very helpful, I thought I had it working but my Serial Number is not updating the last number. I imported my Part Number into the spreadsheet so I could check that it is working as it is supposed to:

Here is the code for the Number column:
if $Module_Code and $Type_Single_Letter and $Created_Date:
all_group = PEEK(Table1.lookupRecords(Module_Code=$Module_Code, Type_Single_Letter=$Type_Single_Letter, Year=$Year))
return PEEK(MAX(all_group.Number))+1

I should state one other thing, and I think I know how to do it, once the number is created I do not want it to increment if any changes are done to the row. I know I should uncheck Apply on Record Changes, do I also want it to uncheck Apply to New Records or is that needed whenever I create a new record it checks for a duplicate and increments it?

I was also wondering, the columns Module and Type are usually a reference list, when I tried that it would put the entire word in like PART instead of just P, are there restrictions on how the code reads reference lists?

Would it be easier if you had access to my database?

Thank you again for amazing detail you provided, it really helped clarify how to do what I wanted.

Gary