Custom Autonumber, auto increment

[kpcollier]

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!

[CoopperBackpack]

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. 

[kpcollier]

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