Creating Material Queries with custom UD fields

This guide explains how to add a custom field to the My Material Queue screen and order the list by it's value.

Bin sequencing is used as an example. Using sequencing ensures materials are picked in the bin sequence order, reducing handling time and improving workflow efficiency. This is especially useful when multiple bins hold the same material or when prioritising specific locations or pick lanes.

The difficulty is that out of the box, the bin sequence value is not provided on MtlQueue records by Kinetic. This would require Kinetic to implement a field on the MtlQueue table for the bin sequence.

Prerequisite

• Knowledge of how to create UD Columns and BPM Data Directives - this guide will not explain the specifics of those processes

• Bin sequence values will need to be defined on Warehouse Bin Maintenance for warehouse bin records which you want to sort by:

Steps

1. Create a UD Column BinSeq on the MtlQueue table with Data Type of Integer

2. If you would like the Bin Sequence to show on the Material Queue list, also create the 4x required UD Fields on the MtlQueue table called for a custom field. For full details on how to do this: Custom Fields Configuration

   1. Emw_col1_column_c: String type with initial value of "BinSeq_c"

   2. Emw_col1_label_c: String type with initial value of "Bin Sequence"

   3. Emw_col1_type_c: String type with initial value of "List" so it can appear on the Material Queue

   4. Emw_col1_disabled_c: Boolean type with initial value of "false"

   
3. Note: A data model regen and app pool recycle will be required for this field to be usable in the next steps.

4. Create an "In Transaction" Data Directive for the table MtlQueue. This will be used to map the bin sequence to the custom MtlQueue BinSeq_C

1. Create a condition and custom code block. Configure the condition to limit execution of the custom code block to when the MtlQueue record is first added:

1. In the custom code block place the necessary logic to map the warehouse bin sequence value to the MtlQueue record. The below example code block is intended to map BinSeq from the matching Warehouse Bin definition, but your requirements may be different, so ensure you review the code before using it.

As always, review the code below, conduct your own testing and never make any changes in a production environment before testing in a Pilot/Test environment first. Never run code that you do not fully understand.

// Make sure MtlQueue has rows
if (ttMtlQueue != null && ttMtlQueue.Count > 0)
{
    // In case multiple rows were created at once
    foreach (var row in ttMtlQueue)
    {
        // Get values from temp table first
        string fromBin = row.FromBinNum;
        string fromWhse = row.FromWhse;

        // Now query Db using those values            
        var binRecord = Db.WhseBin
            .Where(b => b.Company == Session.CompanyID 
                && b.BinNum == fromBin 
                && b.WarehouseCode == fromWhse)
            .FirstOrDefault();

        // If the whse/bin was found set the bin sequence
        if (binRecord != null)
        {
            row.BinSeq_c = binRecord.BinSeq;
        }
    }
}

1. Remember to save the code block and enable the Data Directive when you are done

2. In EKW on My Material Queue define a Material Query to sort by the custom BinSeq_c column. The name needs to match the UD Column you created exactly. Be mindful of the sort order you choose:

1. Return to My Material Queue and select the Mtl Query you just made

Outcome

Using the selected Material Query the records are now ordered in descending order by the bin sequence: