Subform with multiple ID's or editable browseform Topic is solved

[88tc88]

Hi everyone,

I have been using nuBuilder Forte (4.5) for a while now and I am really enjoying the program and the progress I am making. However, at this point I am struggling with something and I am wondering if some of you here could help me out, thanks a lot in advance!

Current situation
I have a table called 'order' that contains a column 'order_amountpaid', which stores the amount actually paid for each order.

Currently, to update this field, I have to:
1) Open the Browse & Edit form for each individual record.
2) Manually enter the amount in order_amountpaid.
3) Save the changes.
However, I often need to update multiple orders at once, as payments for many appointments are entered retrospectively. Updating each record one by one is inefficient and time consuming.

Question
Is there a way in NuBuilder to update multiple records within the same form, similar to a subform or inline editing grid that displays the same data as the Browse form but allows direct editing of multiple values at once?

I'm essentially looking for a way to:
1) View multiple 'orders' in a list format.
2) Edit the 'order_amountpaid' value for these multiple orders without opening each order individually.
3) Save all changes in one action.
Would this require custom JavaScript, SQL updates, or a different form setup in NuBuilder? Any guidance or best practices would be greatly appreciated!

Thanks in advance again!

[steven]

Hi 88tc88,

Are you able to share a copy of your database with us - zipped (without any sensative information)?


Steven

[steven]

88c88,

I asked your question to Grok (grok.com) ...

I have been using nuBuilder Forte (4.5) for a while now and I am really enjoying the program and the progress I am making. However, at this point I am struggling with something and I am wondering if some of you here could help me out, thanks a lot in advance!

Current situation
I have a table called 'order' that contains a column 'order_amountpaid', which stores the amount actually paid for each order.

Currently, to update this field, I have to:
1) Open the Browse & Edit form for each individual record.
2) Manually enter the amount in afspraak_werkelijkbetaald.
3) Save the changes.
However, I often need to update multiple orders at once, as payments for many appointments are entered retrospectively. Updating each record one by one is inefficient and time consuming.

Question
Is there a way in NuBuilder to update multiple records within the same form, similar to a subform or inline editing grid that displays the same data as the Browse form but allows direct editing of multiple values at once?

I'm essentially looking for a way to:
1) View multiple 'orders' in a list format.
2) Edit the 'order_amountpaid' value for these multiple orders without opening each order individually.
3) Save all changes in one action.
Would this require custom JavaScript, SQL updates, or a different form setup in NuBuilder? Any guidance or best practices would be greatly appreciated!

Groks answer...

20 web pages
I’m glad to hear you’re enjoying nuBuilder Forte 4.5! Your question about updating multiple records efficiently is a common need, and nuBuilder offers some flexibility to achieve this. While it doesn’t natively provide a fully editable grid like some other platforms out of the box, you can accomplish your goal with a combination of a Subform object, some custom JavaScript, and a thoughtful form setup. Here’s a step-by-step approach to help you update multiple order_amountpaid values in a list format and save them all at once:

Solution Overview
You can create an Edit Form with a Subform object that mimics a grid-like interface. The Subform will display multiple order records, allowing you to edit the order_amountpaid field inline for each row. A single "Save" action on the parent form will update all modified records in the database. This approach avoids opening individual records and leverages nuBuilder’s Subform functionality, with some custom JavaScript to streamline the process.

Step-by-Step Guidance
1. Create or Modify Your Table Structure
Ensure your order table has a primary key (e.g., order_id) and the order_amountpaid column. It seems like you already have this set up, so you’re good here.
2. Set Up a Parent Edit Form
Create a new Edit Form (let’s call it MassUpdateOrders) in nuBuilder.
This form doesn’t need to be tied to a specific table (you can leave the "Table" field blank in the Form properties), as it will serve as a container for the Subform.
Give it a meaningful description, like "Mass Update Order Payments."
3. Add a Subform Object to the Parent Form
In the MassUpdateOrders Edit Form, add a Subform object.
Subform Form: Create a new Subform Form (e.g., OrderPaymentSubform) tied to the order table.
Foreign Key: Since this Subform doesn’t need to relate to a parent record in the traditional sense (e.g., one-to-many), you can leave the Foreign Key unset or use a dummy field. For simplicity, we’ll load all records dynamically.
Display Mode: Set the Subform to "Grid" mode (in the Subform object properties) so that each record appears as a row with editable fields side by side.
4. Configure the Subform Form
In the OrderPaymentSubform Form:
Add an Input object for order_id (read-only, to identify each row).
Add an Input object for order_amountpaid (editable, this is where you’ll enter payment amounts).
Optionally, add other fields (e.g., order_date or order_number) as read-only for context, so users know which order they’re editing.
Ensure the Subform’s primary key is set to order_id in the Form properties.
5. Load Multiple Records into the Subform
Since you want to edit multiple orders at once, you’ll need to populate the Subform with the relevant records dynamically. Add custom PHP code in the Before Edit (BE) event of the MassUpdateOrders parent form to fetch the desired order records:
php

$sql = "SELECT order_id FROM order WHERE order_amountpaid IS NULL OR order_amountpaid = 0"; // Adjust this condition as needed
$result = nuRunQuery($sql);

$rows = [];
while ($row = db_fetch_array($result)) {
$rows[] = [$row['order_id'], '', '']; // [primary_key, order_amountpaid, nuDelete flag]
}

$subformData = new stdClass();
$subformData->id = 'ordersubform'; // Match the Subform object ID (e.g., 'ordersubform')
$subformData->foreign_key = ''; // No foreign key needed
$subformData->primary_key = 'order_id';
$subformData->table = 'order';
$subformData->action = 'save';
$subformData->rows = $rows;
$subformData->fields = ['ID', 'order_amountpaid', 'nuDelete'];

$GLOBALS['nuFORMdata'] = [$subformData]; // Pass the data to the Subform
Replace 'ordersubform' with the actual ID of your Subform object (check the "Object ID" in the Subform properties).
Modify the SQL query to filter the orders you want to edit (e.g., unpaid orders).
6. Enable Inline Editing
The Subform will now display a grid of orders with editable order_amountpaid fields. Users can type new values directly into each row.
7. Save All Changes at Once
When you click "Save" on the MassUpdateOrders parent form, nuBuilder will automatically process the Subform data and update the order table for all modified rows. No extra SQL is needed here unless you want custom validation or additional updates.
Optionally, add custom JavaScript in the Before Save (BS) event of the parent form to validate entries (e.g., ensure order_amountpaid is a valid number):
javascript

function nuBeforeSave() {
var subform = nuSubformArray('ordersubform', false); // Get all rows except deleted ones
for (var i = 0; i < subform.length; i++) {
var amount = $('#' + subform + 'order_amountpaid').val();
if (amount && isNaN(amount)) {
nuMessage(['Error: Invalid amount entered in row ' + (i + 1)]);
return false; // Prevent saving
}
}
return true; // Proceed with save
}
8. Enhance Usability with Custom JavaScript (Optional)
To make the grid more user-friendly, you could add JavaScript in the parent form’s Custom Code > JavaScript section:
Auto-focus the first editable field when the form loads:
javascript

$('#ordersubform0000order_amountpaid').focus();
Highlight changed rows:
javascript

$('input[id$="order_amountpaid"]').on('change', function() {
$(this).closest('.nuSubformLine').css('background-color', '#ffffcc');
});
9. Test and Refine
Open the MassUpdateOrders form, verify that it loads your orders, edit some order_amountpaid values, and save. Check the order table to ensure the updates are applied correctly.
Adjust the SQL in Step 5 to limit the number of records or add search filters if needed.
Does This Require Custom Code or a Different Form Setup?
Custom JavaScript: Yes, for validation or UI enhancements (e.g., Step 7 and 8), but it’s optional for basic functionality.
SQL Updates: Not directly—nuBuilder handles the updates via the Subform save mechanism. The custom PHP in Step 5 is just for loading records.
Different Form Setup: Yes, you’ll use a parent Edit Form with a Subform object instead of relying solely on a Browse & Edit form.
Best Practices
Limit Record Count: Loading too many records into the Subform could slow down the form. Add pagination or filters (e.g., by date range) to keep it manageable.
Validation: Always validate inputs (e.g., numeric values for order_amountpaid) to prevent bad data.
User Feedback: Use nuMessage() to confirm successful saves or highlight errors.
Backup: Test this setup on a copy of your database first, as bulk updates can be tricky to undo.

Amazing!

(I'm not sure how accurate it is)

Steven

[kev1n]

Hi,

I believe this can be solved with minimal effort using a subform:

A subform relies on a main form to save data records. One potential solution is to create a dummy table containing a single record. Each entry in the invoice table can then reference the ID of this dummy record.

Additionally, the subform can use a WHERE clause to filter and display only open invoices, for example. This approach allows you to efficiently manage and save multiple invoices at the same time.

[88tc88]

Thanks kev1n and steven, appreciate your help! I added a blank column to my order table and used that as the Foreign key, this does the job as well and is similair to the solution kev1n proposed. Thanks!

[miasoft]

I'm essentially looking for a way to:
1) View multiple 'orders' in a list format.
2) Edit the 'order_amountpaid' value for these multiple orders without opening each order individually.
3) Save all changes in one action.
Would this require custom JavaScript, SQL updates, or a different form setup in NuBuilder? Any guidance or best practices would be greatly appreciated!

Besides NuBuilder, I use Clarion for desktops. There, this mode is called Edit-In-Place, that is, edit fields directly in Browse(or List). It is convenient for simple editing.