- 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:
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)
Code: Select all
CREATE TRIGGER "add_prodcode" AFTER INSERT ON "product" BEGIN UPDATE product SET prodcode = ('P' || printf('%05d', id)); END
. - 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');
Need your help on how to format a product code
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller
Re: Need your help on how to format a product code
Hi,
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!
-
- VIP Livecode Opensource Backer
- Posts: 9579
- Joined: Wed May 06, 2009 2:28 pm
- Location: New York, NY
Re: Need your help on how to format a product code
Klaus.
It seems I was cavalier; in my own world, you know.
Craig
It seems I was cavalier; in my own world, you know.
Craig
Re: Need your help on how to format a product code
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
Thanks again really appreciated everyone's effort.
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 "e & 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 "e & 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.
Thank you & God Bless Everyone
Regards,
lemodizon
Regards,
lemodizon