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

Daniel44 · Sep 16, 2021

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.

Albert Kallal · Sep 16, 2021

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

Daniel44 · Sep 16, 2021

Albert Kallal said:
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

Albert Thank you for answering I'll try it!

udg · Sep 16, 2021

Did you try

B4X:

Dim NP as String = $"NP-${NumberFormat2(rowid,4,0,0,False)}"$

TILogistic · Sep 16, 2021

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

Daniel44 · Sep 16, 2021

oparra said:
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

View attachment 119177

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

Hey Oparra thank you so much I'll try it!

Daniel44 · Sep 16, 2021

udg said:
Did you try

B4X:

Dim NP as String = $"NP-${NumberFormat2(rowid,4,0,0,False)}"$

Hey udg thank you I'll try it

Daniel44 · Sep 16, 2021

Albert Kallal said:
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

Daniel44 said:

Hey udg thank you I'll try it

Click to expand...

Well I've been trying like this:

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

Daniel44 · Sep 16, 2021

udg said:
Did you try

B4X:

Dim NP as String = $"NP-${NumberFormat2(rowid,4,0,0,False)}"$

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

udg · Sep 16, 2021

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.

Jeffrey Cameron · Sep 16, 2021

Not sure if this is relevant, as I'm not that familiar with SQLite, but how have you defined your table?

Rowid Tables

If the rowid is not aliased by INTEGER PRIMARY KEY then it is not persistent and might change.

This behavior would be sub-optimal for invoice numbers.

Daniel44 · Sep 16, 2021

udg said:
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

Yes you right I shoud be using a resultSet instead. The ORDER BY I can remove that isn't a issue

Daniel44 · Sep 16, 2021

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

Rowid Tables

This behavior would be sub-optimal for invoice numbers.

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

Mahares · Sep 16, 2021

Daniel44 said:
Thank you

@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.

Daniel44 · Sep 16, 2021

Mahares said:
@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.

Hey Mahares thank you for answering! I'll try it

Daniel44 · Sep 16, 2021

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

Mahares · Sep 17, 2021

Daniel44 said:
I was able to fix it

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

Mahares · Sep 17, 2021

Daniel44 said:
Thanks to all of you.

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"))

Daniel44 · Sep 17, 2021

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?

Daniel44 · Sep 17, 2021

Mahares said:
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

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.

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

Active Member

Active Member

Active Member

Expert

Expert

Active Member

Active Member

Active Member

Active Member

Expert

Well-Known Member

Active Member

Active Member

Expert

Active Member

Active Member

Expert

Expert

Active Member

Active Member