Convert nvarchar t

Epicor ERP 10
1

Im trying to sort a column of numbers that are strings and I’d like them to sort numerically. The catch is that I cannot convert the numbers into int. Here is the code:

therefore it does not show me the highest because it is of type nvarchar

foreach (var ttUD31_Recs in (from ttUD31_Row in ttUD31
                             where ttUD31_Row.Company == Session.CompanyID
                             && string.Equals(ttUD31_Row.RowMod, IceRow.ROWSTATE_ADDED, StringComparison.OrdinalIgnoreCase)
                             select ttUD31_Row))
{
    var ttUD31Row = ttUD31_Recs;
    if (ttUD31_Recs != null)
    {
        Ice.Tables.UD31 UD31;
        var UD31_Recs = (from UD31_Row in Db.UD31
                         where UD31_Row.Company == Session.CompanyID
                         orderby UD31_Row.Key1 descending
                         select UD31_Row).FirstOrDefault();
        {
            var UD31Row = UD31_Recs;
            if (UD31_Recs == null)
            {
                ttUD31Row.Key1 = "1";
            }
            else
            {
                ttUD31Row.Key1 = (System.Convert.ToInt32(UD31_Recs.Key1) + 1).ToString();
            }
        }
    }
}
2

I think you will have to do it in two steps.

int intKey1 = System.Convert.ToInt32(UD31_Recs.Key1) + 1;
Key1 = intKey1.ToString();
3

Lets step back a bit.

Why? What are you doing?

Do you have control over the data that is written to UD31? If so, I would be duplicating the Key
as an INT to a numeric field and do the sort on that instead.

There is really no good way to do what you are doing efficiently.

4

I have a ud form that increases each time a record is added. But every time I want to bring to the last record, that is, the maximum value. I can’t get it and therefore the sequence is incorrect

ss

5

It doesn’t really matter that I duplicate as I only have test records

1 Like
6

Unless you want pain, I would go ahead and duplicate they Key1 to a numeric field, or change your key to something like a guid and use the numeric field as your ticket number.

Do the sort on the numeric field.

If you have existing data, just write a quick procedure to write the numeric field from your key
for the existing data.

7

or use YYMMDDhhmmss as a sting into Key1 and then Number01 as the ticket number.

1 Like
8

number 01 is of decimal type, how can I convert from integer type to decimal or be able to call a column created from the c# code of the bpm

9

I have been out of practice on my linq queries… but hopefully this is close. What I did was create a LIST of records, and then sorted that list and returned the top item.


var UD31_Recs = Db.UD31.Where(x=>x.company == Session.CompanyID)
                        .Select(x=>new {int myInt = System.Convert.ToInt32(x.Key1),x.Key1})
                        .ToList();
var topRecord = UD32_Recs.OrderByDescending(x=>x.myInt).FirstOrDefault();
string myKey = topRecord.Key1;
int topValue = topRecord.myInt + 1

OR, you might be able to do all this in one line of code:


var topRecord = Db.UD31.Where(x=>x.company == Session.CompanyID)
                        .Select(x=>new {int myInt = System.Convert.ToInt32(x.Key1),x.Key1})
                        .ToList()..OrderByDescending(x=>x.myInt).FirstOrDefault();

string myKey = topRecord.Key1;
int topValue = topRecord.myInt + 1
1 Like
10

That’s not a problem back in .net / c# land. A decimal field should just take an integer. To convert it back just use Convert.ToInt32(decimalfield).

11

The way that query is written, will bring in all the records that match the company, and then sort
after the fact. If there are a lot of records, this can be a big problem.

12

OH… i agree, but I dont know of any way to convert all the string values to ints, and then sort them before returning one record.
OH WAIT… there appears to be another option. This will do the select, sort by the int version of the key, and then return only the top record, with both the Key1 value and the “myInt” value

var topRecord = Db.UD31.Where(x=>x.company == Session.CompanyID)
                        .OrderByDescending(x=>Int32.Parse(x.Key1)
                         .Select(x=> new{x.Key1,int myInt = Int32.Parse(x.Key1)+1}.FirstOrDefault();
int theTopValue = topRecord.myInt;
13

I may have spoken too soon.

I just realized we had a similar discussion before:

14

If you remove that part, but see the other thread. There were caveats, so we added leading zeros.
Warrants further research.

15

Oops… you are right… I edited the code to remove the extra .tolist() i accidently left in.

1 Like
16

If this is just to get a unique index I would highly encourage you to use a system sequence, there is no sorting or ordering and it’s a single index scan in a single table

Here’s a walk through on doing that

// Bring in Ice.Lib.NextValue.dll
const string sequenceName = "InvcGrpIDAA";
var nextVal = new Ice.Lib.NextValue(Db);    
    
int nextGroupID = nextVal.GetNextSequence(sequenceName);
3 Likes