I've seen a few solutions on here to build various counters but mine seems to be a little trickier.
I have a table with Daily Logs and another table with Batches. Each Daily Log can have multiple Batches and, as the name implies, these logs are generated daily. I'm trying to build a field that automatically populates each Batch in the Daily Log with a Batch # (1 - 30).
I was able to accomplish this function by creating a summary field on the Daily Log, and a lookup field on the Batch with a formula field that counts the next number and uses that as the Batch #. When the user creates a new Batch for the Daily Log, it will add 1 to the total number of Batches on that Daily Log and use that as the Batch #. The count starts over for each Daily Log so it works in that regard.
However, I have 2 issues and I'm hoping to find a better counter. If the user has 5 Batches, for example, and deletes Batch #3, it will delete Batch #3 without renumbering the rest of the Batches and the next Batch a user creates will obviously be #5 even though that's already in use, leaving me with Batch #'s 1,2,4,5,5.
Next, if a Batch is on Daily Log 1, and doesn't get physically made, the user needs to reschedule it to be Batch #1 for the next Daily Log (Daily Log 2). My current solution has no way of accomplishing this. It would automatically make it the last available Batch.
Hopefully I explained this in a way that makes sense and I'll keep looking for solutions on here but if anyone has any ideas, I would appreciate it.