Inventory Control - Help would be just superb.

insomnai · October 26, 2011

Hi, guys and gals, please entertain me for a moment... (begin wonky dancing.... NOW!)

No really, as the header states, i'm after an assist on a second idea that i'm working towards, mainly inventory control:

After searching the forums, I have found some subjects on the matter of working with spreadsheets but only specific to the OP's question but nothing broad covering the whole area. I'm looking specifically to store information in several columns and rows and i'm wondering on the best way to approach this.

What i'm after is basically a 'stock inventory' where I can add new products i.e. [Product Code], [Make], [Model], [Current Stock Level], [Date of Add/Edit] and using this information on a second tab, take products out of 'stock' and tell me how many I have left based on the last removal or addition.

I've got to be honest guys, I don't know where to begin and as always, help is always appreciated in the form of pointing me in the right direction or some snippets with explanations on how it works.

Many many thanks

insomnai

sleepydvdr · October 27, 2011

Do you require Excel or are you open to the idea of creating a database from scratch? I have written a little bit of code that you may like. But it is back on track. Right now, only the Add A Product button has been written. Products with a quantity of less than 10 are highlighted in red; product quantity less than 100 is highlighted in yellow. If you like what I have created so far, I will continue working on it.

#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>
#include <ButtonConstants.au3>
#include <StaticConstants.au3>
#include <array.au3>
 
Global $productCode,$make,$model,$currentStock,$dataModified
 
$Form1 = GUICreate("Form1", 623, 442, 192, 124)
$ListView1 = GUICtrlCreateDummy()
IniWrite("InventoryData.ini", "JunkProduct", "Product Code", $productCode)
$Button1 = GUICtrlCreateButton("Add A Product", 16, 408, 91, 25)
$Button2 = GUICtrlCreateButton("Edit A Product", 120, 408, 91, 25)
$Button3 = GUICtrlCreateButton("Delete A Product", 224, 408, 107, 25)
GUISetState(@SW_SHOW)
_RefreshData()
 
While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            _AddAProduct()
    EndSwitch
WEnd
 
Func _AddAProduct()
#Region ### START Koda GUI section ### Form=
$Form2 = GUICreate("Form1", 336, 258, 302, 218)
$cInput1 = GUICtrlCreateInput("", 16, 24, 193, 21)
$cInput2 = GUICtrlCreateInput("", 16, 64, 193, 21)
$cInput3 = GUICtrlCreateInput("", 16, 104, 193, 21)
$cInput4 = GUICtrlCreateInput("", 16, 144, 193, 21)
$cInput5 = GUICtrlCreateInput("", 16, 184, 193, 21)
$Label1 = GUICtrlCreateLabel("Product Code", 224, 24, 69, 17)
$Label2 = GUICtrlCreateLabel("Make", 224, 64, 31, 17)
$Label3 = GUICtrlCreateLabel("Model", 224, 104, 33, 17)
$Label4 = GUICtrlCreateLabel("Current Stock", 224, 144, 69, 17)
$Label5 = GUICtrlCreateLabel("Date Modified", 224, 184, 70, 17)
$Button3 = GUICtrlCreateButton("Add Product", 120, 216, 91, 25)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###
 
While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            GUIDelete($Form2)
            ExitLoop
        Case $Button3
            $productCode = GUICtrlRead($cInput1)
            $make = GUICtrlRead($cInput2)
            $model = GUICtrlRead($cInput3)
            $currentStock = GUICtrlRead($cInput4)
            $dataModified = GUICtrlRead($cInput5)
            $errorCheck = IniRead("InventoryData.ini", "Product Code", "Product Code", "NotInDatabase")
            If $errorCheck = "NotInDatabase" Then
                IniWrite("InventoryData.ini", $productCode, "Product Code", $productCode)
                IniWrite("InventoryData.ini", $productCode, "Make", $make)
                IniWrite("InventoryData.ini", $productCode, "Model", $model)
                IniWrite("InventoryData.ini", $productCode, "Current Stock", $currentStock)
                IniWrite("InventoryData.ini", $productCode, "Date Modified", $dataModified)
            Else
                MsgBox(0, "Error", "The product is already in the database")
            EndIf
            GUIDelete($Form2)
            _RefreshData()
            ExitLoop
    EndSwitch
WEnd
EndFunc
 
Func _RefreshData()
GUISwitch($Form1)
GUICtrlDelete($ListView1)
$ListView1 = GUICtrlCreateListView("Product Code|Make|Model|Current Stock|Date Modified", 16, 8, 586, 390)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 0, 100)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 1, 150)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 2, 150)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 3, 85)
GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 4, 100)
 
$sectionNames = IniReadSectionNames("InventoryData.ini")
 
For $i = 1 to $sectionNames[0] Step 1
    $productCode = IniRead("InventoryData.ini", $sectionNames[$i], "Product Code", "NotFound")
    $make = IniRead("InventoryData.ini", $sectionNames[$i], "Make", "NotFound")
    $model = IniRead("InventoryData.ini", $sectionNames[$i], "Model", "NotFound")
    $currentStock = IniRead("InventoryData.ini", $sectionNames[$i], "Current Stock", "NotFound")
    $dataModified = IniRead("InventoryData.ini", $sectionNames[$i], "Date Modified", "NotFound")
    GUICtrlCreateListViewItem($productCode & "|" & $make & "|" & $model & "|" & $currentStock & "|" & $dataModified, $ListView1)
    If Number($currentStock) < 100 Then
        GUICtrlSetBkColor ( -1, 0xfff333)
    EndIf
    If Number($currentStock) < 10 Then
        GUICtrlSetBkColor ( -1, 0xff0000)
    EndIf
Next
EndFunc

However, if you want to work with Excel only, then you will probably want to use a UDF like this one:

Edited October 27, 2011 by sleepydvdr

sleepydvdr · October 27, 2011

OK, forget what I posted before. Here's a workable example:

#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>
#include <ButtonConstants.au3>
#include <StaticConstants.au3>
#include <array.au3>
#include <ComboConstants.au3>

Global $productCode, $make, $model, $currentStock, $dataModified, $productToEdit

$Form1 = GUICreate("Form1", 623, 442, 192, 124)
$ListView1 = GUICtrlCreateDummy()
$Button1 = GUICtrlCreateButton("Add A Product", 16, 408, 91, 25)
$Button2 = GUICtrlCreateButton("Edit A Product", 120, 408, 91, 25)
$Button3 = GUICtrlCreateButton("Delete A Product", 224, 408, 107, 25)
GUISetState(@SW_SHOW)
_RefreshData()

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Button1
            _AddAProduct()
        Case $Button2
            _EditProduct()
        Case $Button3
            _DeleteItem()
    EndSwitch
WEnd

Func _AddAProduct()
    $Form2 = GUICreate("Product Inventory", 336, 258, 302, 218)
    $aInput1 = GUICtrlCreateInput("", 16, 24, 193, 21)
    $aInput2 = GUICtrlCreateInput("", 16, 64, 193, 21)
    $aInput3 = GUICtrlCreateInput("", 16, 104, 193, 21)
    $aInput4 = GUICtrlCreateInput("", 16, 144, 193, 21)
    $aInput5 = GUICtrlCreateInput(@MON & "/" & @MDAY & "/" & @YEAR, 16, 184, 193, 21)
    $Label1 = GUICtrlCreateLabel("Product Code", 224, 24, 69, 17)
    $Label2 = GUICtrlCreateLabel("Make", 224, 64, 31, 17)
    $Label3 = GUICtrlCreateLabel("Model", 224, 104, 33, 17)
    $Label4 = GUICtrlCreateLabel("Current Stock", 224, 144, 69, 17)
    $Label5 = GUICtrlCreateLabel("Date Modified", 224, 184, 70, 17)
    $cButton3 = GUICtrlCreateButton("Add Product", 120, 216, 91, 25)
    GUISetState(@SW_SHOW)

    While 1
        $nMsg = GUIGetMsg()
        Switch $nMsg
            Case $GUI_EVENT_CLOSE
                GUIDelete($Form2)
                ExitLoop
            Case $cButton3
                $productCode = GUICtrlRead($aInput1)
                $make = GUICtrlRead($aInput2)
                $model = GUICtrlRead($aInput3)
                $currentStock = GUICtrlRead($aInput4)
                $dataModified = GUICtrlRead($aInput5)
                $errorCheck = IniRead("InventoryData.ini", $productCode, "Product Code", "NotInDatabase")
                If $errorCheck = "NotInDatabase" Then
                    IniWrite("InventoryData.ini", $productCode, "Product Code", $productCode)
                    IniWrite("InventoryData.ini", $productCode, "Make", $make)
                    IniWrite("InventoryData.ini", $productCode, "Model", $model)
                    IniWrite("InventoryData.ini", $productCode, "Current Stock", $currentStock)
                    IniWrite("InventoryData.ini", $productCode, "Date Modified", $dataModified)
                Else
                    MsgBox(0, "Error", "The product is already in the database")
                EndIf
                GUIDelete($Form2)
                _RefreshData()
                ExitLoop
        EndSwitch
    WEnd
EndFunc   ;==>_AddAProduct

Func _RefreshData()
    GUISwitch($Form1)
    GUICtrlDelete($ListView1)
    $ListView1 = GUICtrlCreateListView("Product Code|Make|Model|Current Stock|Date Modified", 16, 8, 586, 390)
    GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 0, 100)
    GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 1, 150)
    GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 2, 150)
    GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 3, 85)
    GUICtrlSendMsg(-1, $LVM_SETCOLUMNWIDTH, 4, 100)

    $sectionNames = IniReadSectionNames("InventoryData.ini")
    If @error Then
        Dim $sectionNames[2]
        $sectionNames[0] = 1
        $sectionNames[1] = "NotFound"
    EndIf
    _ArraySort($sectionNames, 0, 1)

    For $i = 1 To $sectionNames[0] Step 1
        $productCode = IniRead("InventoryData.ini", $sectionNames[$i], "Product Code", "NotFound")
        $make = IniRead("InventoryData.ini", $sectionNames[$i], "Make", "NotFound")
        $model = IniRead("InventoryData.ini", $sectionNames[$i], "Model", "NotFound")
        $currentStock = IniRead("InventoryData.ini", $sectionNames[$i], "Current Stock", "NotFound")
        $dataModified = IniRead("InventoryData.ini", $sectionNames[$i], "Date Modified", "NotFound")
        GUICtrlCreateListViewItem($productCode & "|" & $make & "|" & $model & "|" & $currentStock & "|" & $dataModified, $ListView1)
        If Number($currentStock) < 100 Then
            GUICtrlSetBkColor(-1, 0xfff333)
        EndIf
        If Number($currentStock) < 10 Then
            GUICtrlSetBkColor(-1, 0xff0000)
        EndIf
    Next
EndFunc   ;==>_RefreshData

Func _DeleteItem()
    $Form3 = GUICreate("Delete A Product", 303, 169, 302, 218)
    $sectionNames = IniReadSectionNames("InventoryData.ini")
    If @error Then
        ;MsgBox(0, "", "Error")
        Dim $sectionNames[2]
        $sectionNames[0] = 1
        $sectionNames[1] = "NotFound"
    EndIf
    _ArraySort($sectionNames, 0, 1)
    $delCombo = GUICtrlCreateCombo("", 24, 40, 241, 25, BitOR($CBS_DROPDOWNLIST,$CBS_AUTOHSCROLL))
    $delLabel1 = GUICtrlCreateLabel("Select an item to delete", 24, 16, 115, 17)
    $delLabel2 = GUICtrlCreateLabel("Warning: this cannot be undone", 24, 72, 186, 17)
    GUICtrlSetFont(-1, 8, 800, 0, "MS Sans Serif")
    GUICtrlSetColor(-1, 0xFF0000)
    $delButton1 = GUICtrlCreateButton("Delete Item", 168, 112, 91, 25)
    GUISetState(@SW_SHOW)

    For $i = 1 To $sectionNames[0] Step 1
        GUICtrlSetData($delCombo, $sectionNames[$i])
    Next

    While 1
        $nMsg = GUIGetMsg()
        Switch $nMsg
            Case $GUI_EVENT_CLOSE
                GUIDelete($Form3)
                ExitLoop
            Case $delButton1
                $delete = GUICtrlRead($delCombo)
                IniDelete("InventoryData.ini", $delete)
                GUIDelete($Form3)
                _RefreshData()
                ExitLoop
        EndSwitch
    WEnd
EndFunc   ;==>_DeleteItem

Func _EditProduct()
    $sectionNames = IniReadSectionNames("InventoryData.ini")
    If @error Then
        Dim $sectionNames[2]
        $sectionNames[0] = 1
        $sectionNames[1] = "NotFound"
    EndIf
    _ArraySort($sectionNames, 0, 1)

    $Form4 = GUICreate("Edit a product", 303, 169, 302, 218)
    $delCombo = GUICtrlCreateCombo("", 24, 40, 241, 25, BitOR($CBS_DROPDOWNLIST,$CBS_AUTOHSCROLL))
    $delLabel1 = GUICtrlCreateLabel("Select an item to edit", 24, 16, 115, 17)
    $delButton1 = GUICtrlCreateButton("Edit Item", 168, 112, 91, 25)
    GUISetState(@SW_SHOW)

    For $i = 1 To $sectionNames[0] Step 1
        GUICtrlSetData($delCombo, $sectionNames[$i])
    Next

    While 1
        $nMsg = GUIGetMsg()
        Switch $nMsg
            Case $GUI_EVENT_CLOSE
                GUIDelete($Form4)
                ExitLoop
            Case $delButton1
                $productToEdit = GUICtrlRead($delCombo)
                If $productToEdit = "" Then
                    GUIDelete($Form4)
                    ExitLoop
                Else
                GUIDelete($Form4)
                _EditProductForm()
                ExitLoop
                EndIf
        EndSwitch
    WEnd
EndFunc   ;==>_EditProduct

Func _EditProductForm()
    $make = IniRead("InventoryData.ini", $productToEdit, "Make", "NotFound")
    $model = IniRead("InventoryData.ini", $productToEdit, "Model", "NotFound")
    $currentStock = IniRead("InventoryData.ini", $productToEdit, "Current Stock", "NotFound")
    $dataModified = IniRead("InventoryData.ini", $productToEdit, "Date Modified", "NotFound")
    $Form5 = GUICreate("Form1", 336, 258, 302, 218)
    $cInput1 = GUICtrlCreateInput($productToEdit, 16, 24, 193, 21)
    $cInput2 = GUICtrlCreateInput($make, 16, 64, 193, 21)
    $cInput3 = GUICtrlCreateInput($model, 16, 104, 193, 21)
    $cInput4 = GUICtrlCreateInput($currentStock, 16, 144, 193, 21)
    $cInput5 = GUICtrlCreateInput(@MON & "/" & @MDAY & "/" & @YEAR, 16, 184, 193, 21)
    $Label1 = GUICtrlCreateLabel("Product Code", 224, 24, 69, 17)
    $Label2 = GUICtrlCreateLabel("Make", 224, 64, 31, 17)
    $Label3 = GUICtrlCreateLabel("Model", 224, 104, 33, 17)
    $Label4 = GUICtrlCreateLabel("Current Stock", 224, 144, 69, 17)
    $Label5 = GUICtrlCreateLabel("Date Modified", 224, 184, 70, 17)
    $Button3 = GUICtrlCreateButton("Edit Product", 120, 216, 91, 25)
    GUISetState(@SW_SHOW)
    #EndRegion ### END Koda GUI section ###

    While 1
        $nMsg = GUIGetMsg()
        Switch $nMsg
            Case $GUI_EVENT_CLOSE
                GUIDelete($Form5)
                ExitLoop
            Case $Button3
                $productCode = GUICtrlRead($cInput1)
                $make = GUICtrlRead($cInput2)
                $model = GUICtrlRead($cInput3)
                $currentStock = GUICtrlRead($cInput4)
                $dataModified = GUICtrlRead($cInput5)
                IniWrite("InventoryData.ini", $productCode, "Product Code", $productCode)
                IniWrite("InventoryData.ini", $productCode, "Make", $make)
                IniWrite("InventoryData.ini", $productCode, "Model", $model)
                IniWrite("InventoryData.ini", $productCode, "Current Stock", $currentStock)
                IniWrite("InventoryData.ini", $productCode, "Date Modified", $dataModified)
                GUIDelete($Form5)
                _RefreshData()
                ExitLoop
        EndSwitch
    WEnd
EndFunc   ;==>_EditProductForm

Edited October 27, 2011 by sleepydvdr

kertz · September 25, 2012

you managed to finish the script?

DicatoroftheUSA · September 25, 2012

With inventory, I recommend learning sqlite, and investing in a barcode printer and scanner.

Sign In

Inventory Control - Help would be just superb.

Recommended Posts

insomnai

Link to comment

Share on other sites

sleepydvdr

Link to comment

Share on other sites

sleepydvdr

Link to comment

Share on other sites

kertz

Link to comment

Share on other sites

DicatoroftheUSA

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta