Need your help on how to format a product code

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

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

Post by AxWald » Thu Jul 15, 2021 11:57 am

Hi,
  1. 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.
    .
  2. 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)
    .
  3. 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".
    .
  4. 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!
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!

dunbarx
VIP Livecode Opensource Backer
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

Post by dunbarx » Fri Jul 16, 2021 4:58 am

Klaus.

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

Craig

lemodizon
Posts: 175
Joined: Thu Apr 05, 2018 3:33 pm

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

Post by lemodizon » 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" :(
p1.JPG
i added another field on my database for my prodcode
p2.JPG
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.
Thank you & God Bless Everyone :wink:

Regards,
lemodizon

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”