inventory db

[marymi]

I'm in the planning stages of an inventory db. The part I can't figure out is how to design the database as far as changing costs go. If I have a cost associated with an item, how do I handle when the cost changes? I know I need to have an effective date with each cost, but all items won't have the costs changing at the same time.

I appreciate any help or suggestions.

Thanks,
Mary

[cgyrob]

You should have a seperate table which holds your costs and is referenced to the item table.

Items
--------
id
item_name
Desc
etc.

costs
----------
id
items_id
Costs
effective_date
expiry_date

When you need to update a cost you set the expiry_date for the current record and insert a new record in costs for the items_id. This way you will also have historical data for your changing costs. When referencing the costs you just have to select the active record for the item.

[marymi]

Thanks. That makes sense, but I'm a little fuzzy about the details. (Let me preface this by saying I'm reasonably experienced with access, but I'm trying to learn filemaker and would rather develop it in filemaker)

How would I call up the correct cost?

Appreciate your help.

Mary

[cgyrob]

I have never used filemaker but it is a relational db so their is most likely either a sql window or graphic sql designer like in acccess.

If there isn't you will have to find out how to join tables in filemaker, then join the items table to the costs table where the expiry date is null.

Simple sql

Select a.Item_name, b. Costs
From items a, costs b
Where a.id = b.Item_id
and b.expiry_date is null

Sorry I couldn't help with the filemaker syntax, there might be someone on here that knows the product.

[marymi]

Thanks! I didn't think of the is null part!

Regards,
Mary