LiveCode Forums.

Posted: **Thu Jul 15, 2021 11:57 am**

Hi,

Having a "`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE" isn't just for fun - it's to make sure every record has a unique identifier. Not having such is calling for trouble.
Product codes and product names are prone to change at any time - if only because the user can do it. This results in a fiasco, the way you do it. Besides, TEXT indexes tend to be ways slower than INT indexes.
.
If you have a valid id & want to use it to create nice prodCodes, you don't fiddle in LC, you do it with a trigger:
Code: Select all
```
CREATE TRIGGER "add_prodcode" AFTER INSERT ON "product" 
BEGIN 
UPDATE product SET prodcode = ('P' || printf('%05d', id)); 
END
```
Now you just insert a ProdName (thus creating a new record & activating the trigger), and have fine id & prodCodes. (Change the '5' to the desired number of digits)
.
This way you can change both prodCode & prodName as you desire - you always use the id to refer to the product.
If the product is not needed anymore, you don't delete the record but set a 'deleted' field to true for it - so the records of old sales documents still point to a valid "deleted product".
.
If you want to find the AUTOIMCREMENT value that will be used next for table 't_table', just ask the db:
Code: Select all
```
SELECT (`seq` +1) AS NextSeq FROM `sqlite_sequence` WHERE (`name` = 't_table');
```

Have fun!

Posted: **Fri Jul 16, 2021 4:58 am**

Klaus.

It seems I was cavalier; in my own world, you know.

Craig

Posted: **Sun Jul 18, 2021 12:00 pm**

Hello everyone,

I just want to thank all you guys for sharing your knowledge.

I just end up on this format coz it's kinda difficult to code the way i want to format my prodcode into this format "P00001"

i added another field on my database for my prodcode

here is my code below

Code: Select all

on mouseUp
   getLAstnumber
end mouseUp


############################
function getMylastProdID pmylastNumber
   
   local lSQLStatement,mylastNumber, tProdCode
   global gDatabaseID
   
   put "SELECT MAX(ID) FROM Product " into nxtNumber  
   revExecuteSQL gDatabaseID, nxtNumber
   put revDataFromQuery(,, gDatabaseID, nxtNumber) into mylastNumber
   
   
   if mylastNumber is empty then
      add 1 to mylastNumber
      
      put  mylastNumber  into fld "ProdID"
      put "P" & mylastNumber into fld "ProdCode"
      put  fld "ProdCode" into tProdCode
      put fld "ProdName" into tProdName
      
      put " INSERT into Product(ID, ProdCode,ProdName) VALUES (" & quote & mylastNumber & quote &comma & quote & tProdCode &quote & comma& quote & tProdName & quote &")" into lSQLStatement
      revExecuteSQL gDatabaseID, lSQLStatement
      put  mylastNumber  into fld "ProdID"
      DisplayAllProductInfo
      
   else
      
      put "SELECT MAX(ID) FROM Product " into nxtNumber  
      revExecuteSQL gDatabaseID, nxtNumber
      put revDataFromQuery(,, gDatabaseID, nxtNumber) into mylastNumber
      
      add 1 to mylastNumber
      put "P" & mylastNumber into fld "ProdCode"
      put  fld "ProdCode" into tProdCode
      put fld "ProdName" into tProdName
      
      put " INSERT into Product(ID, ProdCode,ProdName) VALUES (" & quote & mylastNumber & quote &comma & quote & tProdCode &quote & comma& quote & tProdName & quote &")" into lSQLStatement
      revExecuteSQL gDatabaseID, lSQLStatement
      put  mylastNumber  into fld "ProdID"
      DisplayAllProductInfo
      
   end if
   
   if mylastNumber is an integer then
      beep
      Answer Info "Hi, ID number   successfully added." 
      
   end if
end getMylastProdID

Thanks again really appreciated everyone's effort.

LiveCode Forums.

Need your help on how to format a product code

Re: Need your help on how to format a product code

Re: Need your help on how to format a product code

Re: Need your help on how to format a product code