Auto Increament issue under child table

[EITS]

Hello,
I have a parent table which has the

1. project.ID,(primary key) (we have many projects)

My child table has multiple rows of…

1. purchase transactions
2. Transaction number

…against each project ID

I need to add auto increament on “transaction number” field. when I add the auto increament field in my child table, Knack takes all the available records into account.

This is how I need to show it

project ID TNP001
Transaction# 1 Purchase amount $ 1000
Transaction# 2 Purchase amount $ 800
Transaction# 3 Purchase amount $ 230

project ID TNP023
Transaction# 1 Purchase amount $ 1996
Transaction# 2 Purchase amount $ 345

How do I auto-increment values on transactions connected to project ID. I need this to repeat if it is for different project ID. Any idea how to do it? Any help would be appreciated.

Thanks!

[Wolfgang_Brötz]

Hello EITS,

in my experience, you could get TN numbers starting from 1 by

1. adding a “Count Transactions” field in the (parent) Projects table
2. adding an Equation field named "Count + 1"with the formula ‘“Count Transactions” + 1’ in the Projects table
3. adding a trigger field in the Projects table which inserts a connected record in the (child) Transactions table setting the “Transaction Number” to “Count + 1” pulled from the Projects table.

Does this work for you?

[EITS]

@Wolfgang_Brötz Thank you for helping, but it didn’t work. I had data already in my database and we have a transaction# field which we manually populate. I was asked if we could add a new transaction# field that auto populates. Attached is the screenshot of two columns side by side. One is the old field which will need to be deleted in future and the new field will calculate the transaction# and populate it going forward. (right now it looks wonky.)

[Wolfgang_Brötz]

Hello EITS, sorry to read that it did not work. Not sure if i understand your issue correctly. Can you express the relationship between the old field y and the new desired field x as a function x = x(y)?

[Sunny_Singla]

Hello,

I think first you need to clear old records or need to handle that number manually.
After that, you need the below steps.

1. Create a count field in the Parent object
   

   

2. Create an equation field in the child object.
   

   

3. Create a number field in child object and add condition rule as below
   

   

4. Create formula field
   

   

5. Result
   

   

AutoNumberTemp is just for internal use(to update Auto Number once) don’t use it’s as unique it’s only showing count after sometime.

Regards,
Sunny Singla
ssingla1985@gmail.com
+919855089359

[EITS]

Thank you Sunny, We were not allowed to delete old files for now. But it is good to know that it can be done.