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

stock control Tutorial or guides?

Post new topic   Reply to topic Forum Index -> Base
View previous topic :: View next topic  
Author Message

Joined: 02 Mar 2011
Posts: 1

PostPosted: Wed Mar 02, 2011 8:21 am    Post subject: stock control Tutorial or guides? Reply with quote

Hello all

I am a young student trying to get to grips with Base. I have gone through many of the tutorials i found on this forum (club members, Authors/book records and others), around 8 if i remember right.

My question is this, does anyone know of or have a guide on building a stock control database. I have tried to build my own a few times in the last week but have run in to various problems. I am rather hopeful there may be a worked example from which i can work through and identify where i was going wrong.

I have a products, suppliers, costs, delivery s and transaction tables which i have linked with the appropriate relations. I managed to build a form to input new items in to the products table however i ran in to problems when i tried to tie in the costs table. my aim had been to store cost as an entity for a given time i.e: cost = x where date is from A to B. The idea was to store old cost values encase they were changed so the correct value of an item could be calculated in the future even if the price had been modified.

Anyone know of a guide that could help me here?

Thanks for your time
Back to top
View user's profile Send private message
Super User
Super User

Joined: 07 Jan 2010
Posts: 769

PostPosted: Thu Mar 03, 2011 9:12 pm    Post subject: Re: stock control Tutorial or guides? Reply with quote

Screv wrote:
The idea was to store old cost values in case they were changed so the correct value of an item could be calculated in the future even if the price had been modified.

Welcome Screv Very Happy
...and good job reading the available guides and tutorials! I don't know of any guides that would answer your specific questions because you're starting with a bit of a hybrid that may involve nested SQL and/or macros to properly implement. So maybe let this serve as your guide:

There's a few cases here so you'll need to decide what you want:
Case 1) Would you be happy with a historical look at actual transactions with the proper price applied in all cases?
Case 2) Or do you need to perform analysis using historical pricing perhaps hypothetically such as identifying price trends, etc.?

I ask this because the first case is rather easy, while the second case requires some advanced SQL (or programmatic analysis if you prefer that route). I can probably offer more help with the first case because I've actually implemented it in the case of invoicing using a transactions ledger. The first case doesn't even require a separate PRICES table because the actual price at the moment of each transaction can be stored as a value in the TRANSACTIONS table. Other, permanent data may be derived from FK-PK relationships by simply saving the associated Foreign Keys in the TRANSACTIONS table. This approach breaks with 'database normalization' ideals to some extent, but for good reason.

If you prefer to employ a PRICES table, even for Case 1, you can do so and record price with Timestamp (Timestamp gives the option of intra-day price changes as well) in the PRICES table. In this case, I would create a PRICES table with the following columns:
Price_ID (PK), Price_Timestamp, Product_ID (FK), Price

Notice there's no ending Timestamp for each price because that's assumed by a later Timestamp with a newer price.

Here's the SQL to build the PRICES table, since the Base GUI does not offer to set a default Timestamp value (although that consideration is mute when using macros to automate a Transactions Form input):

Copy & Paste the following SQL code to: Tools > SQL... and press Execute and Close.
Then View > Refresh Tables
"Product_ID" INTEGER,
"Price" DECIMAL(14,4)

Extracting the most recent price for each product is fairly easy, such as necessary for Case 1. For this, I would use two Queries that build on each other as follows, but you can use nested SQL as well. I'm guessing the database engine query optimizer turns this into nested SQL either way...Notice the creation of the MAX_Timestamp alias in the first Query, followed by it's use in the second Query.

Current Timestamps
SELECT MAX( "Price_Timestamp" ) AS "Max_Timestamp", "Product_ID" FROM "Prices" AS "Prices" GROUP BY "Product_ID"

Current Prices
SELECT "Prices"."Price", "Prices"."Price_ID", "Prices"."Product_ID" FROM "Prices" AS "Prices", "Current Timestamps" AS "Current Timestamps" WHERE "Prices"."Price_Timestamp" = "Current Timestamps"."Max_Timestamp"

And finally, when developing Forms to input Transactions, it can be messy trying to filter and update the TRANSACTIONS table with the actual price value. So I recommend using a few lines of macro code to automate these tasks in the Form.

Here's a working example which includes macro code to automate Form input tasks:

That's about as far as I can take you, but that about covers Case1.

Case 2, on the other hand is more involved. Performing historical pricing analysis on a specific date or over date ranges requires advanced SQL (or code). I'm happily to refer you to the SQL experts should that need arise.
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic Forum Index -> 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