OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Design help please Stock Movement control

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
petrikNZ
Newbie
Newbie


Joined: 04 Jun 2008
Posts: 2

PostPosted: Wed Jun 04, 2008 6:34 pm    Post subject: Design help please Stock Movement control Reply with quote

Getting in a total knot with this.

We provide stands to customers to display our product on. There are a number of different stands and customers can choose what and how many they want.

There has never been system in place for keeping tabs on the movement.

So there are x number of stands out there, we have x number in stock. What I need is a database to keep track of outgoing and incoming stands and who they are going to/coming back from. We may have a company that already has a stand that returns it adding to our in stock number. Obviously any outgoing has to reduce our instock level.

Along side instock tracking I also want to keep tabs on who has what from the moment we go live with this. Eg. Customer x may already have a stand that we don't know about, today orders a stand and next month returns that stand and the one they already had. Then we may have a customer return a stand that they had before we started tracking.

All I have right now are two tables and I'm not sure what to do next as far as setting up the matching/tracking of stands to customers.

The tables I have are:

Stands In Stock
- Stand ID
- Code
- Description
- In Stock

Customers
- Customer ID
- Customer Code
- Company Name
- Company Location
- Contact
- Notes
Back to top
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Thu Jun 05, 2008 6:35 am    Post subject: Reply with quote

Hi there,

I can give you some quick tips. This is the what I would do it.

I would have a table that had information on every stand that existed in it. You may wish to call it "Asset". The Primary Key of this table would be the unique identifier for a particular stand. This table would hold a Foreign Key field for the "type" of stand.

There would be a table that held the details of each "type" of stand. If you had, say, 6 types of stand then this table would only contain 6 rows. The unique Primary Key of each row is the one which would be stored in the "Asset" table.

There would be a table which would be your cross reference table (called "XREF"). This would also have an auto incrementing unique Primary Key and also contain fields for the date the stand was moved to a new client. This table would also have integer fields for two Foreign Keys. The first foreign key would come from the "Asset" table and the second would be a Foreign Key taken from the "Client" table Primark Key.

There would be a table that stored the details of a client (called "Client"). This table would have an auto incrementing Primary Key, the name of the client and any other details of the client required to be stored.
I would have a form that included a mainform and subform for the purposes of entering that information.

I would have a form that included a mainform and subform. The mainform would have "Asset" as its data source and the subform would have "XREF" as its data source. The forms would be linked in the subforms data properties such that the Primary Key of the "Asset" table was joined to the appropriate Foreign Key field in the "XREF" table. The "Asset" mainform would contain fields to put particulars of the individual asset but also a dropdown listbox which had as its data source the "Type" table. The boundfield of this listbox would store the "Type" Primary Key as a Foreign Key in the mainform's "Asset" data table. The subform would display the "XREF" data table in a "table grid". The subform would also have a listbox drawn onto it that would contain the "Client" data table as its list source. The boundfield of this listbox would be stored in the XREF data table as the other Foreign Key field.

I would design a simple form purely for the purpose of entering the details into the "Type" data table.

I would design a simple form purely for the purpose of entering the details into the "Client" data table.

I would design a query that referenced the "Asset" relative to each "Client". I would then design a simple form with a mainform and subform that had for the mainform the "Client" data table and which had for the subform the query data source I just designed. The subform data would be displayed with a "table grid". I would link/join in the subform's data properties the Primary Key from the mainform "Client" table to the field in the query that held the Foreign Key for the "Client" table (that was stored in the "XREF" table).

To operate the data base I would use the "Asset"/"XREF" form to enter a movement of an "Asset" by first selecting that asset in the mainform. I would then creating a new record in the XREF form which automatically contained todays date. I would then select the "Client" from the drop down listbox in the subform. Any other assets going to the same client would be done the same way by first selecting the particular "Asset" in the mainform.

When it came time to display what "Assets" were with which "Client" I would open the form that holds the query and select the client in the mainform record. The list of assets that client holds would then be displayed.

This would be the nuts and bolts of the database and could be easily expanded from here to contain or show other things.

One modification may be for the "Client" subform to contain two listbox's such that the first listbox would narrow the selection from the second listbox. This way you could have a data source for the first listbox that contained the category for each client. For this type of modification you would need an understanding of macros to enable this listbox behaviour.

I did a working example for someone that displays most of the things I just talked about (particularly the narrowed listbox's) and I also explain some things with Base:
http://www.oooforum.org/forum/viewtopic.phtml?t=71055

Hey, your in luck...I just reworked the example a bit so it now has a drop down box in the mainform and a form based on a query, (as I talked about earlier). I even tailored it a bit, using the table and field names you are likely to use. Download it here:
http://www.mediafire.com/?idd1si2xw2c
(Product_Stand example)
EDIT: More up to date improved example furthur down page!

I'm not sure how far you have got with Base yet so here are some quick instructions to get the example up and working.
1/ Unzip the files and run the Product_Stand.odb file.

2/ Once Base has opened you must register the database. This is done by Tools>Options>OpenOffice.org Base>Databases and selecting "New" then selecting the database file.

3/ You must load the Macro file too. This is done by going to the Base macro's asea, Tools>Macros>Organise Macros>OpenOffice.org basic (or just press ALT F11) then Organized>Libraries>Import and choose the script file.

Now, I haven't tested the example on Base 2.4, only with Base 2.3.1. I have heard that V2.4 can have issues with listbox's sometimes. If you have problems there are some posts on the forum about it. One solution may be to turn off the form preview (I read somewhere).

As a bit of an added bonus the example has a feature I worked out with the help of ms777 (from the macro forum). I have color coded the table grid so that each row can be marked with a different color depending on if the account for the hire has been paid, unpaid or it is a loan (you could change this to display whatever you liked of course). The feature is that as you move the vertical scrollbar up and down the colors follow the appropriate row.

Here are some screen shots from the example

Cheers

Voo







Last edited by Voobase on Fri Jun 06, 2008 6:51 pm; edited 2 times in total
Back to top
View user's profile Send private message
petrikNZ
Newbie
Newbie


Joined: 04 Jun 2008
Posts: 2

PostPosted: Thu Jun 05, 2008 6:35 pm    Post subject: Reply with quote

Thanks for the detailed reply, I have printed it out and will study it when I get a minute. Will keep you posted as to how I get on.
Back to top
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Thu Jun 05, 2008 6:46 pm    Post subject: Reply with quote

No worries,

I am just reworking it again slightly as it dosen't quite address the question of keeping track of how many stands are currently with which client or how many at your "Home Base". Presently it shows the history of the movement of stands.

Shouldn't take long so I'll repost the example soon.

Cheers

Voo
Back to top
View user's profile Send private message
Voobase
OOo Advocate
OOo Advocate


Joined: 21 Nov 2007
Posts: 400
Location: Australia

PostPosted: Fri Jun 06, 2008 9:00 am    Post subject: Reply with quote

Ok, I've made the example a bit fancier now. At the moment I only have time to give you a quick rundown on what features I've added and later I can give you a bit more of an explanation on individual sections, once you have had a chance to study it a bit more.

I've added a searchbox for the Asset form so you can search on any of the fields in the "mainform" section. The searchbox is designed to clear the filter when the text is deleted. You will need to look at the form and control events around the searchbox to see exactly which subs are involved from the macro.

As well as all the clients which are organised in categories I have added your "stand supplying store" as just another client. The category name I chose is "HomeBase" and there are two clients held under this "Northside" and "Southside". I don't know if you have multiple premises that you work from. If so just add more as if they were "clients" but keep them under the "HomeBase" Category.

Now when you are entering a new asset movement for one of your stands there are more things being kept in the asset table. It now keeps reference of which client has the item plus some text values of their name and the "type" name etc. This is to simplify getting this info again in the "Client_Stand" form.

The "Client_Stand" form now has two additional table grids to show all the items a particular client has at the present time. One of these table grids is narrowed by a listbox depending on the "type" selected. These table grids are driven off a couple of simple query's which are referenced to the "Client" subform.

There is also a calculation which occurs in a macro that counts the number of items in both of the "HomeBase" stores for the type selected in the "Type" listbox.

That's about it in brief. Of course there is a lot more going on but you will need to study the example to determine things such as what "form" and "control" events are firing which sub's in the macro. Also have a look at what data sources are involved in every part of the form by using the "Form Navigator" and looking at the different properties for the forms and control. Have a read through the macro file and try to determine what each sub is involved with. I have put some comments in the code to help here but there could be more of course.

Anyway Have Fun... Here's the updated example...
http://www.mediafire.com/?ytwdltgmc2j
(Product_Stand2 example database)

Cheers

Voo



Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group