• 0

# Custom Autonumber, auto increment

## Question

Hello,

I am trying to create a custom autonumber to be used in my table. For some reference, there are two tables in question. The first is an already existing table for Bids - Bid Table. This has a Bid ID (regular autonumber) that I need to use in the autonumber (Est_ID) of the next table (Estimate_Table).

We are trying to make the Est_ID for the Estimate_Table to be something like: [@field:Bid_ID]-1,  [@field:Bid_ID]-2....[@field:Bid_ID]-6 where the first part of the autonumber is the corresponding Bid ID it is linked to, plus the number of iterations there are. To clarify further, if we had a record in the bid table (a bid) with a Bid ID of 4050 and we had 3 corresponding records (estimates) in the Estimate Table for it, they would be 4050-1, 4050-2, 4050-3.

I am thinking I will have to use a formula for this but I am not sure. Is it possible to have a formula query to the table to see how many records in the estimate table there are that share the same bid ID, and can auto increment based on the last number of that group? Not sure if I am wording this right as it is a bit confusing. My apologies.

Or, if anyone has successfully attempted this in another way, I would be grateful to see your solution.

Thank you!

## Recommended Posts

• 0

Hello @kpcollier

I am afraid that this cannot be achieved with the Formula field in the table, since the Formula field operates only within 1 record.

As a result, we cannot check/use the values that are stored in the other records in the Formula field.

It looks like the best solution is to create a Triggered Action.

I am not sure about your table design, so I assume that Est_Table is the child table that has the Bid_id field as well.

For example, the table looks like this:

This is the Trigger design:

Here is the output:

Maybe it is possible to optimize the Trigger or use another approach.

Feel free to update this thread if you have any questions.

##### Share on other sites

• 0

Thanks, @CoopperBackpack, I will take this information and run with it. I appreciate the help.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.