zipcode field removing leading zeroes

guess you're using a numeric field for the zipcode. They allways remove leading zeros.

You can add a textfield (newZipcode) and then do "Update multiple records..."

Step-by-step:
1. Add a new text field (newfield)
2. use the cogwheel
3. Select "Update multiple records..."
4. search the new text field (newfield)
5. use his dropdown field: "assign calculated value"
6. klick into the white field to the right of the dropdown
7. chose the field name you want to move data from (oldfield)
8. chose OK-button
9. make sure, the drop down in the lower left corner shows "update all visible records"
10. chose blue button "continue"

Hope, this'll do it for you.

You need a 'Text' field and not numeric.

I have a table with zipcodes linked to a table with cities and states. I don't want to go through the hassle of reconnecting the fields, so I'm creating a ZipPrint field which is the display value of the zipcode field (which as a number field here has no leading zeros). I can fill the table as described by Nick above, but each new record will by default still be missing the zeros in the linked field. Below is the formula I've made to update the zipPrint field each time a record is added:

let zipIN := trim(text(ZipCode));
let ln := length(zipIN);
let zeros := 5 - ln;
let padded := lpad(zipIN, zeros, "0");
ZipPrint := format(number(padded), "00000")

The above code assumes that you have  a number field for the innitial ZipCode values. I think the length will get messed up if you draw the innitial values from a text field (which could for instance contain leading zeros).

When you do the "update multiple records" as suggested by tacho, you can use:

format(ZipCode, "00000")

as the formula for the new "text" based ZipCode or zipPrint field. You place that code next to the new text field in the dialog box, where "ZipCode" represents the name of the original number-based field that refuses to display leading zeros. Then, as I mentioned, if you have a relational database model that requires you to keep the original field connected to the rest of the database, you can simply place a formula like the one in my previous post into the "on update" or "on create" options, depending on what works for your system.

To put it all together...

this is the formula that I placed in the "Trigger after Update" section of the table (not on the specific field for zipcodes, although I think you could place this code in the original number-based field as well):

let zipIN := trim(text(ZipCode));
let ln := length(zipIN);
let zeros := 5 - ln;
let padded := lpad(zipIN, zeros, "0");
ZipPrint := format(number(padded), "00000")

ZipCode is my number-based original field that has no leading zeros, ZipPrint is my new text-based field.

If you do not have any data in place yet, I think you can avoid this all by simply making a text-based ZipCode field and then importing all your data with zeros.

In the case that you already have lots of data in place, and will never add additional zipcodes, and have not connected the table to other tables via the zipcode field, you can skip the above forumula, and simply run the "update multiple records" , as mentioned above, with  format(ZipCode, "00000") as a calculation type update in a newly created (text-based) "ZipCode 2" field. Then you can just delete the first field and use the new field.

My code above seems necessary when you have imported a decent amount of data and then created relationships to other tables that use the number-based "ZipCode" field to link the records. Since it seems impossible in Ninox to link two existing fields in a parent-child relationship, (you always have to create a new field in one of the tables to be the link), then deleting the original field creates a painful amount of work in re-linking all the data. It is possible that I am wrong about all of this, as I am new to Ninox, but I have utterly failed to find a way to link two fields in a parent-child relationship, when already exist in different tables. To a certain extent, I'm hoping I am wrong, and someone can show me how to connect two existing fields in a way that does not create a third field.

Ben, Steven already gave you a link in this thread...

https://ninoxdb.de/en/forum/technical-help-5ab8fe445fe2b42b7dd39ee7/one-to-many-relationship-5b705fed9efc8227533e7c96?post=5e504b273242dd01559d71ff&page=1

Here is the link again and there another as well...

https://ninoxdb.de/en/forum/technical-help-5ab8fe445fe2b42b7dd39ee7/how-can-i-automatically-link-two-fields-from-two-tables-5c8cfc004a46f37365e3968f

https://ninoxdb.de/en/forum/technical-help-5ab8fe445fe2b42b7dd39ee7/is-it-possible-to-link-automatically-via-a-function-fields-in-two-tables-that-contain-the-same-text-5c6aaf80e850ad238982b496

If you are unable to make it work, post the code you are using.

Hi Sean, the link Stephen gave is to your code in the thread that you reference here. I will post my copy of the code below yours over there, but in my tables, the button did not link two existing fields, but rather linked the tables via a third field with the same data as the existing fields. However, it also added a character to the data. But I don't want to derail this thread, which has some very useful tips about number fields and leading zeros more than I already have, so I will post my code and details on the thread where you indicated in the link, and perhaps I made an error there that is generating a new third field or something. Thanks in advance for your help. Honestly, I am really liking Ninox, despite my constant questions and learning curve