Change field contents with calc

[faaslave]

I have a field with numbers. ie, 1-14,000.

I nee to make them into 6 digit numbers starting with 000001 through 014000.

What is the easiest way to enter a calc to do this?

Thx Dave

[comment]

SerialIncrement ( "000000" ; YourNumber )

The result should be Text.

[faaslave]

The numbers are already in there, So I was hoping to make a calc that will change the existing ones to the correct format first.

So 1 will become 000001, and so on.

I changed it to a text field and future records will be done correctly, I just need to fix the ones that exist now.

Thanks, Dave

[comment]

Show all records, click into the field and select Replace Field Contents... from the Records menu. Replace the contents with a calculation using the above formula.

Make sure you have a backup, since this is not undoable.

[faaslave]

Show all records, click into the field and select Replace Field Contents... from the Records menu. Replace the contents with a calculation using the above formula.

I tried but nothing happened. And I am afraid to increment them because of one problem.

I have 14,000 or so records, numbered 1 through 14,300

That means some of the numbers are not used.

Each record needs to keep its number with the leading 0's added.

Then I need to find which numbers are not being used, and insert records using those numbers.

Is there a calc that will just add leading 0's so the number totals 6 digits, regardless of the current length of the number.

So, 1 ends up 000001, and 2378 ends up 002378.

I would also love to be able to run a report that would show me which numbers were not being used.

Thanks for your help comment

Dave

[comment]

Is there a calc that will just add leading 0's so the number totals 6 digits, regardless of the current length of the number.

Yes - the calc above (provided the number is less than 7 digits, of course).

[faaslave]

I know this must be annoying for you, but I don't get it.

SerialIncrement ( "000000" ; YourNumber )

The result should be Text.

Your Number is suppose to an increment. If I put 1, then all records end up with 000001.

I clicked in the field, then I clicked on replace.

Then what goes in place of Yournumber?

[comment]

You need to "Replace with calculated result". Instead of YourNumber use the name of your field.

[faaslave]

It worked. I was not using the quotes around the 000000's even though you had them right there on your example. Thank you very much.

What I don't get though, is the formula calls for incrementBy where I put the field name. So I don't understand why this works. Seems to me that the formula is asking for a starting number, then incrementing by the amount in incrementBy.

I am glad this works, but not sure it makes sense to me.

Thanks Dave

[comment]

Seems to me that the formula is asking for a starting number, then incrementing by the amount in incrementBy.

That is correct. The starting number (or more correctly, text) is ""000000". The number to increment by is the existing number in each record.