Android Question String Functions for inovice number is not working as I expect[SOLVED]

[Daniel44]

Hi all. I am working on a system in which I am generating a unique number for invoices. I know there are scripts for unique values but they do not work for me for an invoice. I need the invoice number to be easy to read. The following script when it gets to 10 adds a 0 to the right and it is not what I expect

My script:

Sub NUMEROPEDIDO
  
    OpSql.Initialize
    Dim sf As StringFunctions
    sf.Initialize
    DBCursor = OpSql.SQL1.ExecQuery("SELECT rowid, * FROM pedidos ORDER BY rowid DESC ") 'contacts_table = table name in the database
    If DBCursor.RowCount > 0 Then
        DBCursor.Position = 0
        NP = sf.Right("NP-000" & sf.Trim(sf.Val(sf.Right(DBCursor.GetString("rowid"), 13)) + 1), 13)
        Log("NUMERO DE PEDIDO : " & NP)
    Else
        NP = "NP-0001"
        Log("NUMERO DE PEDIDO : " & NP)
      
    End If
End Sub

what it shows:
NP-0009
NP-00010

what I expect:

NP-0009
NP-0010

As you can see I'm working SQLite and in my table I'm working with my table pedidos (orders) rowid. This script it works. It shows consecutive numbers as I need but the problem is when I get to ten invoices. The zeros to the left aren't used but adds number to the right instead.

I am using SDK 29 on Android 11. I have tried changing it to left stringfunction but it keeps showing the same thing. I don't know if with string functions is the most convenient or I should do it another way. Thank you in advance.

 

[Mahares]

because it is empty it gives this error

My table has records. If I get a chance, I will delete all my records and test it with no records. I think I can make it work.

 

[Mahares]

what I expect:

NP-0009
NP-0010

This will do it for you whether you have no records in the table or have records. When it gets to 10, it will show it as: NP-0010. When it gets to 112, it will show it as: NP-0112. When it gets to 1001, it shows it as: NP-1001, etc.

Sub NUMEROPEDIDO
    Dim strID As String = SQL.ExecQuerySingleResult($"SELECT max(rowid) FROM pedidos"$)
    If strID=Null Then 
        strID = "NP-0001"
    Else
        strID = SQL.ExecQuerySingleResult($"SELECT 'NP-' || printf('%04i', max(rowid)+1) FROM pedidos"$)        
    End If
    Log(strID)
End Sub

 

[Daniel44]

This will do it for you whether you have no records in the table or have records. When it gets to 10, it will show it as: NP-0010. When it gets to 112, it will show it as: NP-0112. When it gets to 1001, it shows it as: NP-1001, etc.

Sub NUMEROPEDIDO
    Dim strID As String = SQL.ExecQuerySingleResult($"SELECT max(rowid) FROM pedidos"$)
    If strID=Null Then
        strID = "NP-0001"
    Else
        strID = SQL.ExecQuerySingleResult($"SELECT 'NP-' || printf('%04i', max(rowid)+1) FROM pedidos"$)       
    End If
    Log(strID)
End Sub

Hey Mahares thank you I'll try that

 

[Daniel44]

This will do it for you whether you have no records in the table or have records. When it gets to 10, it will show it as: NP-0010. When it gets to 112, it will show it as: NP-0112. When it gets to 1001, it shows it as: NP-1001, etc.

Sub NUMEROPEDIDO
    Dim strID As String = SQL.ExecQuerySingleResult($"SELECT max(rowid) FROM pedidos"$)
    If strID=Null Then
        strID = "NP-0001"
    Else
        strID = SQL.ExecQuerySingleResult($"SELECT 'NP-' || printf('%04i', max(rowid)+1) FROM pedidos"$)       
    End If
    Log(strID)
End Sub

It works! With or without data into the table it's working awesome. Thank you so much @Mahares !!!