How can I sort code fields?

kirkostas

Hi,

I have a table and I want to retrieve the maximum value of a code field.
My code field values are 1 - 10.
I sort my table but FINDLAST retrieves always the value 9 because the value 10 is under the value 1. What can I do to retrieve the correct value?

Thank you.

Yaroslav_Gaponov

Hi

WITH recTable DO BEGIN
ASCENDING(TRUE);
IF FIND('+') THAN
MESSAGE(Code);
END;

Luc_VanDyck

Does your CODE-field only contains numbers or also characters?
If you have "B1" and "A10" as values for example, what do you consider to be the largest value?

kirkostas

// update "Tax Office" table
TaxOfficeRec.SETRANGE(TaxOfficeRec.Name,"Tax Office");
IF NOT TaxOfficeRec.FINDFIRST THEN BEGIN
  TaxOfficeRec.RESET;
  TaxOfficeRec.ASCENDING(TRUE);
  IF TaxOfficeRec.FINDLAST THEN
    EVALUATE(TaxOfficeRecID,FORMAT(TaxOfficeRec.Code));
  TaxOfficeRecID += 1;
  TaxOfficeRec.LOCKTABLE;
  EVALUATE(TaxOfficeRec.Code,FORMAT(TaxOfficeRecID));
  TaxOfficeRec.Name := "Tax Office";
  TaxOfficeRec.INSERT;
END;

I have the same problem again. The sorting order of the table is:

1
10
2
3
4
5
6
7
8
9

and I want to get the value 10 but it returns me the value 9.

Yaroslav_Gaponov

Hi

So may be change type to integer

or need repeat-until search

fidel

You are trying to sort a text field as number. unfortunatelly this is not for navision.

As i see from your code you are trying to migrate data in your tax office table. This must be done once or multiple times?

kirkostas

That is why I need to sort the table with the code field because I want to have the next available value to insert my data. I want to insert about 100 records in Tax Office table.

fidel I have added you in my Skype account if you want to discuss a little more.

Luc_VanDyck

You can create a new Integer-field in your table and copy the contents of your Code-field to this field. Then create a new key on this Integer-field and you are set.

Alternative is to use a REPEAT .. UNTIL construction to loop through all records, and use a IF rec.Codefield > intLargestValue THEN intLargestValue := rec.Codefield statement to store the largest value in a variable.

David_Singleton

kirkostas wrote:

Hi,

I have a table and I want to retrieve the maximum value of a code field.
My code field values are 1 - 10.
I sort my table but FINDLAST retrieves always the value 9 because the value 10 is under the value 1. What can I do to retrieve the correct value?

Thank you.

This is how SQL sorts code fields. If it really is only 10 values, you could o it in code. If its a lot more, then set the Sort type to Integer in the Table designer.

Asallai

I was in the same trouble on the past week, because I have a database that is restored under SQL Server. And I cannot run over the whole tables to expand the length of the code fields...
But I think you create a job where does not get the no from the No.Series to set that code values to same length... :?
Is any idea how can I set the SQL database (without any programming) to the correct sorting?
I cannot believe that is not any settings to solve this problem. Or this is only my problem

David_Singleton

Asallai wrote:

I was in the same trouble on the past week, because I have a database that is restored under SQL Server. And I cannot run over the whole tables to expand the length of the code fields...
But I think you create a job where does not get the no from the No.Series to set that code values to same length... :?
Is any idea how can I set the SQL database (without any programming) to the correct sorting?
I cannot believe that is not any settings to solve this problem. Or this is only my problem

Go to properties for the field and change "SQL Data Type" to integer.

kirkostas

I don’t want to change the SQL Data Type of the field Code in Tax Office table. So I come with a solution to my problem. Using the INCSTR function I have created a pre-fix for my inserted data. For example I use the “TAX-000001”. So I do a SETFILTER in Code field for “TAX*” and I do a FINDLAST so I have my last value and I increase the value using INCSTR function.

// update "Tax Office" table
IF "Tax Office" <> '' THEN BEGIN
  TaxOfficeRec.SETRANGE(TaxOfficeRec.Name,"Tax Office");
  IF NOT TaxOfficeRec.FINDFIRST THEN BEGIN
    IF  TaxOfficeRecCode = '' THEN BEGIN
      TaxOfficeRecCode := 'TAX-000001';
      TaxOfficeRec.RESET;
      TaxOfficeRec.SETFILTER(TaxOfficeRec.Code,'TAX*');
      IF TaxOfficeRec.FINDLAST THEN BEGIN
        TaxOfficeRecCode := TaxOfficeRec.Code;
        TaxOfficeRecCode := INCSTR(TaxOfficeRecCode);
      END;
    END ELSE
      TaxOfficeRecCode := INCSTR(TaxOfficeRecCode);
    IF TaxOfficeRec.HASFILTER THEN
      TaxOfficeRec.RESET;
    TaxOfficeRec.LOCKTABLE;
    TaxOfficeRec.Code := TaxOfficeRecCode;
    TaxOfficeRec.Name := "Tax Office";
    TaxOfficeRec.INSERT;
  END;
END;

Thank you all =D>