Trouble Making Relationship

[zebex]

Im working on a stock inventory database. Basically, with three (3) tables namely:

1. Stock

2. Stockline

3. Requisition

Im having some problems in connecting the relationships for the 3 tables. I was hoping that in stockline I can pull-out some datas from stock in organizing my inventory. For requisition, I want some data to be pulled out from stockline and stock using drop-down list.

If someone can guide me on how to achieve this. I have provided my file open for your scrunity.

Inventory.zip

[Søren Dyhr]

Since the level is an unstored value based on what is pulled via the transaction lines, can't it be part of a drop down list since it has to be indexed values to make it display correctly.

This can be done by having the present stocklevel stored in each transaction ... somewhere along the line of this:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000333

But on the other hand could related stored values be shown in dynamic value lists, via what we call Ugo's method ... by combining these two approaches should you be able to get what you're after.

But you need to be cautioned here, looking up the last record won't allow you to delete arbitrary transactions. They would need counter transaction to get nulled.

If this can be accepted are popups/dropdowns not a way your stocklevels can be shown ahead of the selection, so a cartesian portal might be more up to the task?

--sd

[comment]

I was hoping that in stockline I can pull-out some datas from stock in organizing my inventory. For requisition, I want some data to be pulled out from stockline and stock using drop-down list.

"some data" is a somewhat vague request. In general, I don't see why you couldn't either place related fields on the layout or perform a lookup, as appropriate.

Looking at your Stock table, it's not clear to me which field is an item name/description and which fields are categories to which the item belongs. I think that your value list needs to be defined to show the item's unique ID and name/description.