In other words you have 2 basic adjustments. I will call these two basic adjustments, Pulldown and Replenish
Pulldown - where stock is leaving your inventory. [OUT]
Replenish - add stock to your inventory. [IN]
Ok, that's fine. So to begin lets look at a table to maintain the actual inventory. The Base application comes with a pre-defined products table, I know you started a test database, but I will go ahead and use this table structure for the moment.:
Code: Select all
Products(
"ProductID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"CategoryID" INTEGER,
"Discontinued" BOOLEAN,
"LeadTime" VARCHAR(50),
"ProductDescription" VARCHAR(250),
"ProductName" VARCHAR(50),
"ReorderLevel" INTEGER,
"Serialnumber" VARCHAR(50),
"SupplierID" INTEGER,
"UnitPrice" DECIMAL(17),
"UnitsInStock" INTEGER,
"UnitsOnOrder" INTEGER
)
From reading your posts I take it you want to track each adjustment to this columns value. To do this we would want another table to hold these adujustment records. How about we just call this InvAdujustments:
Code: Select all
InvAdjustments(
"AdjID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"ProductID" INTEGER,
"AdjDate" DATE,
"AdjType" INTEGER,
"Units" INTEGER
)In this table then each record has a primary key, a foreign key to the product table, a date the adjustment happened, a type of adjustment PullDown or Replenish [In fact we could expand on these basic types - say for breakage, spoilage, customer return, etc] and the number of units in this adjustment.
OK, now for that Adjustment type field we would want a third table to hold what the valid adjustments are, and whether they are a PullDown or a Replenish. I will name the table AdjustmentTypes:
Code: Select all
AdjustmentTypes(
"AdjType" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"StockType" TINYINT NOT NULL,
"AdjName" VARCHAR(50)
)In this helper table we have AdjType which is the key we will copy to the Adjustments table, StockType which will be either 1 ( PullDown )or 2 ( Replenish ), and an Adjust ment name. The reason for this type of setup is so that we can associate more then two types of business activity to our two inventory adjustments. In other words we might populate this table at first as
0, 1, "Sale" [ Pullsdown the inventory ]
1, 2, "Purchase" [ Replenishes the inventory ]
3, 1, "Breakage" [ Pullsdown the inventory ]
4, 2, "Return" [ Replenishes the inventory ]
Alright then. This is a very basic structure for tracking an inventory and adjustments to this inventory. In practice you would want to embellish this with other information but good enough for an example.
Back to our basic adjustments. Lets start calling these "Adustment Transactions" for purposes of a business work flow discussion. One way we could implement this business process in a database is to add something called triggers to the InvAdjustments table. A trigger is simply a little snippet of code that will run when ever a predefined actions takes place on the table - for example if we added a trigger for an Insert action on the table we would do something like this in my own weird Psudeo code:
Code: Select all
IF InsertRecord.AdjType -> AdjustmentTypes.StockType = 1 THEN
REM we are pullind down the inventory
Products.UnitsOnHand = Products.UnitsOnHand - InsertRecord.Units
ELSE
IF InsertRecord.AdjType -> AdjustmentTypes.StockType = 2 THEN
REM we are replenishing the inventory
Products.UnitsOnHand = Products.UnitsOnHand + InsertRecord.Units
ELSE
REM we don't know what to do, so don't do anything
RAISE AN ERROR AND DO NOT ALLOW THE INSERT (UNKOWN STOCK ADJUSTMENT TYPE)
ENDIF
ENDIFOne way to handle this "Adjustment Transaction" is with something called a Trigger on the InvAdjustments table. Another common way would be to write a "Stored Procedure" in the database. A stored procedure is, for all intents and purposes, equivalent to a Basic procedure in Base. In both cases the procedure can be called arbitrarily, instead of being associated with a record event for a given table. The logic inside this procedure is exactly the same as the pseudo code I showed for the Trigger, a few steps would be added, such as opening record sets for the proper tables but that is just so much detail..
The question then is which approach to take with a Base program. First thing is this. The native database engine for Base does support the creation of table triggers, but these triggers MUST be written in Java and have an internal structure much closer to a stored procedure in other database engines. So, if you are comfortable working in Java we could go down that road, but given your newness to databases in general I assume that OOBasic would be a better language and environment to work in. With this in mind then a Basic procedure seems like the ticket. [As a side note, Base can work with any number of different database engines such as MySQL. If we chose to use MySQL 5.x or higher then the use of a trigger or a traditional stored procedure would be quite viable option]
Looking at what we want this Basic procedure to do we see first that we will want to pass to it all the information necessary to enter one InvAdjustment record. So we would declare the procedure with something like
Code: Select all
sub spInvAdjustment(
ProductID AS INTEGER,
AdjDate AS DATE,
AdjType AS INTEGER,
Units AS INTEGER,
dbConnection as variant )
end sub
We could accept this as a starting point if we like, and make everything else happen in the procedure. I think for tonight that is just what I will do.
[ this might look intimidating, but it really isn't and I will be happy to walk you through it all, step by step..so just read along]
Here is a basic sub procedure that implements the rudimentary steps of the pseudo code from earlier. It is not a finished product by any means, but it actually runs to completion and assuming the data passed in is valid does what we expect.
Code: Select all
sub spInvAdjustment( ProductID AS INTEGER, AdjDate AS DATE, AdjType AS INTEGER, Units AS INTEGER, dbConnection as variant )
' a variable for the stock adjustment
' to type to perform
dim StockType as integer
' and some constants for what it means
' just for readability - and good coding
' style
const cPullDown = 1
const cReplenish = 2
' just for convienence as string
' to use to build our sql commands
dim sSQL as string
sSQL = "Select ""StockType"" FROM ""AdjustmentTypes"" WHERE ""AdjType"" = ? "
' a variable for a prepared statement
' object that lets us apply SQL
' commands to the databse
dim pStmt as variant
pStmt = dbConnection.PrepareStatement( sSQL )
pStmt.setInt( 1, AdjType )
' a record set variable
dim rs as variant
rs = pStmt.ExecuteQuery
' we should have gotten back one record
if not rs.isBeforeFirst then
' if we are not before the first record
' then we got back zero records
print "oops I don't know that Adjustment type"
exit sub
else
' move to the first record
' and retrive the value from
' the frist column as an integer
rs.next
StockType = rs.getInt( 1 )
end if
' so the next step is to actually
' write our data to the InvAdjustments
' table - we can resuse our prepared
' statement for this
sSQL = "INSERT INTO ""InvAdjustments"" ( ""ProductID"", ""AdjDate"", ""AdjType"", ""Units"" ) VALUES ( ?,?,?,? ) "
pStmt = dbConnection.PrepareStatement( sSQL )
dim aDate as new com.sun.star.util.Date
aDate.Day = Day(AdjDate)
aDate.Month = Month(AdjDate)
aDate.Year = Year(AdjDate)
pStmt.setInt( 1, ProductID )
pStmt.setDate( 2, aDate )
pStmt.setInt( 3, AdjType )
pStmt.setInt( 4, Units )
pStmt.executeUpdate
' here we decide what we will do
' about the inventory items in stock
select case StockType
case cPullDown
sSQL = "UPDATE ""Products"" SET ""UnitsInStock"" = ""UnitsInStock"" - ? WHERE ""ProductID"" = ? "
case cReplenish
sSQL = "UPDATE ""Products"" SET ""UnitsInStock"" = ""UnitsInStock"" + ? WHERE ""ProductID"" = ? "
end select
pStmt = dbConnection.PrepareStatement( sSQL )
pStmt.setInt( 1, Units )
pStmt.setInt( 2, ProductID )
pStmt.executeUpdate
end sub