# auto increment field based of forgin key relation **URL:** \ **Category:** Using Django\ **Created:** [August 9, 2020, 10:35am UTC](https://forum.djangoproject.com/t/auto-increment-field-based-of-forgin-key-relation/3813 "2020-08-09T10:35:47Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![nhrnhr0](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/nhrnhr0/32/1613_2.png) [@nhrnhr0](https://forum.djangoproject.com/u/nhrnhr0)\ **Post date:** [August 9, 2020, 10:35am UTC](https://forum.djangoproject.com/t/auto-increment-field-based-of-forgin-key-relation/3813/1 "2020-08-09T10:35:47Z")
I have a `Category` module that represents the category of a product in a store. example category: ‘winter clothing’ and ‘hats’ I also have a `Product` module that has a name and a `ForeignKey` field to a category. I want some kind of a running number in `Product` to show the order they inserted with the category. I try to explain it the best I can but I know it’s hard to understand, so here is an example of what I want: #### Categories: | id | title | | 1 | winter clothing | | 2 | hats | #### Products: | id | name | category\_id | auto\_number\_in\_category | | 1 | cool hat1 | 2 (hats) | 1 (first ref to hats) | | 2 | cool hat2 | 2 (hats) | 2 (secound ref to hats) | | 3 | Coat | 1 (winter clothing) | 1 (first ref to winter clothing) | So my question is, how do I create this `auto_number_in_category`? Thank you very much! ---
**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\ **Post date:** [August 9, 2020, 2:06pm UTC](https://forum.djangoproject.com/t/auto-increment-field-based-of-forgin-key-relation/3813/2 "2020-08-09T14:06:30Z")
Do these sequence numbers need to be in strict consecutive sequence, or do they just need to be in order? If the later, you could just define that field as an autoincrement field, which might end up giving you something like this: > [@nhrnhr0](#): > > #### Products: > > | id | name | category\_id | auto\_number\_in\_category | > | --- | --- | --- | --- | > | 1 | cool hat1 | 2 (hats) | 1 (first ref to hats) | > | 2 | cool hat2 | 2 (hats) | 3 (secound ref to hats) | > | 2 | Coat | 1 (winter clothing) | 2 (first ref to winter clothing) | Notice that it does maintain the proper sequencing, even if the numbers aren’t consecutive. You could then derive the sequential numbers later when you retrieve them. Note: There are other ways of doing this that would maintain the strict sequencing in the database, but they have particular intricacies of their own - particularly when you’re removing and/or reinserting entries. Trying to maintain strict consecutive order in that table is not a trivial issue. ---
**Author:** ![nhrnhr0](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/nhrnhr0/32/1613_2.png) [@nhrnhr0](https://forum.djangoproject.com/u/nhrnhr0)\ **Post date:** [August 9, 2020, 2:40pm UTC](https://forum.djangoproject.com/t/auto-increment-field-based-of-forgin-key-relation/3813/4 "2020-08-09T14:40:55Z")
Thank you for your response! unfortunately, I need `auto_number_in_category` to be a strict consecutive sequence. because I use it to generate a catalog number for the product. How would I do that? By the way, I saw I made a mistake in the products table, the `id` for `Coat` should have been 3 (fixed). To clarify the `id` field in products and categories is the django’s default. ---
**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\ **Post date:** [August 9, 2020, 2:45pm UTC](https://forum.djangoproject.com/t/auto-increment-field-based-of-forgin-key-relation/3813/5 "2020-08-09T14:45:58Z")
But why do you think the catalog numbers need to be in strict sequential sequence? Also, are these catalog numbers stored somewhere else? Where else are these catalog numbers used? Again, this raises the issue, what happens when a product is deleted? Do you then renumber the table? Do you reassign catalog numbers? If you don’t, then you end up with gaps in the sequencing _anyway_. This is one of those types of items, that if I were given these requirements from someone else, I’d try to make it clear to them that this is not a trivial situation, and that the degree of effort may not be worth it for the desired results. Let’s be real - who _really_ cares what the catalog number is over time? ---
**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\ **Post date:** [August 9, 2020, 2:55pm UTC](https://forum.djangoproject.com/t/auto-increment-field-based-of-forgin-key-relation/3813/6 "2020-08-09T14:55:41Z")
Now, having said that, if there were a truly valid reason why this might be considered a reasonable requirement, my immediate inclination would be to override the save method on the products class, such that when a row is saved, it checks to see if the number\_in\_category field is already set. If not, I’d do a query to find the currently-highest value for that category, add 1 to it, then assign it to that field. ---
**Author:** ![nhrnhr0](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/nhrnhr0/32/1613_2.png) [@nhrnhr0](https://forum.djangoproject.com/u/nhrnhr0)\ **Post date:** [August 9, 2020, 3:10pm UTC](https://forum.djangoproject.com/t/auto-increment-field-based-of-forgin-key-relation/3813/7 "2020-08-09T15:10:52Z")
the catalog number is very important to me, we later use it to give information and sort our warehouse. the catalog number system we want to use is `ABB-000` `A` is category representation (to every category has a Char based on the order it added) `BB` is a representation of the product inside the category (HOW DO I DO THIS?!) `000` is the products `id` right now I’m focusing on the BB part of our catalog number. ---
**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\ **Post date:** [August 9, 2020, 3:39pm UTC](https://forum.djangoproject.com/t/auto-increment-field-based-of-forgin-key-relation/3813/8 "2020-08-09T15:39:30Z")
> [@nhrnhr0](#): > > the catalog number is very important to me, we later use it to give information and sort our warehouse. Absolutely! No doubt about that. However, that doesn’t answer the questions: - Why do you think the catalog numbers need to be in strict sequential sequence? - What happens when a product is deleted? - Do you then renumber the table? - Do you reassign catalog numbers? (If not, then you end up with gaps in the catalog numbers anyway) - Are these catalog numbers stored somewhere else? - Are you building them once and then saving them, or are you dynamically creating them as needed in your application? > [@nhrnhr0](#): > > `BB` is a representation of the product inside the category (HOW DO I DO THIS?!) You can create a method in your model that returns data in a desired format. You can then call that method on an instance of that model to return your custom-formatted field. Typically, you’ll see this in various examples or tutorials as overriding the ` __str__ ` method on the class to allow functions like `print(model_instance)` to print something useful or informative. ---
**Author:** ![nhrnhr0](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/nhrnhr0/32/1613_2.png) [@nhrnhr0](https://forum.djangoproject.com/u/nhrnhr0)\ **Post date:** [August 9, 2020, 4:20pm UTC](https://forum.djangoproject.com/t/auto-increment-field-based-of-forgin-key-relation/3813/9 "2020-08-09T16:20:17Z")
> [@KenWhitesell](#): > > - Why do you think the catalog numbers need to be in strict sequential sequence? **you are right, I don’t need them in strict sequential sequence but I do need them to start from 0 every category for order and that the `A` and `BB` are uniqe together in the catalog number** > - What happens when a product is deleted? **A gap dose created but I know that if the ‘BB’ part is small it’s somewhere in the start of the category** > - Do you then renumber the table? **No** > - Do you reassign catalog numbers? **No** > [@KenWhitesell](#): > > Are these catalog numbers stored somewhere else? **the catalog numbert saved in parts and assembled for each product > I take the `A` representation from the category’s `forgin key` > and the `BB` I still think about how to take, > the `000` is just the product’s id** > > - Are you building them once and then saving them, or are you dynamically creating them as needed in your application? > > **I try to dynamically create them** Thank you very much for your help!