do as database ... end

The Ninox 3.10 release introduces a transformative feature to enhance your experience: the do as database ... end transaction

 do as database ... end is available for Private Cloud and On-Premises.

Key features at a glance

• Simplified database navigation, boosting data access speed and productivity.
• Avoids complex API workarounds.
• Seamless integration with existing database relationships.
• Consistent read and write client-side operations across both web and native apps.
• Intuitive functionality in line with other Ninox transactions like:
  • do as transaction ... end
  • do as server ... end
  • do as deferred ... end

The do as database ... end transaction greatly simplifies the task of navigating through multiple databases, eliminating the need for shifting between databases or applying advanced workarounds for data access.

The core functionality of do as database ... end is to allow you to pull data directly from the current database you're operating in without accessing a separate database. Not only is this transaction method more streamlined than the previous API call method, but it also ensures data accuracy, given certain conditions:

The transaction can be applied only when the databases in context have a pre-existing relationship.

For accurate data retrieval, the database name used must be exact.

Database names with special characters should be enclosed in single quotes.

Set up database relationship

To exemplify, consider 2 databases: Offers and invoices and Procurement.

1. Open the Offers and invoices database.
2. Open the Invoices table.
3. Click the wrench icon in the top-right corner to enable edit mode.
4. Click the drop-down arrow next to the gear icon on the left and select Edit fields from the dropdown to access the table settings.
5. In the table settings, select the All databases in current workspace checkbox.
6. Also in the table settings, click Relationship fields. Under the Procurement database, drag-and-drop the Orders table to Fields.
7. Click Save to apply changes and close the table settings.

Read transaction example

1. In the Offers and invoices database, create a new formula field and name it Order date.
   1. In the table settings, drag-and-drop Formula from Data fields to Fields.
   2. Click Save to apply changes and close the table settings.
2. Open a record in the Invoices table, switch to the Invoices tab, hover above the Order date field until you see the wrench icon.
3. Click the wrench icon to access the field settings.
4. In the Order date field, click the Formula field to open the formula editor.
5. In the formula editor, enter the following script:

   do as database Procurement
       last ((select Orders).'Order date')
   end

6. Click Save to save the script and close the formula editor.
7. In the Formula pop-up, click OK to apply changes and close the pop-up.
8. Click Save changes.
9. Verify that Order date displays the latest order date from the Orders table in the Procurement database.

Write transaction example

Before beginning, ensure you've established a relationship between Offers and invoices and Procurement.

1. In the Offers and invoices database, create a new layout field (button) and name it New invoice.
   1. Click Edit fields and drag-and-drop Formula from Data fields to Fields.
   2. Click Save to apply changes and close the table settings.
2. Open a record in the Invoices table, switch to the Invoices tab, hover above the New invoice button until you see the wrench icon.
3. Click the wrench icon to access the field settings.
4. In the New invoice field, click the On click field to open the formula editor.
5. In the formula editor, enter the following script:

   do as database Procurement
       let newInvoice := (create Orders);
       newInvoice.('Order date' := today());
       popupRecord(newInvoice)
   end

6. Click Save to save the script and close the formula editor.
7. In the Button pop-up, click OK to apply changes and close the pop-up.
8. Click Save changes.
9. When you click the New invoice button in the Invoices table in the Offers and invoices database, a new record from Orders with today’s date should pop up.
10. Verify that the Orders table in the Procurement database contains a new record with today’s date.