Navigation: Build Your Own ERP > Parser >

Parser for Data Grid Control

 

 

 

The parser includes ParseControlExpression that can retrieve the actual value from the data grid. This is usually used in creating column formula to automatically compute column values.

The syntax for parsing data grid control is:
 {Grid.GridName.ColumnName.FunctionName.FilterExpression.FormatEnum} or {GridName.ColumnName.MethodName.FilterExpression.FormatEnum}

The syntax expression is separated by dot "." and each part is explained below:

Grid = is optional only. You can totally omit it.
 GridName = refers to the name of the data grid or control. This is DatasetTableName under the TableCollection data grid of the App Gallery.
 ColumnName = refers to the column name of the data grid.
 FunctionName = refers to the function to be used when evaluating the value. The default function is "Value" which returns the value of the cell
 FilterExpression = refers to the filter to be applied before retrieving the value of the control. This uses the ado.net datatable filter expression.
 FormatEnum = refers to output format such as number format and date format.

Let's us use the following data grid as an example. The Vat and WTax columns are computed automatically.

The above data grid has the following structure.

Caption

Actual Name

Remarks

Subsidiary Ledger

BookSL

Actual grid name may differ from the Caption

GrossTaxable

Amount

Column caption may differ from the actual Column Name

The current row is the 1st row and the rest of the columns have the same captions and column names.

Since you will be using the App Gallery when creating your formula, you can easily see the actual grid and column names. See screenshot of the App Galery below.

Let's us now create formula expression to get the values from the above data grid.

Formula Expression

Return Value

Remarks

{BookSL.VATRate.Value} or {BookSL.VATRate}

.12

BookSL = Use the actual grid name
VATRate = Actual column name
 Value = Get the cell value of the current row of the column VATRate.
You can omit "Value" in the formula since this is the default FunctionName

{BookSL.Amount}
 Use the actual column name "Amount"
 

112.00

As explained above, the actual column name is "Amount" instead of the caption "GrossTaxable". This returns the cell's current row value of the column "Amount" which is 112.00.

'{BookSL.VatAmount.Sum..ToNumber}'

24,012.00

Returns the total of the column "VatAmount" and format the result to number with thousand separator and 2 decimal places. The formula is enclosed with single quote because it's now a string type instead of numeric type. The comma separator forces us to treat it as string.

The ff FormatEnum are supported:
ToLower, ToUpper, ToNumberFormat, Trim, AllTrim, RTrim, TooaDate, etc.
 See ecToParserEvaluatedFormatEnum() function below for details

if({BookSL.VATRate} > 0, ({BookSL.Amount.Value}-{BookSL.Discount.Value}) / (1+{BookSL.VATRate}) * {BookSL.VATRate},0)

12

The ParseControlExpression will substitute the grid's values as follows:
 if(0.12 > 0, (112.00 - 0)/(1+0.12)*0.12,0) and finally compute the VAT Amount

You can use the following functions.

({BookSL.Amount.Sum}

224112

The "Sum" function will return the total value of the column

{BookSL.Amount.Sum.VATRate > 0}

224112

Will return the total of value of the column meeting the filter criteria "VATRate > 0"

{BookSL.EOF} and {BookSL.BOF}

True/False

EOF returns True if the grid is at the end of file
BOF return True if the grid is at the beginning of file

{BookSL.Description.Length}

 

Returns the length of the current cell value

You can use the this keyword to refer to the currently active data grid. Ex: {this.Amount} may be used instead of {BookSl.Amount} and the @ can be used to return the value without evaluating the formula expression.
 Parser will always use the current row when evaluating the formula unless a filter condition is supplied.

Following are the functions supported by the parser. Function names and True or False result are case-sensitive.

Formula Expression

Result Value

Function Name

Remarks

{BookSL.Amount.Value} or
{BookSL.Amount}

112

Value

Returns the actual value of the current row of the column Amount. This is the default function name so can omit it and just use {BookSL.Amount}

{BookSL.VATATC.EncryptedValue}

 

EncryptedValue

Returns the encrypted value of the cell. Usually used for password

{BookSL.VATATC.DisplayValue}

VB 010

DisplayValue

Returns the display value of the cell. Usually used when the column has value items

@{BookSL.EntityCode.SqlValues}

('V00002','V00283')

SqlValues

Returns all the values in the column formatted using SQL Where In Clause format. The "@" is used to prevent the parser in evaluating the result.

{BookSL.EntityCode.CommaDelimitedValues}

V00002,V00283

CommaDelimited Values

Returns all the values in the column delimitted with comma

{BookSL..EOF}

True/False

EOF
 BOF

Returns True if the grid record pointer is end-of-file or beginning-of-file, respectively.

if('{BookSL..EOF}'='True', 'True Sta', 'False Sta')

False Sta

EOF with if-then-else

You usually combined True/False result with an if-then-else function.

{BookSL..HasSelectedRow}

True/False

HasSelectedRow

Returns True if there is one or more selected rows in the grid. You skipped the ColumnName in the syntax by the double dot ".."

{BookSL..HasFocused}

True/False

HasFocused

Returns True if the grid BookSL is currently has focused
 

{BookSL..HasChanges}

True/False

HasChanges

Returns True if there are pending updates made to the grid that were not yet saved.

{BookSL.Dataset.HasChanges}

True/False

Dataset.HasChanges
 

Returns True if there are pending updates made to any of the data grids.

{BookSL.HasError}

True/False

HasError

Returns True if the grid has error

{BookSL.RowState}
 

Unchanged
 Modified
 Added

RowState

Returns current row state either one of the ff;
 Unchanged = no changes made on current row
 Modified = current row is modified
 Added = current row is being added

if('{BookSL.RowState}' = 'Unchanged','T', 'F')

T/F

RowState with if

You usually combined this function with an if-then-else function.

{BookSL.ColumnName}

 

ColumnName

Returns the current column name that has focus

{BookSL.VATATC.Contains.VB}

True

Contains

Returns True if the string after the Contains function is found. In this case the string "VB"

{BookSL.VATATC.Length}

6

Length

Returns the length of the string "VB 010", the value of the current row of the column VATATC

{BookSL.RowCount}

2

RowCount

Returns current total number of records in the grid

({BookSL.Amount.Sum}

224112

Sum

Returns the total of the values of the column Amount

{BookSL.DueDate.DateValue}

44063

DateValue

Returns the numeric value of the date 08/20/2020. Can be used in getting the number of days lapsed when comparing two dates.

{HrsWorked.TimeIn.TimeValue}

9.75

TimeValue

Returns the numeric value of the time 9:45AM. Can be used in computing payroll.

{BookSL.EntityCode.LagValue}

 

LagValue

Returns the previous row value from the current row

{BookSL.EntityCode.LeadValue}

V00283

LeadValue

Returns the next row value from the current row

{BookSL.EntityCode.SumLagValue}

 

SumLagValue

Returns the total values of the previous rows from the current row

{BookSL.EntityCode.SumLeadValue}

 

SumLeadValue

Returns the total values of the succeeding rows from the current row

{BookSL.Amount.HighestValue}

224000

HighestValue

Returns the highest value from the column

{BookSL.Amount.LowestValue}

112

LowestValue

Returns the lowest value from the column

NOTE: To skip unwanted component, use dot "." in its place. Ex: {BookSL..EOF} to skip the ColumnName component since it is not needed

Following are the Format Enum supported by the parser.

Formula Expression

Result Value

Format Enum

Remarks

'{BookSL.Amount.Sum..ToNumber}'

224,112.00

ToNumber

Format the result to number with thousand separator and 2 decimal places.
Note: The formula is enclosed with single quote because it's now a string instead of numeric type.

{BookSL.VATATC.Value..ToLower}

vb 010

ToLower
 ToUpper
 ToTitleCase
 ToSentenceCase '

Converts and returns cell value from "VB 010" to lower case "vb 010" of the column VATATC

{BookSL.VATATC.Value..RTrim}

VB 010

AllTrim
LTrim
 RTrim

Delete extra spaces
 

{BookSL.DueDate.Value..ToShortDate}

08/20/2020

ToShortDate
ToMonth
 ToDay
 ToYear
ToOADate

ToShorDate = returns "12/01/2020"
 ToMonth = returns "08"
 ToDay = returns "20"
 ToYear = returns "2020"
 ToOADate = returns 44063

{BookSL.VATATC.Value..Left2}

VB

Left
 Right
 

Retruns character from the left, the "2" represents the number of characters to be returned

{BookSL.VATATC.Value..Substring,1,4}

B 01

Substring

Returns substring from string 1 to 4. This is zero-based

NOTE: We skip the FilterExpression using dot "." in its place since it is no needed.

Below is the screenshot of the App Gallery showing the Journal Entry v20 app data structures where you can easily see the table names and the Select Statements for the column names.

Below are the actual codes used by the system for your further references.

        /// <summary>
        /// Syntax: GridName.ColumnName.funcName.RowFilter.FormatEnum
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="grid">Grid Control</param>
        /// <param name="columnName">Column Name</param>
        /// <param name="funcName">Function Name</param>
        /// <param name="rowFilter">Filter criteria</param>
        /// <param name="formatEnum">Format Enum </param>
        /// <returns></returns>
        public static T ecGetEvaluatedValue(this C1TrueDBGrid grid, string columnName, string funcName, string rowFilter, string formatEnum)
        {
            ///
            ///returns evaluated value after parsing expression based on parameters given
            funcName = funcName.Trim();
            string retVal = string.Empty;
            int bookMark = grid.Bookmark;
            try
            {
                if (columnName == "this")
                    columnName = grid.FocusedSplit.DisplayColumns[grid.Col].DataColumn.DataField;
                //
                if (funcName.ToLower() == "value" && rowFilter.Length == 0) // revised 7/15/2019 to by removing funcName.Length == 0 as the funcName maybe zero but the columnName maybe a special function like "RowCount"
                {
                    //this block is added on 7/4/2019 to immediately return the value of the grid's current column. Most of the time this is the value being asked by the calling module
                    retVal = grid.Columns[columnName].Value.ToString(); //just simply return the column value
                    if (retVal.Length > 0)
                    {
                        if (grid.ecGetColumnDataType(columnName).ecIsDataTypeDateType())
                            retVal = retVal.ecToShortDate(); //format to short date
                    }
                    else
                        if (grid.ecGetColumnDataType(columnName).ecIsDataTypeNumeric())
                        retVal = "0"; //return 0 for empty numeric field
                                      //
                    return (T)Convert.ChangeType(retVal, typeof(T)); //just simply return the column value
                }
               
                //evaluate possibilities other than the real value
               
                else if (columnName == "HasSelectedRow" || funcName == "HasSelectedRow") //for grid.HasSelectedRow expression
                    if (grid.ecHasSelectedRow())
                        retVal = "True"; //return true statement
                    else
                        retVal = "False"; //return false statement

                else if (columnName == "HasFocused" || funcName == "HasFocused") //for grid.HasFocused
                    if (grid.Focused)
                        retVal = "True"; //return true statement
                    else
                        retVal = "False"; //return false statement
                else if (columnName == "Dataset" && funcName == "HasChanges") //for all grids with changes. This should be && expression not || 10/29/2018
                    if (grid.ecGetDatasource().DataSet.HasChanges())
                        retVal = "True"; //return true statement
                    else
                        retVal = "False"; //return false statement
                else if (columnName == "HasChanges" || funcName == "HasChanges") //for specific grid with changes
                    if (grid.ecGetDatasource().GetChanges() != null && grid.ecGetDatasource().GetChanges().Rows.Count > 0)
                        retVal = "True"; //return true statement
                    else
                        retVal = "False"; //return false statement

                else if (columnName == "IsEOF" || funcName == "IsEOF" || columnName == "EOF" || funcName == "EOF") //for grid.IsEOF expression
                    if (grid.Row < 0 || grid.Row == grid.RowCount)
                        retVal = "True"; //return true statement
                    else
                        retVal = "False"; //return false statement
                else if (columnName == "IsBOF" || funcName == "IsBOF" || columnName == "BOF" || funcName == "BOF") //for grid.IsBOF expression
                    if (grid.Row == 0)
                        retVal = "True"; //return true statement
                    else
                        retVal = "False";
                else if (columnName == "HasErrors" || columnName == "HasError" || funcName == "HasErrors" || funcName == "HasError") //for grid.HasError expression
                    if (grid.ecHasErrors())
                        retVal = "True"; //return true statement
                    else
                        retVal = "False"; //return false statement
                else if (columnName == "RowState" || funcName == "RowState") //for grid.RowState expression
                    retVal = grid.ecGetRowState();  //return rowState of the current row: Unchanged, Modified, or Added
                else if (columnName == "ColumnName" || funcName == "ColumnName") //for grid's current column name
                    retVal = grid.FocusedSplit.DisplayColumns[grid.Col].DataColumn.DataField;  //return column name of the current column

                else if (columnName == "SelectedRows" && funcName == "Count" || (funcName == "Count" && rowFilter == "SelectedRows")) //for counting the number of selected rows in a grid
                    retVal = grid.SelectedRows.Count.ToString();

                else if (funcName == "ForeColor")
                    retVal = grid.FocusedSplit.DisplayColumns[grid.Col].Style.ForeColor.Name; //does not work yet
                else if (funcName == "BackColor")
                    retVal = grid.FocusedSplit.DisplayColumns[grid.Col].Style.BackColor.Name; //does not work yet
                else if (funcName == "Contains")
                {
                    if (grid[grid.Row, grid.FocusedSplit.DisplayColumns[grid.Col].DataColumn.DataField].ToString().IndexOf(rowFilter) >= 0)
                        retVal = "True";
                    else
                        retVal = "False";
                }

                //for with RowFilter expression, use the grid's datasource in evaluating the value except when the rowFile is equal to "selectedrows"
                //selectedrows filter uses the grid in getting the values instead
                else if (rowFilter != null && rowFilter.Trim().Length > 0) //should have the char "=" to consider as filter argument
                {
                    DataView dv = grid.ecGetDatasourceCurrent(); //use the dataview in getting the value
                    string orginalFilter = dv.RowFilter;

                    try
                    {
                        dv.RowFilter = rowFilter; //apply filter
                    }
                    catch
                    {
                        dv.RowFilter = orginalFilter; //set as if rowFilter did not match any records
                    }
                    finally
                    {
                        if (funcName == "Length")
                        //returns the length of column value
                        {
                            if (dv.Count > 0)
                                retVal = dv[0][grid.Columns[columnName].DataField].ToString().Trim().Length.ToString();
                            else
                                retVal = "0"; //default to 0
                        }
                        else if (funcName == "RowCount" || columnName == "RowCount" || (columnName == "Rows" && funcName == "Count")) //for datasource row count with filter condition
                            retVal = dv.Count.ToString();
                        else if (funcName == "GridRowCount" || columnName == "GridRowCount" || (columnName == "GridRows" && funcName == "GridCount")) //for grid.Rows.Count with filter condition
                            retVal = grid.RowCount.ToString();
                        else if (funcName == "Sum") //for grid.ColumnName.Sum.RowFilter
                        {
                            retVal = dv.ecGetColumnTotal(grid.Columns[columnName].DataField, rowFilter);
                        }

                        else if (funcName.ecIsSqlWhereInPropertyType() || funcName.ecIsParserPropertyType())
                        {
                            //get column values in sql where in format
                            retVal = dv.ecGetColumnValuesToStringArray(grid.Columns[columnName].DataField, rowFilter).ecToStringSqlWhereInClauseFormat(dv.ToTable().ecGetColumnDataType(columnName), rowFilter);
                            //format further for SqlValues by adding "\" before each single quote
                            if (funcName.ecIsParserPropertyType())
                            {
                                retVal = retVal.ecConvertSqlWhereInToParserFormat();
                            }
                        }

                        else if (funcName.ToLower() == "commadelimitedvalue" || funcName.ToLower() == "commadelimitedvalues")
                        {
                            //get column values comma delimited
                            retVal = dv.ecGetColumnValuesToStringCommaDelimited(grid.Columns[columnName].DataField, rowFilter);
                        }
                        //
                        else if (funcName.ToLower() == "datevalue")
                        {
                            //get datevalue
                            retVal = dv[0][columnName].ToString().ecToDate("{0:g}").ToOADate().ecToStrNoFormat();
                        }

                        else if (funcName.ToLower() == "timevalue")
                        {
                            //get timevalue
                            if (grid.Columns[columnName].Value.ToString().Length > 0)
                            {
                                if (grid.Columns[columnName].DataType.ToString().ecIsDataTypeDateType())
                                {
                                    DateTime dt = DateTime.Parse(grid.Columns[columnName].Value.ToString());
                                    retVal = Convert.ToDecimal(dt.TimeOfDay.TotalHours).ecToStrNoFormat();
                                }
                                else if (grid.Columns[columnName].DataType.ToString().ecIsDataTypeTimeSpan())
                                    retVal = Convert.ToDecimal(TimeSpan.Parse(grid.Columns[columnName].Value.ToString()).TotalHours).ecToStrNoFormat();
                            }
                            else
                                retVal = "0"; //default value
                        }
                        else //if (funcName == "Value") for grid.ColumnName.Value
                        {
                            if (dv.Count > 0)
                            {
                                retVal = dv[0][columnName].ToString(); //just return the column value
                                if (retVal.Length > 0 && grid.ecGetColumnDataType(columnName).ecIsDataTypeDateType())
                                    retVal = retVal.ecToShortDate(); //format to short date
                            }
                            else
                                retVal = "";
                        }
                        //set original filter back
                        dv.RowFilter = orginalFilter;
                    }
                }

                //for without RowFilter or when RowFilter="SelectedRows", just use the grid in getting the value
                else
                {
                    if (funcName == "Length")
                    //returns the length of the column value
                    {
                        //check if there is selected row
                        if (grid.ecHasSelectedRow())
                            retVal = grid.Columns[columnName].Value.ToString().Trim().Length.ToString();
                        else
                            retVal = "0"; //default for no selected row

                    }
                    else if (funcName == "RowCount" || columnName == "RowCount" || (columnName == "Rows" && funcName == "Count")) //for datasource row count
                        retVal = grid.RowCount.ToString();
                    else if (funcName == "GridRowCount" || columnName == "GridRowCount" || (columnName == "GridRows" && funcName == "GridCount")) //for grid.Rows.Count with filter condition
                        retVal = grid.RowCount.ToString();
                    else if (funcName == "Sum") //for grid.ColumnName.Sum.RowFilter
                        retVal = grid.ecGetColumnTotal(columnName, "").ecToStrNoFormat(2);
                    //
                    else if (funcName == "EncryptedValue" && rowFilter.Length == 0)
                        retVal = ecFunction.PasswordEncryption(grid.Columns[columnName].Value.ToString());
                    else if (funcName.ToLower() == "displayvalue" && rowFilter.Length == 0)
                        retVal = grid.Columns[columnName].CellText(grid.Row).ToString();

                    else if ((funcName.ecIsSqlWhereInPropertyType() || funcName.ecIsParserPropertyType()) && rowFilter.Length == 0)
                    {
                        //get column values in sql where in format
                        DataView dv = grid.ecGetUderlyingDataView();
                        retVal = dv.ecGetColumnValuesToStringArray(grid.Columns[columnName].DataField, rowFilter).ecToStringSqlWhereInClauseFormat(dv.ToTable().ecGetColumnDataType(columnName));
                        //format further for SqlValues by adding "\" before each single quote
                        if (funcName.ecIsParserPropertyType())
                        {
                            retVal = retVal.ecConvertSqlWhereInToParserFormat();
                        }
                    }
                    //
                    else if ((funcName.ToLower() == "commadelimitedvalue" || funcName.ToLower() == "commadelimitedvalues") && rowFilter.Length == 0)
                    {
                        //get column values comma delimited
                        DataView dv = grid.ecGetUderlyingDataView();
                        retVal = dv.ecGetColumnValuesToStringCommaDelimited(grid.Columns[columnName].DataField, rowFilter);
                    }

                    else if (rowFilter.Trim().ToLower() == "selectedrows" && (funcName == "Value" || funcName == "Values"))
                    {
                        //return column values from selected rows using sql wherein clause
                        retVal = grid.ecGetColumnValuesFromSelectedRows(grid.Columns[columnName].DataField).ecToStringSqlWhereInClauseFormat(grid.Columns[columnName].DataType.UnderlyingSystemType.Name, "None");
                    }
                    else if (funcName.ToLower() == "datevalue")
                    {
                        //get datevalue
                        retVal = grid.Columns[columnName].Value.ToString().ecToDate("{0:g}").ToOADate().ecToStrNoFormat();
                    }
                    else if (funcName.ToLower() == "timevalue")
                    {
                        //get timevalue
                        if (grid.Columns[columnName].Value.ToString().Length > 0)
                        {
                            if (grid.Columns[columnName].DataType.ToString().ecIsDataTypeDateType())
                            {
                                DateTime dt = DateTime.Parse(grid.Columns[columnName].Value.ToString());
                                retVal = Convert.ToDecimal(dt.TimeOfDay.TotalHours).ecToStrNoFormat();
                            }
                            else if (grid.Columns[columnName].DataType.ToString().ecIsDataTypeTimeSpan())
                                retVal = Convert.ToDecimal(TimeSpan.Parse(grid.Columns[columnName].Value.ToString()).TotalHours).ecToStrNoFormat();
                        }
                        else
                            retVal = "0"; //default value
                    }

                    else // for grid.ColumnName.Value
                    {
                        if (grid.AddNewMode == AddNewModeEnum.AddNewCurrent && grid.Bookmark == grid.Row)
                            retVal = ""; //return empty string.
                        else
                        {
                            try
                            {
                                if (funcName.ToLower() == "text")
                                    retVal = grid.Columns[columnName].Text;
                                else if (funcName.ToLower() == "previousvalue" || funcName.ToLower() == "lagvalue")
                                {
                                    if (grid.Bookmark == 0)
                                        if (grid.ecGetColumnDataType(columnName).ecIsDataTypeNumeric())
                                            retVal = "0";
                                        else
                                            retVal = "";
                                    else
                                        retVal = grid.Columns[columnName].CellValue(grid.Bookmark - 1).ToString();
                                }
                                else if (funcName.ToLower() == "nextvalue" || funcName.ToLower() == "leadvalue")
                                {
                                    if (grid.Bookmark + 1 < grid.RowCount)
                                        retVal = grid.Columns[columnName].CellValue(grid.Bookmark + 1).ToString();
                                    else
                                        if (grid.ecGetColumnDataType(columnName).ecIsDataTypeNumeric())
                                        retVal = "0";
                                    else
                                        retVal = "";

                                }
                                else if (funcName.ToLower() == "sumpreviousvalue" || funcName.ToLower() == "sumlagvalue")
                                {
                                    double sumvalue = 0;
                                    if (grid.Bookmark > 0)
                                        for (int i = 0; i < grid.Bookmark; i++)
                                            sumvalue = sumvalue + grid.Columns[columnName].CellValue(i).ToString().ecToNumber();
                                    retVal = sumvalue.ecToStrNoFormat();

                                }
                                else if (funcName.ToLower() == "sumnextvalue" || funcName.ToLower() == "sumleadvalue")
                                {
                                    double sumvalue = 0;
                                    for (int i = grid.Bookmark + 1; i < grid.RowCount; i++)
                                        sumvalue = sumvalue + grid.Columns[columnName].CellValue(i).ToString().ecToNumber();
                                    retVal = sumvalue.ecToStrNoFormat();

                                }
                                else if (funcName.ToLower() == "highestvalue")
                                {
                                    //grid.Bookmark = grid.Bookmark - 1;
                                    //retVal = grid.Columns[columnName].Value.ToString();
                                    //grid.Bookmark = bookMark; //back to original bookmark
                                    //retVal = grid.ecGetUderlyingDataRow(grid.Bookmark + 1)[columnName].ToString();
                                    retVal = grid.ecGetHighestColumnValue(columnName).ToString();
                                }
                                else if (funcName.ToLower() == "lowestvalue")
                                {
                                    //grid.Bookmark = grid.Bookmark - 1;
                                    //retVal = grid.Columns[columnName].Value.ToString();
                                    //grid.Bookmark = bookMark; //back to original bookmark
                                    //retVal = grid.ecGetUderlyingDataRow(grid.Bookmark + 1)[columnName].ToString();
                                    retVal = grid.ecGetLowestColumnValue(columnName).ToString();
                                }

                                else
                                {
                                    retVal = grid.Columns[columnName].Value.ToString(); //just return the column value
                                    if (retVal.Length > 0 && grid.ecGetColumnDataType(columnName).ecIsDataTypeDateType())
                                        retVal = retVal.ecToShortDate(); //format to short date
                                }

                            }
                            catch
                            {
                                if (grid.ecGetColumnIndex(columnName) >= 0)
                                {
                                    if (grid.ecGetColumnDataType(columnName).ecIsDataTypeNumeric())
                                        retVal = "0"; //default retVal for no record and on adding new row
                                    else
                                        retVal = "";
                                }
                                else
                                    retVal = string.Empty;
                            }

                            finally
                            {
                                grid.Bookmark = bookMark;
                            }
                        }
                    }
                }

                //return 0 for numeric field instead of empty space
                if (retVal.Length == 0) //for numeric field, the value should be 0 if null
                    if (grid.ecGetColumnIndex(columnName) >= 0)
                    {
                        if (grid.ecGetColumnDataType(columnName).ecIsDataTypeNumeric())
                            retVal = "0";
                        else
                            retVal = string.Empty;
                    }

                //get back to the original bookmark
                grid.Bookmark = bookMark;
            }
            catch
            {
                retVal = string.Empty;
            }
            //
            if (retVal != null && retVal.Length > 0 && formatEnum != null && formatEnum.Length > 0)
                retVal = ecFunction.FormatText(retVal, formatEnum);
            //
            return (T)Convert.ChangeType(retVal, typeof(T));
        }
               

        /// <summary>
        /// Returns converted formula by evaluating additional formatEnum such as ToLower, ToUpper, ToNumberFormat, Trim, AllTrim, RTrim, TooaDate, etc.
        /// </summary>
        /// <param name="formula"></param>
        /// <param name="formatEnum"></param>
        /// <returns></returns>
        public static string ecToParserEvaluatedFormatEnum(this string formula, string formatEnum)
        {
            //for further formatting
            string retVal = formula;
            if (formatEnum.ToLower() == "tolower") //ToLower
                retVal = retVal.ToLower();
            if (formatEnum.ToLower() == "toupper") //ToUpper
                retVal = retVal.ToUpper();
            //
            if (formatEnum.ToLower() == "tonumberformat" || formatEnum.ToLower() == "tonumber") //format it to number with command and 2 decimal places
                retVal = retVal.ecToNumber().ecToStr();

            if (formatEnum.ToLower() == "trim" || formatEnum.ToLower() == "alltrim") //format it to number with command and 2 decimal places
                retVal = retVal.Trim();
            if (formatEnum.ToLower() == "rtrim") //format it to number with command and 2 decimal places
                retVal = retVal.TrimEnd(' ');
            if (formatEnum.ToLower() == "ltrim") //format it to number with command and 2 decimal places
                retVal = retVal.TrimStart(' ');
            //Date Format
            if (formatEnum.ToLower() == "toshortdateformat" || formatEnum.ToLower() == "toshortdate")
                retVal = retVal.ecToShortDate();
            if (formatEnum.ToLower() == "tomonth") //get the Month
                retVal = retVal.ecToShortDate().ecLeft(2);
            if (formatEnum.ToLower() == "today") //get the Day
                retVal = retVal.ecToShortDate().Substring(3, 2);
            if (formatEnum.ToLower() == "toyear")
                retVal = retVal.ecToShortDate().Substring(6, 4); //get the Year
            //convert date to its numeric value similar to excel DateValue function
            if (formatEnum.ecLeft(8).ToLower() == "tooadate")
            {
                //
                retVal = retVal.ecToDate("{0:g}").ToOADate().ecToStrNoFormat();
            }

            //
            if (formatEnum.ecLeft(4).ToLower() == "left")
            {
                //
                int i = formatEnum.ecGetNumberFromStr();
                if (i > 0)
                    retVal = retVal.ecLeft(i);
            }
            if (formatEnum.ecLeft(5).ToLower() == "right")
            {
                //
                int i = formatEnum.ecGetNumberFromStr();
                if (i > 0)
                    retVal = retVal.ecRight(i);
            }
            if (formatEnum.ecLeft(9).ToLower() == "substring")
            {
                //
                formatEnum = formatEnum.Replace("Substring", "").Replace("(", "").Replace(")", "");
                string[] subs = formatEnum.ecToStringArray(',',false,true);
                if (subs[1] == null || subs[1].ToString().Length == 0 || subs[1].ecToNumber() > retVal.Length - subs[0].ecToNumber())
                    retVal = retVal.Substring(subs[0].ecToNumber());
                else
                    retVal = retVal.Substring(subs[0].ecToNumber(), subs[1].ecToNumber());

            }
            return retVal;
        }
  

 

 


 


 

 

 

Copyright © 2021 Terasoft Corporation