Jump to content

relationships - going crazy


This topic is 6668 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 !!!

Link to comment
Share on other sites


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.


Link to comment
Share on other sites


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 ?

Link to comment
Share on other sites

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


Link to comment
Share on other sites

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 ?

Link to comment
Share on other sites

This topic is 6668 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.