Need your help on how to format a product code

[lemodizon]

Hi everyone,
I’m trying to create a simple product information using livecode. I’m using a database (sqlite) I have 1 table (Product) see below.

I want to set my product code into this format “P00000” and every time I will add a product it will increment by 1. The problem I encountered it only increment the last char. How do you do in livecode to increment every digit until it reaches the P99999 and will add another digit. Do I have to check the previous product code before it will increment? This is new to me coz I usually used ID and set it autoincrement from the database. Hope you guide me. Thanks in advance.

here is my codes below

Code: Select all

on mouseUp
      DBOpen
   CollectAllData
end mouseUp

############################

command CollectAllData
   local tProdID, tProdName
   
   put  "P" & 00000 into fld "ProdCode"
   add 1 to char 6 of fld "ProdCode"
   
   put fld "ProdCode" into tProdID
   put fld "ProdName" into tProdName
   
   if tProdID is not empty then
      SaveProdInfo tProdID, tProdName
   end if
end CollectAllData

############################

command SaveProdInfo pProdID, pProdName
   local lSQLStatement
   global gDatabaseID
       
   put "INSERT into Product(ProdCode,ProdName)VALUES(" & quote & pProdID & quote & comma & quote & pProdName & quote &")" into lSQLStatement
   
   revExecuteSQL gDatabaseID, lSQLStatement
   
   if the result is  an integer then
      beep
      Answer Info "Hi, information successfully added." 
      lock screen
      DisplayAllProductInfo
      DBClose
      unlock screen           
   else
      DBClose
      Beep
      Answer Error "Sorry, there was a problem in adding the  information." 
      
   end if
end SaveProdInfo

[dunbarx]

Hi.

Just glanced at your handler, but why add to char 6? Assuming you have your "P" code as something like "P12345" in a variable "pCode", if you:

Code: Select all

put "P" & (char 2 to 6 of pCode + 1) into pCode

Or anything similar. Now the numbers will do their thing.

Craig

[Klaus]

Hi Craig,

this will give -> P1

You need to set the NUMBERFORMAT first like this:

Code: Select all

...
## Set to 5 digit, no floating point
set numberformat to "00000"

## Numberformat will however only affect a following mathematical operation
put "00000" into pCode
add 1 to pCode
put "P" & pCode into fld "ProdCode"
...

Best

Klaus

[Klaus]

Do I have to check the previous product code before it will increment? This is new to me coz I usually used ID and set it autoincrement from the database.

Unless you know all of your products including "pCode" by heart, this seems to be essential!
Autoincrement will only use numbers, no concatenated values -> "P" & 00007

[dunbarx]

Klaus,

this will give -> P1

Huh? No it won't. It will give "P12346".

Craig

[dunbarx]

Lemodizon.

My squabble with Klaus notwithstanding, my handler, or anything like it, should be made more robust

Code: Select all

on mouseUp
   put "P12345" into pCode
   put "P" & (char 2 to 50 of pCode + 1) into pCode
end mouseUp

LC will ignore empty "spaces", but that can come in handy when you don't actually know how many digits you might be dealing with.

Craig

[Klaus]

Klaus,

this will give -> P1

Huh? No it won't. It will give "P12346".

Craig

It doesn't here, macOS 10.14.6, LC 9.6.2

[dunbarx]

Klaus.

How is this possible? I am adding 1 to a number string, and prepending a "P".

Have you stepped through the handler?

Craig

[Klaus]

Hi Craig,

How is this possible?

to me this is correct behavior, you always need to use numberformat (or FORMAT()) to force leading ZEROs.

Have you stepped through the handler?

Yes, everything as I exspected.

Code: Select all

...
put "P00000" into pCode ## -> P00000
put "P" & (char 2 to 6 of pCode + 1) into pCode ## -> P1
...

Best

Klaus

[dunbarx]

Klaus.

It never occurred to me to have to format any of this. I never had to for such a basic string gadget.

On a new stack with a button and a field, with "P12345" in the field and this in the button script:

Code: Select all

on mouseUp
   put fld 1 into pCode
   put "P" & (char 2 to 50 of pCode + 1) into fld 1
end mouseUp

I can click all day on the button and all I get in the field is a "P" followed by ever increasing values of a 5 digit string.

It makes no difference if I pre-load a variable with, say, "P12345" and run the same code. My later post does just this. The variable contains the updated string.

Craig

[SparkOut]

Yes but this is because you start with a string that is 5 chars long (12345).
If you put "P00001" into the field then take char 2 to -1 you will get the string "00001".
But you can't add 1 to a string, so LC internally converts it to a number to add one to. As a number, LC sees this as 1 without padding with leading zeroes. When you concatenate it back with "P" & the number, you'll get "P2" unless you do something specific to render the leading zeros. It's only the string representation that will have the number padding.

[lemodizon]

hi everyone,

additional picture see below something like this i want to know on how do you do in livecode. Where it increment at the end it seem complicated code to me Thank you guys for sharing your knowledge I appreciated

pcode.JPG (14.16 KiB) Viewed 7134 times

[dunbarx]

Sparkout.

Aha. Right.

I took the OP's example ("12345") and did not think that he might start with a string like "00001".

I get that such examples of product codes look and feel better as "00001" as opposed to "1". Now I see what Klaus was on about, but he never mentioned the padded initial zeros thing, as you did, so I missed the point.

Craig

[SparkOut]

hi everyone,

additional picture see below something like this i want to know on how do you do in livecode. Where it increment at the end it seem complicated code to me Thank you guys for sharing your knowledge I appreciated

pcode.JPG

There are many ways to do this. All will need some sort of code, none of it would be terribly complicated. The approach to take might depend on how you expect the application to scale up. Would you need many more self-incrementing codes?
One way would be to have two tables in the database, each with an auto-incremented index. Then when you fetch the row with index 6, you just display the formatted output "000006" with the prefix either C for the customer code table or P for package code table.
Another way would be to keep the current code in a custom property, update it as you work on the app and save to a file. Read the file at the startup of the application so it knows where you got to next time.
Or have a lookup table in the database to read the current value and update that as you go.

The key is to note that you can only do arithmetic on numbers, so you just keep a track of the numbers. When it comes to displaying, a quick formatting and concatenation of the prefix will be needed.

[Klaus]

Hi Craig,

I took the OP's example ("12345") and did not think that he might start with a string like "00001".

to be precise, lemondizon gave this example in his first script:

I want to set my product code into this format “P00000”

and in his example script he wrote:

Code: Select all

...
put  "P" & 00000 into fld "ProdCode"
add 1 to char 6 of fld "ProdCode"
...

I took the OP's example ("12345") and did not think that he might start with a string like "00001".

No, you took your own example ("12345") in your first response:

...Assuming you have your "P" code as something like "P12345" in a variable "pCode", if you:...

I get that such examples of product codes look and feel better as "00001" as opposed to "1". Now I see what Klaus was on about, but he never mentioned the padded initial zeros thing, as you did, so I missed the point.

I thought that was more than obivous.


Best

Klaus