how get value of lookup field on child table from parent table?

[xbox2007]

hello
i have project

have four tables :
Order >> to make order
Suppliers >> Supplier company -- each suppiler have own item
Items >> item information , price and Supplier Name
Orderitems >> to add items on order

Order (parent table) >>> Orderitems (child table)

On order table there are :
Supplier >> look up from Suppliers

ON Orderitems there are :
OrderNo >> lookup field from Order
Supplier >> look up from Suppliers
Item >> look up from Suppliers

Screenshot 2025-03-14 232801.png (175.13 KiB) Viewed 11981 times

when add item to order :

Suppliers information send to Orderitems (child table) so items drop down will show only item for Current supplier
i dont want each time add new item to order select supplier from dorpdown then select items

Screenshot 2025-03-14 233637.png (78.21 KiB) Viewed 11981 times

Screenshot 2025-03-14 233657.png (59.66 KiB) Viewed 11981 times

Screenshot 2025-03-14 233713.png (54.19 KiB) Viewed 11981 times

Screenshot 2025-03-14 234724.png (89.22 KiB) Viewed 11981 times

thanks a lot

[a.gneady]

Here’s a possible approach to retrieving the value of a lookup field from the parent table, then using it in the child table record: In your child table hook (e.g. hooks/orderitems.php), inside orderitems_before_insert and orderitems_before_update, retrieve the parent record's ID and then look up the needed parent field. For example:

Code: Select all

function orderitems_before_insert(&$data, $memberInfo, &$args){

   // 1. Get the parent “OrderNo” from the child record
   $parentID = makeSafe($data['OrderNo']);  

   // 2. Use that ID to retrieve the “Supplier” from the parent “Order” table
   if($parentID){
      $parentSupplier = sqlValue("SELECT `Supplier` FROM `Order` WHERE `id`='{$parentID}'");
      
      // 3. If we successfully retrieved the parent supplier, set it in the child record
      if(!empty($parentSupplier)){
         $data['Supplier'] = $parentSupplier;
      }
   }

   return TRUE;
}

In this example:

• orderitems is the child table.
• OrderNo is the lookup to the parent “Order” table.
• Supplier in the parent “Order” table is the field you need to copy or use in the child table.
• Supplier in the child table is another lookup that you want to automatically set.

With that code in place, you can automatically retrieve the parent’s lookup value and use it in the child record without manually re-selecting the supplier each time. You could add a hint in the supplier lookup description in order items that the user doesn't need to select it as it would be set automatically.

[xbox2007]

hello

i already add code but its not working , its only work after Make save change , i can still see items for all supplier

still can see items for all supplier

Screenshot 2025-03-17 212512.png (55.06 KiB) Viewed 11865 times

only after save i can see item for selected supplyer

Screenshot 2025-03-17 212724.png (52.85 KiB) Viewed 11865 times

thanks a lot