Chained Select Using Dependent

vincmeister

Hi Allan,

I want to populate select list based on other field like this case. In my case, i want to populate item (name) based on purchase order (PO) number. The list on item, are limited as the item on a PO

My field:

fields: [ 
    {
        label: "PO Number:",
        name: "erp_receiving_detail.po_number"
    },  {
        label: "Item:",
        name: "erp_receiving_detail.item_code",
        type: "select2"
    }
]

I'm using this dependency

editorReceivingDetail.dependent( 'erp_receiving_detail.po_number', function ( val, data, callback ) {
    po_number = tableReceivingHead.row( { selected: true } ).data().erp_receiving_head.po_number;
    $.ajax( {
        url: '../usersc/function/update_item.php?po_number=' + po_number,
        dataType: 'json',
        success: function ( json ) {
            callback( json );
        }
        } );
    } );

My json output:

{"options": { "erp_receiving_detail.item_code" [{"label":"Art Paper 79 x 109 cm 120 Gram","value":"1"},{"label":"Ivory 79 x 109 cm 250 Gram","value":"10"}]}}

but the erp_receiving_detail.item_code not limited as the json result. It shows all the item from item master table.

Please advise, thank you

Danny

allan

Hi Danny,

Could you try changing from a select2 to a plain select field and let me know if that resolves the issue? If so, then its an issue with the select2 plug-in.

Allan

vincmeister

Hi Allan,

Same result after changing to select.
This is my original option for the item_code, left join erp_items table

Field::inst( 'erp_receiving_detail.item_code' )
    ->options( Options::inst()
        ->table( 'erp_items' )
        ->value( 'item_code' )
        ->label( 'item_name' )
        ->where( function ($q) {
                $q
            ->where( 'is_active', '1', '=' )
            ->where( 'item_group_code', '1', '=' );
        })
        ),
        Field::inst( 'erp_items.item_name' ),

I can simply look up the option from erp_po_detail, it works, . But if I join to erp_po_detail i need to look up the item_name (erp_items table) for the label. Is it possible?
This is my php

Field::inst( 'erp_receiving_detail.item_code' )
    ->options( Options::inst()
        ->table( 'erp_po_detail' )
        ->value( 'item_code' )
        ->label( 'item_code' )
        ->where( function ($q) {
            $q->where( 'erp_po_detail.po_number', $_POST['po_number'], '=' );
    })
),

Please help
Thanks
Danny

allan

You want to do a join in the options list? So the value and the label are coming from different tables? Is that correct or have I misunderstood?

If that is correct, then at this time there is no join option / method for the Options class. Instead you would need to provide a custom function that would query the database as required.

Allan

vincmeister

Hi Allan,

You want to do a join in the options list? So the value and the label are coming from different tables? Is that correct or have I misunderstood?

Yes

My tables are:
1. erp_items: item_code, item_name
2. erp_po_detail: po_number, item_code
3. erp_receiving_detail: receiving_id, item_code, po_number

I want to show erp_receiving_detail.item_code as select
the option lists are come from erp_po_detail.item_code, which linked by same po_number. But i want to show it as item_name, which come from erp_items.item_name.

According to the example case example on my 1st post, i'm using this query

    $po_number = $_GET['po_number'];
        $query = $db->query("
        SELECT 
            erp_items.item_name as label,  
            erp_po_detail.item_code as value
        FROM erp_po_detail 
        LEFT JOIN erp_items ON erp_items.item_code = erp_po_detail.item_code 
        WHERE po_number = $po_number
    ");
    $count = $query->count();
    if ($count > 0){
        $results = $query->results();
        $json = '{"options": { "erp_receiving_detail.item_code" ' . json_encode($results) . '}}';
        print_r($json);
    }

and the output is as expected:

{"options": { "erp_receiving_detail.item_code" [{"label":"Art Paper 79 x 109 cm 120 Gram","value":"1"},{"label":"Ivory 79 x 109 cm 250 Gram","value":"10"}]}}

I need to transfer the json result to select or select2 list options.
Is that possible?

Thanks
Danny

allan

That looks good to me.

The documentation here shows how you can provide a custom option to get the list of options. Just have it return the array of options.

Allan

vincmeister

Hello Allan,

I put this on my options

->options(function(){
    $db = DB::getInstance();
    $po_number = $_POST['po_number'];
    $query = $db->query("
    SELECT 
        erp_items.item_name as label,  
        erp_po_detail.item_code as value
    FROM erp_po_detail 
    LEFT JOIN erp_items ON erp_items.item_code = erp_po_detail.item_code 
    WHERE po_number = $po_number
    ");
    $count = $query->count();
    if ($count > 0){
        $results = $query->results();
        $json = json_encode($results);
        //print_r($json);
    }
}),

I got this on my result
erp_receiving_detail.item_code:null

but my print_r is working with this output
[{"label":"Art Paper 79 x 109 cm 120 Gram","value":"1"},{"label":"Ivory 79 x 109 cm 250 Gram","value":"10"}]

Please help, thank you
Danny

allan

There is no return statement. Just add return $results;,

Allan

vincmeister

Many thanks Allan, you're the best !