Chained Select Using Dependent

Chained Select Using Dependent

vincmeistervincmeister Posts: 136Questions: 36Answers: 4
edited June 2017 in Editor

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

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • vincmeistervincmeister Posts: 136Questions: 36Answers: 4

    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

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • vincmeistervincmeister Posts: 136Questions: 36Answers: 4

    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

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin

    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

  • vincmeistervincmeister Posts: 136Questions: 36Answers: 4

    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

  • allanallan Posts: 61,439Questions: 1Answers: 10,053 Site admin
    Answer ✓

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

    Allan

  • vincmeistervincmeister Posts: 136Questions: 36Answers: 4
    edited June 2017

    Many thanks Allan, you're the best !

This discussion has been closed.