Master/Detail Add with uuid as primary keys

This public forum is for user-to-user discussions of PHPMaker. Note that this is not support forum.
Post Reply
dquinlan
User
Posts: 27

Master/Detail Add with uuid as primary keys

Post by dquinlan »

I have master/detail tables for invoices and invoice items.

I have tried to implement Master/Detail Add but it is failing with Invalid Key Value, I think in the detail record insert.

I have seen some posts referring to not using autoincrement for the Master key value can be an issue. Does this mean you have to use an autoincrement for the Master table primary key or can it be accomplished. I have tried creating the key value in the Master table Row_Inserting event, saving it to a Session variable and using it in Detail Row_Inserting but I get the same error?

Can you confirm that it is mandatory to use an auto increment key in the Master table?


mobhar
User
Posts: 11660

Post by mobhar »

You may post your tables schema (CREATE TABLE ...) including some records inside (INSERT INTO ...), so others could help.


dquinlan
User
Posts: 27

Post by dquinlan »

Here are the schemas, global function and events

DROP TABLE IF EXISTS `invoices`;
CREATE TABLE `invoices` (
  `uuid` varchar(36) COLLATE utf8mb3_unicode_ci NOT NULL,
  `client_id` varchar(36) COLLATE utf8mb3_unicode_ci NOT NULL,
  `client_name` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `invoice_no` varchar(255) COLLATE utf8mb3_unicode_ci NOT NULL,
  `invoice_date` date NOT NULL,
  `due_date` date NOT NULL,
  `status` varchar(36) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `invoice_total` double(15,2) DEFAULT 0.00,
  `payment_total` double(15,2) DEFAULT 0.00,
  `terms` text COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `currency` varchar(255) COLLATE utf8mb3_unicode_ci NOT NULL,
  `recurring` tinyint(1) NOT NULL DEFAULT 0,
  `email_id` varchar(36) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `pdf_saved` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `increment_num` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `invoice_items`
--

DROP TABLE IF EXISTS `invoice_items`;
CREATE TABLE `invoice_items` (
  `uuid` varchar(36) COLLATE utf8mb3_unicode_ci NOT NULL,
  `invoice_id` varchar(36) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `invoice_no` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `item_name` varchar(255) COLLATE utf8mb3_unicode_ci NOT NULL,
  `item_description` text COLLATE utf8mb3_unicode_ci NOT NULL,
  `quantity` double(9,3) NOT NULL,
  `price` double(15,2) NOT NULL,
  `tax_id` varchar(36) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `tax_rate` double(4,2) DEFAULT 0.00,
  `item_order` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

--
-- Indexes for table `invoices`
--
ALTER TABLE `invoices`
  ADD PRIMARY KEY (`uuid`),
  ADD UNIQUE KEY `invoices_number_unique` (`invoice_no`),
  ADD UNIQUE KEY `increment_num` (`increment_num`),
  ADD KEY `invoices_client_id_foreign` (`client_id`);

--
-- Indexes for table `invoice_items`
--
ALTER TABLE `invoice_items`
  ADD PRIMARY KEY (`uuid`),
  ADD KEY `invoice_items_invoice_id_foreign` (`invoice_id`),
  ADD KEY `invoice_items_tax_id_foreign` (`tax_id`),
  ADD KEY `invoice_items_invoice_no_foreign` (`invoice_no`);

The Master/Detail relationship is

invoices`.`uuid` = `invoice_items`.`invoice_id

I have a function to create uuid

function getGUID(){
        mt_srand((double)microtime()*10000); 
        $charid = strtoupper(md5(uniqid(rand(), true)));
        $hyphen = "-";
        $uuid =
        	 substr($charid, 0, 8).$hyphen
            .substr($charid, 8, 4).$hyphen
            .substr($charid,12, 4).$hyphen
            .substr($charid,16, 4).$hyphen
            .substr($charid,20,12);
       return $uuid;  
}

Invoices Row_Inserting uses GetGUID()

    // Row Inserting event
    public function rowInserting($rsold, &$rsnew)
    {
      	$client_name = $rsnew["client_name"];
    	$client_id_sql = "SELECT uuid FROM clients WHERE name ='" . AdjustSql($client_name) . "'";
       	$client_id = ExecuteScalar($client_id_sql); 
    	$rsnew["client_id"] = $client_id;
    	$_SESSION["uuid"] = GetGUID();
    	$rsnew["uuid"] = $_SESSION["uuid"];	
    	Log("GUID ". $_SESSION["uuid"]);		
        return true;
    }

Invoice_Items Row_Inserting sets invoice_id = $_SESSION["uuid"]

    // Row Inserting event
    public function rowInserting($rsold, &$rsnew)
    {
    	$rsnew["invoice_id"] = $_SESSION["uuid"];
    	Log("Item ID ". $_SESSION["uuid"]);	
    	//
        return true;
    }

dquinlan
User
Posts: 27

Post by dquinlan »

Is the Master Row_Inserting event executed before the Detail Row_Inserting event?


arbei
User
Posts: 9284

Post by arbei »

dquinlan wrote:

Is the Master Row_Inserting event executed before the Detail Row_Inserting event?

Yes.

You logged the new uuid. What did you see in the log file? (You should see the saved uuid logged by the Row_Inserting event of the detail table.)


dquinlan
User
Posts: 27

Post by dquinlan »

Yes the Log displays the id.

[2023-03-15T06:30:36.811881+00:00] log.DEBUG: Item ID A0D078AB-F73F-995B-0D51-29728DEA2F96 [] []
[2023-03-15T06:30:36.811926+00:00] log.DEBUG: Item invoice_no Proven 212 [] []

It fails immediately after the Row_Inserting of the Detail table, before Row_Inserted. Have checked the other key fields; invoice_no, tax_id They are all generated

Previous posts seem to infer Master Detail Add will only work with auto increment id's?


mobhar
User
Posts: 11660

Post by mobhar »

Since uuid field in invoices table is a primary key, then when it is not auto-increment field in database, then in Master/Detail-Add form the field still empty. So, you need to assign the value into that field by using Row_Rendered server event, for example:

if (CurrentPageID() == "add") {
    $_SESSION["uuid"] = getGUID();
    $this->uuid->EditValue = $_SESSION["uuid"];
}

Therefore, you don't need the code in Row_Inserting server event to assign the GUID to that field in that invoices table.

In addition, you don't need the code in Row_Inserting server event that belongs to invoice_items table.


arbei
User
Posts: 9284

Post by arbei »

In Row_Inserting server event, after:

$rsnew["uuid"] = $_SESSION["uuid"];

you may add:

$this->uuid->setDbValue($_SESSION["uuid"]);


dquinlan
User
Posts: 27

Post by dquinlan »

Thanks for the help here, I have been able to resolve it


Post Reply