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

Daniel44

Active Member
Licensed User
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:

B4X: 
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.
 
Last edited:
Sort by date Sort by votes

Albert Kallal

Active Member
Licensed User
OK, try this:

B4X: 
        Dim NP As String = "NP-0000"
        Dim strID As String = DBCursor.GetString("rowid")
        NP = NP.SubString2(0,7-strID.Length) & strID

So substring is zero based, but the 2nd value is also absolute position in the string less 1.
(so in theory, that number should be 6, but we have to +1 to it).

Don't think you need any string handling library code.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada
 
Upvote 0

Daniel44

Active Member
Licensed User
Albert Thank you for answering I'll try it!
 
Upvote 0

udg

Expert
Licensed User
Longtime User
Did you try
B4X: 
Dim NP as String = $"NP-${NumberFormat2(rowid,4,0,0,False)}"$
 
Upvote 0

TILogistic

Expert
Licensed User
Longtime User
Use B4XFormatter

[B4X] B4XFormatter - Advanced number formatter

B4XFormatter is an alternative to NumberFormat / NumberFormat2 keywords. It is implemented in B4X and it is cross platform. There are two types in the library: B4XFormatter - The main class. B4XFormatData - A type with various configurable fields. The formatter holds a list of format data...
www.b4x.com

B4X: 
Public Sub TestFormatter
    Dim out As String
   
    out = out & CRLF & SetFormmatter("NP-", "12345")
    out = out & CRLF & SetFormmatter("NP-", 3.45)
    out = out & CRLF & SetFormmatter("NP-", "123")
    out = out & CRLF & SetFormmatter("NP-", 2456000.3)
   
    Log(out)
End Sub

Public Sub SetFormmatter( Prefix As String, Value As String) As String
    Dim formatter As B4XFormatter
    formatter.Initialize
    formatter.GetDefaultFormat.GroupingCharacter = ""
    formatter.GetDefaultFormat.DecimalPoint = ""
    formatter.GetDefaultFormat.MaximumFractions = 0
    formatter.GetDefaultFormat.MinimumFractions = 0
    formatter.GetDefaultFormat.Prefix = Prefix
    formatter.GetDefaultFormat.IntegerPaddingChar = "0"
    formatter.GetDefaultFormat.MinimumIntegers = 8
    Return formatter.Format(Value)
End Sub



other:

Formattare con separat migliaia e virgola decimali (modo italiano)

Provo a guardarlo al volo Come non detto. Magari dopo la partita, visto che gli esempi di @oparra funzionano bene e sono l'ideale, essendo B4X e multipiattaforma.
www.b4x.com
 
Upvote 0

Daniel44

Active Member
Licensed User
Hey Oparra thank you so much I'll try it!
 
Upvote 0

Daniel44

Active Member
Licensed User
B4X: 
OpSql.Initialize
    
    
    DBCursor = OpSql.SQL1.ExecQuery("SELECT rowid, * FROM pedidos ORDER BY rowid DESC ") 'contacts_table = table name in the database
    Dim rowid As Long = DBCursor.GetLong("rowid")
    Dim NP As String = $"NP-${NumberFormat2(rowid,4,0,0,False)}"$

error:

android.database.CursorIndexOutOfBoundsException: Index -1 requested, with a size of 0

Then tried this:

B4X: 
OpSql.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
        Dim NP As String = "NP-0000"
        Dim strID As String = DBCursor.GetString("rowid")
        NP = NP.SubString2(0,7-strID.Length) & strID
        Log("NUMERO DE PEDIDO : " & NP)
    Else
        
        Log("NUMERO DE PEDIDO : " & NP)
        
    End If
without error but it doesn't change the numer ex: 0001, 0002, 0003.. just show 0001 when I'm adding the second invoice.my order table is empty and when I fill it with data it is after that I need it to change the invoice number. if one already exists then change it to 0002.

Thank you
 
Upvote 0

Daniel44

Active Member
Licensed User
udg said:
Did you try
B4X: 
Dim NP as String = $"NP-${NumberFormat2(rowid,4,0,0,False)}"$
Click to expand...
I tried this:
B4X: 
OpSql.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)
        Dim NP As String = $"NP-${NumberFormat2(DBCursor.GetString("rowid"),4,0,0,False)}"$
        Log("NUMERO DE PEDIDO : " & NP)
     End If
but nothing is displayed.. no numbers
My table pedidos (orders) is empty, when I start adding the first one it should be display 0001, when I add the second it should be display 0002. But nothing happens

Thank you
 
Upvote 0

udg

Expert
Licensed User
Longtime User
You should use a ResultSet instead of a Cursor.
Anyway, in your post #9 you don't cover the case of an empty table (rowcount = 0); that's the place where you would put NP equal to NP-0001, if I understand you correctly
When it comes to a table with a least one row, you can
- limit the result to one row (since you have an ORDER BY desc)
- pick that rowid,
- add 1
- use it in NumberFormat2

Code example:
B4X: 
DBCursor = OpSql.SQL1.ExecQuery("SELECT rowid, * FROM pedidos ORDER BY rowid DESC LIMIT 1;") 'contacts_table = table name in the database
    If DBCursor.RowCount > 0 Then
        DBCursor.Position = 0
        Dim crowid As Long = DBCursor.GetLong("rowid")
        Dim NP As String = $"NP-${NumberFormat2(crowid+1,4,0,0,False)}"$
        Log("NUMERO DE PEDIDO : " & NP)
     Else
        NP = "NP-0001"
     End If
Something like that.

BTW, I wouldn't use rowid as the base for the invoice numbering. Better to have a separate numeric field to increment when adding a new invoice.
Read here about rowid possible values reuse.
 
Last edited:
Upvote 0

Daniel44

Active Member
Licensed User
Yes you right I shoud be using a resultSet instead. The ORDER BY I can remove that isn't a issue
 
Upvote 0

Daniel44

Active Member
Licensed User
Jeffrey Cameron said:
Not sure if this is relevant, as I'm not that familiar with SQLite, but how have you defined your table?

This behavior would be sub-optimal for invoice numbers.
Click to expand...
Hey Jeffrey thank you for you reply. It's a common table. I'm not using an autoincrement as a Primary Key I just use the rowid inside the table , and it is well defined. Thank you
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
Daniel44 said:
Thank you
Click to expand...
@oparra and @udg gave some nice options. And if you want another method to do it all within SQLite instead of using an external function. here it is:
B4X: 
Dim rs As ResultSet = SQL.ExecQuery("SELECT 'NP-' || printf('%04i',rowid) as rid, * FROM pedidos  ORDER BY rid DESC")
Do While rs.NextRow
    log(rs.getstring("rid"))
Loop
In the resultset: 1 becomes NP-0001, 18 becomes NP-0018, 502 becomes: NP-0502, etc.
 
Upvote 0

Daniel44

Active Member
Licensed User
Hey Mahares thank you for answering! I'll try it
 
Upvote 0

Daniel44

Active Member
Licensed User
Thanks to all who responded. I am very grateful. I was able to fix it. I leave here my script in case anyone can use it. Thanks to all of you.

B4X: 
Sub NUMEROPEDIDO
    OpSql.Initialize
    DBCursor = OpSql.SQL1.ExecQuery("SELECT MAX(rowid) as ID FROM pedidos ORDER BY rowid DESC")

    If DBCursor.RowCount > 0 Then
    DBCursor.Position = 0
    Dim NP As String = "NP-0000000"
    Dim strID As String = DBCursor.GetString("ID")+1
    NP = NP.SubString2(0,10-strID.Length) & strID
    Log("NUMERO PEDIDO : "&NP)
    Else
        Dim fakerowid As String = 1
         NP  = "NP-0000000"
        
        NP = NP.SubString2(0,10-fakerowid.Length) & fakerowid
        Log("NUMERO PED : "&NP)
    End If
End Sub
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
Daniel44 said:
I was able to fix it
Click to expand...
Hey Daniel: You really did not need to make it complicated by using a cursor or resultset at all. Here is what you full code should have been:
B4X: 
Sub NUMEROPEDIDO
 Dim strID As String= SQL.ExecQuerySingleResult("SELECT max(rowid)+1 FROM pedidos ")
 Dim NP As String
 If strID > 1 Then
       NP  = "NP-0000000"
       NP = NP.SubString2(0,NP.Length-strID.Length) & strID
       Log("NUMERO PEDIDO : " & NP)
 Else
       NP  = "NP-0000001"            
 End If
End Sub
 
Upvote 1
M

Mahares

Expert
Licensed User
Longtime User
Daniel44 said:
Thanks to all of you.
Click to expand...
Here it is all in ONE line of code and tested. Try it. If it does not do what you want, I will admit Messi is a better player that Ronaldo.
B4X: 
Log( IIf(SQL.ExecQuerySingleResult($"SELECT max(rowid) FROM pedidos"$) > 0 , _
    SQL.ExecQuerySingleResult($"SELECT 'NP-' || printf('%07i', max(rowid)+1) FROM pedidos"$) , "NP-0000001"))
 
Upvote 1

Daniel44

Active Member
Licensed User
Hey Mahares thank you so much I'll try. I really appreciate your help so nice from you Thank you! Hey! It is more than clear that Messi is better than Ronaldo or do you have doubts?
 
Upvote 0

Daniel44

Active Member
Licensed User
It Works when my table pedidos has data.. but if it empty it shows me this error:
java.lang.NullPointerException: Attempt to invoke virtual method 'java.lang.String java.lang.String.trim()' on a null object reference.

Don't forget that my table is empty and that at the moment of entering the first invoice the invoice number NP-0000001 should be generated. Maybe because it is empty it gives this error

Anyway I have found a new solution by having my table empty as @udg told me. I have done this:

B4X: 
OpSql.Initialize
   
    DBCursor = OpSql.SQL1.ExecQuery("SELECT rowid, * FROM pedidos ORDER BY rowid DESC")
    If DBCursor.RowCount > 0 Then
    DBCursor.Position = 0
        Dim crowid As Long = DBCursor.GetLong("rowid")
        Dim NP As String = $"NP-${NumberFormat2(crowid+1,7,0,0,False)}"$
    Log("NUMERO PEDIDO : "&NP)
    Else
        Dim fakerowid As String = 1
         NP  = "NP-0000000"
       
        NP = NP.SubString2(0,10-fakerowid.Length) & fakerowid
        Log("NUMERO PED : "&NP)
    End If
End Sub

And it works so far.
 
Upvote 0
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…