LookupGrid and SearchDef

LookupGrid/SearchDef

These controls are definded in a xaml-file and based on type they are:

• Type 10 -> LookupGrid

• Type 20 -> SearchDef

Insert a new LookupGrid

Naming convention for LookupGrids: prefix lg_Name.lgd Naming convention for SearchDefs: Name.shd

Contents of a xaml-file for LookupGrid/SearchDef:

<?xml version="1.0" standalone="yes"?>
<TLXMLDefinition>
   <type>10</type>
   <searchfields>name</searchfields>
   <displayfields>funktions_schl, name, email, geschlecht</displayfields>
   <titles>Position, Name, E-Mail, Geschlecht</titles>
      <sqlstmt>SELECT * FROM pers_tel</sqlstmt>
   <returnvalues>nr</returnvalues>
   <orderfield>lfd_nr</orderfield>
   <tables>pers_tel</tables>
</TLXMLDefinition>

• Type: 10-LookupGrid, 20-SearchDef;

• SearchFields: fields to be searched in

• DisplayFields: fields which will be displayed in the search response

• Titles: titles of the fields from the db, which will be displayed in the Lookup

• SqlStmt: the sql-statement which will bring the data into the Lookup

  • if no SqlStmt is written, the DataSource of the LookupGrid will be programmatically set in BindDataControls() (see Window Handling Object)

• ReturnValues: field which will complete the value of the LookupGrid

• Orderfield: field(s) by which the sql-statement will be ordered

• Tables: table in which the search will take place

Result

If we want to order the data that will be retrieved in a SearchDef, the OrderBy Field must be set in the OrderBy-Fields designer section. Do NOT order data in SQL-Set.

Translating hardcoded SQL values in LG

Steps for translating hardcoded SQL values:

• 1. Add the hardcoded value to LookupGrid

For adding a hardcoded value, the sql-statement behind the lookupgrid should contain an union-sql between the hardcoded value and values that are retrieved from the database.

<?xml version="1.0" standalone="yes"?>
<TLXMLDefinition>
  <type>10</type>
  <searchfields>gjahr_per</searchfields>
  <displayfields>gjahr_per, kal_jahr_monat</displayfields>
  <titles>GJahr,KalJahr</titles>
  <sqlstmt>
    SELECT 0 as nr, '0' as gjahr, 0 as periode, 2999 as kal_jahr, 0 as kal_monat, 0 as status, 'Alle' as gjahr_per, 'Alle' as kal_jahr_monat
    UNION
    SELECT nr, gjahr, periode, kal_jahr, kal_monat, status, gjahr + '.' + RIGHT(STRING('00', periode), 2) as gjahr_per,
    STRING(kal_jahr) + '.' + RIGHT(STRING('00', kal_monat), 2) as kal_jahr_monat
    FROM fibu_periode order by kal_jahr desc, kal_monat desc
  </sqlstmt>
  <returnvalues>nr</returnvalues>
  <orderfield></orderfield>
  <tables>fibu_periode</tables>
</TLXMLDefinition>

• 2. Translate hardcoded value

Translations can be:

• • Standard -> can be found in busGenericMethods, from which the translation can be called in Opened()-WHO method. The TranslateLookUpColumns-method Translates the given columns of a given LookUpGrid

public override void Opened()
{
   lgnr.DropDownOpened += (sender, args) => 
      BusGenericMethods.Instance.TranslateLookUpColumns(lgnr,"displaytype");
}

This is the implementation in BusGenericMethods:

public virtual bool TranslateLookUpColumns([NotNull] xLookupGrid lookUpGrid, params string[] columnNames)
{
    if (lookUpGrid == null) 
        throw new ArgumentNullException(nameof(lookUpGrid));

    if(lookUpGrid.DataSource is null)
        return false;

    return TranslateDataTable(lookUpGrid.DataSource, columnNames);
}

TranslateDataTable translates the given columns of a given DataTable

public virtual bool TranslateDataTable([NotNull] DataTable dataTable, params string[] columnNames)
{
    if (dataTable == null)
        throw new ArgumentNullException(nameof(dataTable));

    var didTranslate = false;

    foreach (var row in dataTable.AsEnumerable())
    {
        foreach (var columnName in columnNames)
        {
            if (row[columnName] is string value)
            {
                row[columnName] = l.Translate(value);
                didTranslate = true;
            }
        }
    }

    return didTranslate;
}

• • Individual -> creating an own WHO-method for lookupGrid translation

public override void Opened()
{
    TranslateControl(this.xlgPeriode);
}

public virtual void TranslateControl(TimeLine.Client.Controls.xLookupGrid lookupGrid)
{
    if (lookupGrid == null) return;
    //translate hardcoded value
    var dsource = lookupGrid.DataSource;
    try
    {
        dsource.AsEnumerable().ForEach(x =>
        {
            x["wert"] = l.Translate(x["wert"].ToString());
        });
    }
    catch (Exception) { }
}