Emmanuel Katto : Best Way to Format Ascending Lot Numbers Based on Ingredient and Date

[emmanuelkatto23]

Hi Team,

I am Emmanuel Katto, trying to figure out how to format lot numbers in a consistent way for different ingredients in my inventory system. Here's what I need:

The lot number should be in the following format:
SupplyID-Date-Ascending Number

• SupplyID is the unique identifier for the ingredient (e.g., 001, 002, etc.)
• Date should be the date of creation in the format DDMMYY (e.g., 241012 for October 12, 2024)
• The Ascending Number should increment for each new lot for each ingredient, starting at 01 for the first lot of each ingredient on any given day.

Regards
Emmanuel Katto

 

[CJ_London]

Your requirement does not make sense - insufficient information, no context and confusing example

Probably better to keep as separate fields/controls? But not prepared to waste my time speculating

 

[GaP42]

Agree with @CJ_London re separate fields:
1. Do you have the SupplyID in your ingredients records? I presume it is not the PK for the ingredient. In the ingredients record it might not be named SupplyID but there does need to be a field: text, unique (with restrictions on which characters can be used?)
2. You can create a date string using the FORMAT function:
https://learn.microsoft.com/en-us/o...format-function-visual-basic-for-applications
3. The ascending number - do you have a record where its value will be stored? You indicate it is incremented for each ingredient on a given day. As there are multiple ingredients in a lot - it can't be in the Lot table according to your requirement. Do you have a Lot-ingredient table that handles the combinations of ingredients in a lot?
The assignment of the ascending number to such a record will basically need to:
- check if the ingredient, on this day has been used - if not then initialise to 1
else find max value for ascending number for this ingredient on this date and increment by 1
- convert the ascending number to a string padding with 0 to the length required.
This is the Ascending Number component of your lot number

Once you have these elements you need to concatenate the strings to make the lot number.

Good luck

 

[Uncle Gizmo]

This op is not known for engaging!

@Minty stated:-

If I get any response from the OP for this reply I would like a chocolate biscuit.

Did you get your chocolate biscuit @Minty

 

[CJ_London]

This op is not known for engaging!

Which is why I’m not speculating

 

[Minty]

This op is not known for engaging!

@Minty stated:-

If I get any response from the OP for this reply I would like a chocolate biscuit.

Did you get your chocolate biscuit @Minty

Nope, and the coffee has gone cold now!

 

[Mark_]

"Date-Ascending Number" should be a Date/Time field. Time is then your "Ascending number". Is this for a school assignment? If so, I can explain in more detail.