Controlling Auto-Increments on specific records

[Ron Kline]

Hello All!

I’m trying to write script for a field that will auto populate a batch # for products being made. The format for the batch # will be Product ID #-MMYYYY-Batch #.

Product ID # – this is a number I have already assigned to every unique product that can be made
MMYYYY – this will be the month & year for when the product is made
Batch # – I want this to be an auto-incrementing # that starts at 1 (or 01 or 001) for each unique Product ID # a record is created for.

So, for example, the first batch of a product (lets say product 12) made in a month would have an overall batch # of 12-112019-1 (or 01 or 001). The first batch made for a different product (lets say product 25) would have an overall batch # of 25-112019-1 (or 01 or 001). The 2nd batch made for any given product would have the same beginning numbers as the 1st batch of that product but that last batch # component would increment to 2 (or 02 or 002) and so on. When a new month comes around that batch # component would reset back to 1 (01 or 001) for products made in the new month. So for the first batch of product 12 in the new month the overall number would be 12-122019-1 (or 01 or 001).

I have been thinking about this for the past few days but have not come up with any ideas on how to accomplish it.

If anyone has any advice or ideas I would appreciate it

Thanks!

[Daniel Leu]

Interesting question…. I would create a separate form that has fields for ProductID, Batch# and BatchUpdated (date). This is used to track the current batch number and when this batch number was last updated.

In a form script that updates the batch number, I would check if the batch number was already been updated this month. If this is the case, then it is just a matter of incrementing the batch number. Otherwise, reset it to 1. At the end of the script, update the BatchUpdated field with the current date.

When creating a new record for your production run, you can fetch the current batch number from the batch number form.

Hope this helps!

[Sam Moffatt]

I’d expand a little on Daniel’s approach and actually suggest two forms: one for your products and then a linked form would be the individual batches. The reason for that is that you can look up your products as top tier entries and then see all of the linked batches. I’d put the form script then in that product form and create a new linked record batch records that are linked. This will save you some database traversal where you’re only looking at linked records for the individual product. You could also do some more optimisation with extra lookup fields to speed up creating new records though you can iterate through record lists in Tap Forms pretty quickly.

[Author]

Posts