Automatically duplicate record to other tables

[88tc88]

Hi all,

Currently I am running into a challenge which I will try to explain as concise and clear as possible.

In nuBuilder I have an edit-form with data of a specfic Delivery-order. In the database I store this data in the table 'Order' and all the items of the Delivery, which are in a subform on the edit-form, in the 'Orderitem' table. Since the delivery can be invoiced in multiple parts, I added a button to the edit-form. When the button is clicked, it should do the following:

Show/create a new edit-form which is the same as the delivery edit-form and has all the same data, but all this data should be stored in the 'Invoice' and 'Invoiceitem' tables. In this way the user would be able to make custom invoices that are based on the deliveryorder. In other words, an invoice should be created which can be further edited by the user.

I can imagine my explanation is a bit vague, because it is quite a specific question. To make things a little more clear I added an SQL export of a simple and anonymized version of the database.

Thanks a lot in advance guys!

[kev1n]

Hi,

If we assume that an order must be saved before an invoice can be created, then a PHP procedure could be implemented to first insert a record into the factuur table and then add the corresponding items into the factuuritem table.
The "Create Invoice (factuur)" button would trigger a PHP procedure (e.g. with code create_invoice):

Code: Select all

nuRunPHPHidden('create_invoice');

Here's an example of what the create_invoice procedure might look like

Code: Select all

// Create record in factuur:
$insertSql = " 
    INSERT INTO `factuur` (
        `factuur_id`,
        `factuur_order_id`,
        `factuur_nummer`,
        `factuur_opmerking`,
        `factuur_datum`
    ) VALUES (
        :factuur_id,
        :factuur_order_id,
        :factuur_nummer,
        :factuur_opmerking,
        :factuur_datum
    )
";

$orderId = nuID(); 

$data = [
    "factuur_id" => $orderId, 
    "factuur_order_id" => 12345, // or some incrementing number etc.
    "factuur_nummer" => 'INV-2025-001', 
    "factuur_opmerking" => '....',
    "factuur_datum" => date("Y-m-d")
];

nuRunQuery($insertSql, $data, true);

// More code here ....
// Loop through orders_items and add create the order items in factuuritem....


// Finally open the form "COMPANY_Tabel_Factuur"
$js = "nuForm('67f53db73d013a8', '$orderId', '', '', '2');";
nuJavaScriptCallback($js);