relationships - going crazy

[nickOS]

I am working on a FM7 database. The master db is the one where all records are entered. The child db is almost identical to the first one with few minor exceptions. Child db is used for inventory and deliveries.When an item is deducted ( delivered ) from the child, the master must remain un altered. I am working on this for almost 2 weeks and cannot figure out what am I doing wrong, to start with, records from master do not dispay on child or vv.

Heeeelp please !!!

[stanley]

Nick:

Welcome to the Forums.

First off, it sounds like you may not have a firm grasp of the underlying concepts involved in relational databases. I say that because your parent and child tables shouldn't have much identical information in them.

To help you out, we'll need some more specific information, but just for starters, let me give you a basic conceptual notion of how an inventory system can be designed.

You'll have a table of items that you stock - I'll call it ITEMS. This will have some basic fields:

nSerialNumber - an auto-entered serial number

tItemName - the name of the item

nItemPrice - the price you charge for the item

nNumberInStock - the amount of the item you have in stock.

You'll have a child table of transactions - I'll call it TRANSACTIONS. This is where you enter your sales, and would have these fields:

nSerialNumber - an auto-entered serial number

nRelatedItemNumber - the serial number of the parent record

nNumberOfItems - the number of items sold/delivered

tSaleOrDelivery - what type of transaction this is

In your relationships graph, connect ITEMS::nSerialNumber to TRANSACTIONS::nRelatedItemNumber.

You'd obviously have tons of other fields in each table. In this example, you'd build a scripted method where, when an item is added to an invoice (which would be yet another table) or is added/sold, a record would be created in TRANSACTIONS, and the serial number for the item in question (from ITEMS) would be entered into the nRelatedItemNumber field in TRANSACTIONS. In the TRANSACTIONS layout, you can show related data from ITEMS (such as the item name & price). In the ITEMS layout, you can place a portal based on your relationship to TRANSACTIONS, and show data from every transaction for that item.

Note that your script would also have to add/deduct the TRANSACTIONS::nNumberOfItems to/from ITEMS::nNumberInStock.

Hope that gives you some help.

-Stanley

[nickOS]

Stanley,

thanks for your reply. However, I cant stop feeling helpless, even after your example.

I used your suggestion and made an identical example, same fields same everything. Two databases, one called ITEMS and the other one TRANSACTIONS. Linked both in the relationships graph, but did not work for me.

I have tried for over 4 hours to make it work but to no avail. What am doing wrong ?

[nickOS]

Hello Stanley,

I got it. After playing with your example again this morning I made it work. My problem was that I was not making the specific reference to the related item number of your example, and this was my error all along.

I appreciate your help, hope that all goes smooth now.

Thanks again

Nick

[nickOS]

Another question concerning relationships. All the examples I have seen - so far - evolve around a unique ID number that is referenced in the relationships. In my case I cannot use a unique number - such as a serial no. I must use one text field and one numeric field, which are unique for each of my records. Can relationships be perfected with two fields such as the above ?

[Vaughan]

Yes, but a serial number is safer.

If any of the data in the other two fields is changed than all the related records will be lost.