Building a smarter counter

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

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.

Photo of Alan

Alan

  • 0 Points

Posted 5 years ago

  • 0
  • 1
I'm not understanding part 2 of your question, but for you main question, what if instead of a summary count, you did a summary maximum of the batch #.  That way if batch # 3 were deleted and there was a batch #5 in the data, the maximum would be "5".
Photo of Alan

Alan

  • 0 Points
Thanks for the feedback. I'm not sure if I follow your idea to use the maximum. My main goal is to have the app automatically assign a batch # to each batch starting at batch # 1 on each Daily Log. As for part 2, the app is tracking batches that are being produced each day. So if something happens and they can't produce the last batch or two on a given day, those batches need to be the first batches produced the next day. So they need to be added to the next Daily Log and need to be batch #1. The way my current solution works is if I move them to the next Daily Log, they will be the last batches. I think this will be a little trickier to accomplish so I'm more interested in solving the first part of my problem for now.
Sorry, I just saw this now.  You seem to be suggesting that you have it working but if you delete a batch, then you end up with duplicate batch numbers.  I assume that you are using the technique to do a summary count, and then a lookup and then doing a snapshot of the lookup.  Is that what you are doing?  Or do you have a semi manual method?

If you are suing the snapshot method, then what I was suggesting is that rather than counting the batches, you would do a summary maximum of the batch number, in order to know the highest batch number used so far.  That way if you delete a batch , you will not end up with duplicates.