How to get code 9 rather that code 10

Gabry · 2013-08-02

This code wants to re-create a new code starting from the last and highest one that is already present in AddressTable. The problem is that when it arrives code 10 (code, [10] lenght), FINDLAST gets 9 code and not the last one 10. When I try to insert a new record with primary key (Customer, Code) there is unique record problem.

I need a statement which gets the highest code between 9 and 10: probably is a lenght problem. How can I manage that?

            AddressTable.SETRANGE("Customer No.", CustomerNo);
              IF AddressTable.FINDLAST THEN BEGIN // get last record
                CodeMax := AddressTable.Code;
                pCode :=  FORMAT(INCSTR(CodeMax), 10);
              END ELSE BEGIN
                pCode := FORMAT('1', 10);
              END;

Luc_VanDyck · 2013-08-02

Make sure that the length of all Customer No's is the same by adding 0's in front (00001, 00009, 00010, 00011).
Or add a field to the table of type Integer, copy the Customer No. to that field and use SETCURRENTKEY on that field. Then you are sorting on a integer-field instead of a code-field, which results in the correct sort order.

Gabry · 2013-08-02

Luc Van Dyck wrote:

Make sure that the length of all Customer No's is the same by adding 0's in front (00001, 00009, 00010, 00011).

I tried to apply the above solution using FORMAT(code, 10), but it does not work.

Do you know how to add the suitable number of zeros (code lenght is [10]) before the number (of different lenghts: e.g. 9, 10, 100)?

I checked on format properties as the 3.rd parameter of FORMAT function, but so far I did not find it.

Luc_VanDyck · 2013-08-02

FOR i := 1 TO 10 - STRLEN("Customer No.") DO
  "Customer No." := '0' + "Customer No."

Kishorm · 2013-08-02

You could use: CONVERTSTR(FORMAT(number,10),' ','0'))

Gabry · 2013-08-02

Kishorm wrote:

You could use: CONVERTSTR(FORMAT(number,10),' ','0'))

Thank you. For those codes that I am able to write by myself it works. The issue is that some these codes (1,..., 9, 10, ..., 100) are already written in the database, thus using FINDLAST I do not get the record with the highest code.

Please do you have any other insights using the read capabilities/statement only?

Kishorm · 2013-08-02

If you want to keep the existing values as they are then there are a couple of options...

1) Assuming you haven't already started using values prefixed with 0s then you can set the "SQL Data Type" property of the Code field to Integer - this will mean that the values are stored as an Integer in SQL (and hence sorted by numeric value) and you will not be able to use any non-numerical chars but assuming that the code is only generated by your small code snippet as per the 1st post then this will be fine.

2) An alternative option, assuming that each customer will not have too many addresses, is to iterate through each of their address, use EVALUATE(IntegerVariable, Code) to convert the Code field to an Integer variable and then keep a record of the highest Integer value found - and then use the next one.

thegunzo · 2013-08-12

The magic is in the property string for the FORMAT command...

AddressTable.SETRANGE("Customer No.", CustomerNo);
              IF AddressTable.FINDLAST THEN BEGIN // get last record
                CodeMax := AddressTable.Code;
                pCode :=  FORMAT(INCSTR(CodeMax), 10,'<Integer,10><Filler Character,0>');
              END ELSE BEGIN
                pCode := FORMAT('1', 10,'<Integer,10><Filler Character,0>');
              END;

How to get code 9 rather that code 10

Comments

Categories