Editing

Overview

Servoy's data editing capabilities allow developers to create, modify, and delete records with precision and control. These operations are essential for managing dynamic data in real-time applications. Servoy’s framework supports transactional operations, which means that developers can ensure data consistency and integrity across various complex workflows.

Creating Records

Creating a record in Servoy is straightforward with the foundset.createRecord() method. This method initiates a new, empty record that can be populated with data before being saved to the database.

Example

The createRecord() method is commonly used in forms where users input new data, such as adding a new order in an e-commerce system. The example shows a new order being created with a date, a customer ID, and an initial status of pending. In an inventory system, when new stock arrives, the warehouse staff uses a form to add the new inventory items to the database. The createRecord() method allows the system to generate a new entry for each item efficiently.

var newOrder = ordersFoundset.createRecord();
newOrder.order_date = new Date();
newOrder.customer_id = 123;
newOrder.status = 'pending';

Other methods for creating a record:

• createRecord(onTop)

• createRecord(onTop, changeSelection)

• createRecord(index)

• createRecord(index, changeSelection)

Editing Records

Direct Assignment

Once a record is retrieved or created, its fields can be modified by assigning new values directly to the record’s columns.

Example

This approach is the most straightforward for modifying existing records. It is particularly useful when making changes to a single record that is currently selected in a form, such as updating an order’s status when it has been shipped. In a customer service application, when a representative updates a customer’s details, such as changing their address or phone number, direct assignment is used to modify the customer record.

var order = ordersFoundset.getSelectedRecord();
order.status = 'shipped';
order.shipping_date = new Date();

Batch Editing Using Foundset Updater

The JSFoundsetUpdater is a powerful tool for applying the same update to multiple records simultaneously, without iterating through each record, using the getFoundSetUpdater(foundset) method.

Example

This example demonstrates how to update the status of all orders placed before January 1, 2020, to "archived." The JSFoundsetUpdater is more efficient than looping through records individually, especially for large datasets. A common scenario is end-of-year processing, where orders older than a certain date need to be archived. This approach ensures that all relevant records are updated in a single operation, reducing the risk of errors and improving performance.

var updater = databaseManager.getFoundSetUpdater(ordersFoundset);
updater.setColumn('status', 'archived');
updater.addCondition('order_date', '<', new Date('2020-01-01'));
updater.performUpdate();

Deleting Records

Single Record Deletion

Deleting a specific record from a foundset involves selecting the record and calling the deleteRecord() method. Deleting a record is as simple as selecting it and invoking the deleteRecord() method. This operation removes the record from both the foundset and the database, ensuring it no longer appears in queries or searches.

Example

When a user wishes to remove an outdated or incorrect entry, such as a customer who is no longer active, they can delete the record directly from the system.

var customer = customersFoundset.getSelectedRecord();
customersFoundset.deleteRecord(customer);

Other methods for deleting a record:

• deleteRecord(record)
• deleteRecord(index)

Deleting All Records in a Foundset

This method is useful for clearing a foundset completely, such as when resetting a dataset or removing test data. This approach is more drastic and should be used with caution, as it removes all records in the foundset using the deleteAllRecords() method. It's ideal for scenarios where bulk deletion is necessary, such as clearing out temporary data.

Example

During testing, developers might populate the system with test orders, which can be easily removed in bulk using this method once testing is complete.

ordersFoundset.deleteAllRecords();

Reverting Deletions

By setting autosave to false, any changes, including deletions, can be reviewed and reverted before being committed to the database. This feature is particularly useful in systems where changes need to be reviewed before finalization, such as in draft mode editing where users can preview changes before saving.

Example

In a financial application, transactions might be deleted in error. By disabling autosave, the deletion can be rolled back if the user realizes the mistake before committing.

databaseManager.setAutoSave(false);
ordersFoundset.deleteRecord(order);
// Review deletion
databaseManager.rollbackTransaction(); // Reverts the deletion

Auto Save: True vs. False

The Auto Save feature in Servoy controls how and when changes to data are committed to the database. By default, Auto Save is set to true, meaning any changes made to records are automatically saved as users interact with the application. This ensures that data is always current and reduces the risk of losing changes. However, in more complex scenarios, setting Auto Save to false provides developers with greater control, allowing changes to be reviewed, batched, or discarded before committing them to the database.

Auto Save: True

This mode commits changes automatically, making it suitable for applications where data consistency is required immediately after user actions.

Why Use Auto Save: True

• Real-Time Data Consistency: Auto Save set to true is ideal for applications where data needs to be immediately reflected in the database, such as in transaction-heavy environments like retail systems or real-time monitoring applications.

• Simplicity: This setting reduces the need for manual saves, ensuring that data is always up-to-date without requiring additional steps from the user.

Example

In retail point-of-sale (POS) systems, each transaction must be recorded as soon as it occurs. Autosave ensures that every sale is immediately reflected in the inventory and sales records.

Auto Save: False

This setting allows for greater control, enabling developers to decide when to commit changes. It’s ideal for complex workflows where multiple related changes need to be committed simultaneously.

Why Use Auto Save: False

• Greater Control: Setting Auto Save to False is advantageous in scenarios where multiple related changes need to be made and reviewed together before saving. This approach is commonly used in applications where data integrity is critical, such as financial systems or multi-step data entry processes.

• Error Handling: By delaying the commit of data changes, developers can implement validation, error checking, and transaction management, reducing the risk of incomplete or incorrect data being saved.

Currently delete is immediate when Auto Save is set to false.

Example

This example shows how autosave can be turned off to allow for multiple related changes to be made before committing them. It’s especially useful in scenarios where partial data should not be saved. In an order entry system, users may need to input multiple details across different tables before saving the entire transaction. Autosave can be disabled to ensure the transaction is only saved when all details are correct.

// Disable Auto Save
databaseManager.setAutoSave(false);

// Perform data operations
var product = productsFoundset.createRecord();
product.name = 'New Product';
product.price = 19.99;

// Save changes at once
databaseManager.saveData();

Retrieving Changes

The getChangedData() method provides a way to review the changes made to a record before they are saved. This method is particularly useful for logging or auditing purposes, allowing developers to track what changes have been made to a record before they are committed to the database.

Example

In a CRM system, changes to customer records might need to be reviewed or approved by a manager before being finalized. This method allows for such review processes to be implemented.

var changes = order.getChangedData();
for (var field in changes) {
    application.output('Field ' + field + ' changed to: ' + changes[field]);
}

Save Edited Records

When saving edited records in Servoy, it's important to understand that if the save operation fails, it will not throw an exception. Instead, the saveData() method will return false. It is the developer's responsibility to check whether the save was successful and handle any potential issues, including parsing exceptions from failed records.

Saving a Single Record

Saving a single record is often used when a user edits or creates a single data entry, such as updating customer information or adding a new product. This approach ensures that the specific record is saved to the database, and if the operation fails, it allows developers to handle the failure by checking for errors and providing feedback or corrective actions. Method: saveData(record)

Example

In a customer management system, a user edits a single customer's details, such as updating their phone number or address. Before committing this change, it's essential to verify that the update is successful, especially if the record must meet specific validation criteria, such as unique email addresses. If the save fails, the user can be alerted, and the failure can be logged for further analysis.

var success = databaseManager.saveData(record);
if (!success) {
    var failedRecords = databaseManager.getFailedRecords();
    for (var i = 0; i < failedRecords.length; i++) {
        var failedRecord = failedRecords[i];
        application.output('Failed to save record with ID: ' + failedRecord.getPKs() + ' Error: ' + failedRecord.exception.getMessage());
    }
}

Saving an Entire Foundset

This method is used when multiple records within a foundset need to be saved simultaneously, such as updating the status of all orders in a list or marking a batch of tasks as completed. Saving an entire foundset ensures that all records within the set are committed to the database in one operation. Developers must verify the save operation’s success to ensure data consistency across the application. Method: saveData(foundset)

Example

In an order management system, an administrator may batch update the status of several orders in a foundset (e.g., marking them all as shipped). It’s critical to ensure that all records are successfully saved to prevent inconsistencies in order processing. If any record fails to save, the operation should be logged, and corrective actions should be taken, such as retrying the save or notifying the administrator.

var success = databaseManager.saveData(foundset);
if (!success) {
    var failedRecords = databaseManager.getFailedRecords();
    for (var i = 0; i < failedRecords.length; i++) {
        var failedRecord = failedRecords[i];
        application.output('Failed to save record in foundset with ID: ' + failedRecord.getPKs() + ' Error: ' + failedRecord.exception.getMessage());
    }
}

Saving All Edited Records in the Session

Saving all edited records in the current session is useful in scenarios where multiple foundsets and records have been modified, such as during a data entry session or after making extensive changes across the application. This global save operation commits all pending changes to the database. Developers should check the success of this operation to ensure that all modifications are correctly saved, providing an opportunity to handle any errors across the session’s changes. Method: saveData()

Example

In a data entry application where multiple records across different foundsets are edited during a session, it's crucial to save all changes before the session ends. This ensures data consistency across the application. If the global save operation fails, the developer can handle the situation by identifying which records failed and implementing a rollback or providing the user with an opportunity to correct the errors before retrying the save.

var success = databaseManager.saveData();
if (!success) {
    var failedRecords = databaseManager.getFailedRecords();
    for (var i = 0; i < failedRecords.length; i++) {
        var failedRecord = failedRecords[i];
        application.output('Failed to save record with ID: ' + failedRecord.getPKs() + ' Error: ' + failedRecord.exception.getMessage());
    }
}

Handling Failed Records

When saving data in Servoy, it’s essential to manage failed records to maintain data integrity.

Get Failed Records

The databaseManager.getFailedRecords() method retrieves an array of records that failed to save during the last saveData() operation. This allows developers to identify and handle problematic records, ensuring data integrity by addressing issues such as validation errors or database constraints that prevented the save.

Example

In an e-commerce application, after bulk updating product information, some records fail to save due to validation errors, such as duplicate SKUs or missing mandatory fields.

var failedRecords = databaseManager.getFailedRecords();
for (var i = 0; i < failedRecords.length; i++) {
    var failedRecord = failedRecords[i];
    application.output('Failed to save record with ID: ' + failedRecord.getPKs());
}

Processing Exceptions in Failed Records

Each record that fails to save may have an associated exception detailing why the save failed. The record.exception property allows developers to access these details, enabling them to log specific error messages, notify users of issues, and take corrective actions. This ensures that errors are not only identified but also addressed in a meaningful way, improving application reliability.

Example

In a financial application, when processing transactions, some records fail to save due to database constraints, such as violating foreign key constraints or exceeding the maximum allowed value for a numeric field.

var failedRecords = databaseManager.getFailedRecords();
for (var i = 0; i < failedRecords.length; i++) {
    var failedRecord = failedRecords[i];
    if (failedRecord.exception) {
        application.output('Failed to save record with ID: ' + failedRecord.getPKs() + ' Error: ' + failedRecord.exception.getMessage());
    } else {
        application.output('Failed to save record with ID: ' + failedRecord.getPKs() + ' with no specific error provided.');
    }
}

Using Transactions

Transactions allow you to bundle multiple database operations together. If any operation fails, the entire transaction can be rolled back, ensuring data consistency.

Example

Considering a financial application where multiple records representing transactions are being processed together, such as transferring funds between accounts. It's critical that either all transactions are successfully processed and committed, or none at all, to maintain financial accuracy and prevent discrepancies. This example represents a typical use case in a financial application where funds are transferred between two accounts. The process involves two critical operations:

• Debiting $500 from the source account.

• Crediting $500 to the destination account. Both operations must succeed together to maintain the integrity of the transaction. If any part of the operation fails (e.g., the debit succeeds but the credit fails), the entire transaction is rolled back to prevent inconsistencies, such as money being debited from one account without being credited to another. Detailed error messages are logged if either the save or commit operation fails, and the transaction is rolled back to maintain financial accuracy.

// Start the transaction for processing financial transfers
databaseManager.startTransaction();

try {
    // Create and save the first transaction (debit from source account)
    var debitTransaction = sourceAccountFoundset.getRecord(sourceAccountFoundset.newRecord());
    debitTransaction.amount = -500; // Debit $500 from the source account
    debitTransaction.description = 'Transfer to Account B';

    // Create and save the second transaction (credit to destination account)
    var creditTransaction = destinationAccountFoundset.getRecord(destinationAccountFoundset.newRecord());
    creditTransaction.amount = 500; // Credit $500 to the destination account
    creditTransaction.description = 'Transfer from Account A';

    // Attempt to save both transactions
    if (!databaseManager.saveData()) {
        throw new Error('Failed to save transaction records.');
    }

    // Commit the transaction to ensure both operations are applied
    if (!databaseManager.commitTransaction()) {
        throw new Error('Failed to commit database transaction.');
    }

    // If successful, log the completion of the transfer
    application.output('Transaction committed successfully.');

} catch (e) {
    // Log error and rollback transaction if something fails
    application.output(utils.stringFormat('Transaction failed: "%1$s". Rolling back database transaction.', [e.message]), LOGGINGLEVEL.ERROR);
    databaseManager.rollbackTransaction();
}

Explanation:

• Starting the Transaction: databaseManager.startTransaction() initiates a new transaction. All subsequent operations will be part of this transaction.
• Saving Data: databaseManager.saveData() is called to save the changes. If the save fails, an error is thrown using throw new Error('Failed to save records.');.

• Committing the Transaction: If the data is saved successfully, databaseManager.commitTransaction() is called to commit the transaction. If this fails, another error is thrown using throw new Error('Failed to commit database transaction.');.
• Error Handling:

  • The catch block catches any errors thrown during the save or commit operations.

  • The error message is formatted and output using application.output() with a logging level of LOGGINGLEVEL.ERROR.

  • The transaction is then rolled back using databaseManager.rollbackTransaction(), ensuring that no partial changes are committed if something goes wrong.

Reverting Changes

Revert Single Record

Use the revertChanges() method to undo modifications to a specific record before they are saved. This method is useful for undoing accidental changes to a record. It can be used in scenarios where the user edits a record but decides not to save the changes.

Example

A user might change their mind about editing a customer’s details in a CRM system. Using revertChanges() allows them to undo the edits before saving, maintaining the original data.

record.revertChanges();

Revert Foundset Changes

This method reverts all changes across a foundset, which can be particularly useful when batch changes have been made but need to be discarded. Useful in scenarios where batch edits need to be reviewed and potentially discarded. It allows developers to rollback all changes within a specific foundset, using the revertEditedRecords(foundset) method.

Example

After bulk updating several orders in an e-commerce system, the administrator might realize an error and decide to roll back all changes. This method facilitates that process.

databaseManager.revertEditedRecords(ordersFoundset);

Revert Related Foundset Changes

In scenarios where you need to revert all changes made to records within a related foundset, such as in a master-detail relationship, you can use the revertEditedRecords(foundset) method on the related foundset. This ensures that all unsaved modifications to the related records are discarded.

Example

databaseManager.revertEditedRecords(customer.customers_to_orders);

Revert All Changes

To globally revert all changes in the current session, use databaseManager.revertEditedRecords(). This global undo mechanism is particularly powerful when you want to discard all changes made during a session, useful in complex applications where multiple changes across various foundsets might need to be undone.

Example

In a data entry application, after a session of input, a user might choose to discard all changes if they realize there were fundamental errors in the data entry process.

databaseManager.revertEditedRecords();

Related Articles

Validation

Before saving data, validation ensures that the data conforms to required formats, ranges, and rules, preventing invalid data from being saved. You can find here more information about validation.

Auto-enter

Auto-enter fields are automatically populated during record creation or editing, such as timestamps, user IDs, or sequential numbers. Auto-enter values reduce manual input errors and ensure consistency across records. They are especially useful for fields that require a unique or calculated value at the time of creation. You can find here more information about auto-enter.

Table Events

Table events are triggers that execute specific scripts when data in a table is inserted, updated, or deleted. They can be used for logging, cascading updates, or other automated tasks. You can find here more information about table events.