Every table in a Widgets DB has a single primary key named id. This design is an essential aspect of how the platform works. There are several ways to assign IDs to your records.

Automatic ID Assignment

The simplest, and often the best, way to assign IDs to records is to let the system do it for you. You can simply omit the id column in the new record JSON, and new record will get a system-assigned ID. The new ID is available immediately after the item is built. For example, consider the following code:

        const newrec = {
            "chore_id" : choreid,
            "day_code" : getTodayCode().getDateString()
        };

        const newitem = W.buildItem("chore_comp", newrec);
        console.log(`New Item ID is ${newitem.getId()}`);
        newitem.syncItem();

The definition of the chore_comp table is:

        CREATE TABLE chore_comp (id int, chore_id int, day_code varchar(15), primary key(id))

The ID-allocation algorithm is very simple. It generates integers at random in the useable range, until it finds one that is not currently being used. The useable range is roughly -2.1 billion to positive 2.1 billion, with the exception that numbers from -1000 to 0 are not considered. This exception is so that developers can use -1 or other numbers in that range to represent an "unselected" option: if the SELECTED_ID is -1, you display a main listing, if it is set to something else, you look up the corresponding record and show the detail for it.

This system is intended to avoid collisions when multiple users are editing data in the same Widget DB. Consider what would happen if the next ID was just the max current ID plus 1. Then if two users loaded the Widget data at the same time, and both created new records, then those records would have the same ID, which would cause the data from one user to overwrite the other.

Incremental ID Assignment

There are some situations where it is awkward to use long numbers for record IDs. Long numbers are harder to remember and speak aloud. If you want to tell your colleague the ID of a record that is causing problems, it's easier to say "45" than "-202758134".

To support situations where users prefer to keep records small, the system has a way to obtain new incremental IDs. The function is W.newIncrementalId(tabname). This function will find the record with the highest current ID for the given tablename, and then return this number plus 1. This can then be directly used as the id argument in the record-creation JSON. For example, using the same code snippet as above:

        const newid = W.newIncrementalId("chore_comp");

        const newrec = {
            "id" : newid,
            "chore_id" : choreid,
            "day_code" : getTodayCode().getDateString()
        };

        const newitem = W.buildItem("chore_comp", newrec);
        newitem.syncItem();

This approach will tend to keep the IDs small. The system will allocate ID 0 to the first record in the table, and go up from there. Note that this technique can create problems with ID collisions in multi-user scenarios, as described above.

User-Specified ID Allocation

Finally, you are free to choose your own IDs for your records. The only rule is that IDs must be unique.

One scenario where it can be very useful to specify IDs is when you are working with data from an external source, and that source has its own ID system. In that case, it is convenient to just reuse the external system's IDs. Here's a simple example:

        const externalrec = getExternalRecordList();

        extlist.forEach(function(extrec) {
            const newrec = {
                "id" : extrec["sku"],
                "price" : extrec["price_info"],
                "info" : extrec["description"]
            };

            const newitem = W.buildItem("product_info", newrec);
            newitem.syncItem();
        });

The idea here is that the external data source has three fields, sku which is a unique ID, price_info and description. The above code moves these fields into the column names that are defined for the Widget DB, and uses the SKU as the unique ID.