How can I sort code fields?

kirkostas
kirkostas Member Posts: 127
edited 2007-10-09 in NAV/Navision Classic Client
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.
kirkostas™

Answers

  • Yaroslav_Gaponov
    Yaroslav_Gaponov Member Posts: 158
    Hi

    WITH recTable DO BEGIN
    ASCENDING(TRUE);
    IF FIND('+') THAN
    MESSAGE(Code);
    END;
    Yaroslav Gaponov
  • Luc_VanDyck
    Luc_VanDyck Member, Moderator, Administrator Posts: 3,633
    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?
    No support using PM or e-mail - Please use this forum. BC TechDays 2024: 13 & 14 June 2024, Antwerp (Belgium)
  • kirkostas
    kirkostas Member Posts: 127 
    // 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.
    kirkostas™
  • Yaroslav_Gaponov
    Yaroslav_Gaponov Member Posts: 158
    edited 2007-10-09
    Hi

    So may be change type to integer :lol:

    or need repeat-until search :cry:
    Yaroslav Gaponov
  • fidel
    fidel Member Posts: 14
    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
    kirkostas Member Posts: 127
    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.
    kirkostas™
  • Luc_VanDyck
    Luc_VanDyck Member, Moderator, Administrator Posts: 3,633
    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.
    No support using PM or e-mail - Please use this forum. BC TechDays 2024: 13 & 14 June 2024, Antwerp (Belgium)
  • David_Singleton
    David_Singleton Member Posts: 5,479
    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.
    David Singleton
  • Asallai
    Asallai Member Posts: 142
    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
    David_Singleton Member Posts: 5,479
    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.
    David Singleton
  • kirkostas
    kirkostas Member Posts: 127
    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>
    kirkostas™

Categories