Maybe this is very old news to everybody else, but it’s new to me, and I thought I’d write it down. Maybe it will help some other newbie.
Background
I’m going to have to import data from an old solution in which each record represents a customer, and there’s a “Customer#” field that was an autonumber field in the old database. In the source record set, because some records were deleted (after being autonumbered), there are gaps in the autonumbering sequence. Let’s say there were 49 source records numbered 102 to 157.
I know how to create an Equation field [in Knack] that adjusts autonumbers to a starting value I prefer, so if I want the first autonumber value to be (say) 1001, I create an Equation field that =
AutoIncrementField + 1000
The Problem
I need to KEEP the old customer number values. And since I have records numbered up to 157, I’d like the first new record entered in Knack to be numbered (say) 158. How do I handle this?
The solution
I can’t import into the Equation field, of course. And I can’t import into the AutoIncrement field, either. So I need a third field, ‘CustomerNumberOLD’. This is simply a number field. Once there are values in that field, the new CustomerNumber value can be generated with this equation:
{CustomerNumberOLD}+1==1?{AutoIncrement}+N:{CustomerNumberOLD}
The point of the first part (before the ?, that is, the conditional part) is to see if CustomerNumberOLD is empty [as it will be for all records created in the future in Knack]. The only remaining problem is to determine the value of N. To do this:
- Initially, in the Equation field “CustomerNumber”, I set N to 0.
- I imported the original 49 source records (with a max value of 157).
- After the import, got the value in the AutoIncrement field. Since I created records while I was developing the app, this value could be anything. When I tested this just now, it was 45.
- I went back into the formula for the (new) CustomerNumber field and revised it to add 112 rather than 0, when the value in CustomerNumberOLD is empty. Why 112? It’s the maximum value of the old, imported customers, minus the current max value of Autoincrement. 147-45=112.
There are other ways to do this, of course. This is just the one I came up with and it seems to have worked. And I should add that this recipe matters mainly because I wanted the new autoincrement numbers to follow on the old ones without a gap. If that didn’t matter, I could have just set N in the formula above to something high enough to guarantee it would be higher than the max old value.
NOTE that, even if I didn’t care about the first new record in Knack having a Customer# one greater than the highest-number record from the old database, I’d only get out of steps 3 and 4 above: I’d still need three fields: AutoIncrement, CustomerNumberOLD and CustomerNumber (the Equation field). And I might still need to put in SOME “N” value in the equation to adjust AutoIncrement number.
I hope that Knack at some point adds an option to Autoincrement fields that allows us to set the starting value. But until that happens, the method above will get the job done AND allow me to retain serial values generated in another app.
