Troubling Relationships

Recommended Posts

Hello! I have just upgraded from FM5 to FM8.5Advanced and whoah....it's crazy. I am having a hard time wrapping my brain around the table situation and all these new relationships.

My problem is very simple: I have an inventory database that contains the item code, item description and item price. I have my mainframe database which I would like to display these values when the user chooses an item description. I created a relationship between the mainframe item description and the inventory database item description. I then told the item code and item amount fields in the mainframe db to lookup the values from the inventory db. In addition, I also made the mainframe item description field a drop-down menu of the value list I'd created of the item descriptions so that when someone tries to select an item description they won't mis-type anything.

The layout is based off of the table containing my item descrip, code and amt in the mainframe db.

As far as I know, this should be working. I have tried deleting everything and starting over to no avail. Please help! If I am doing something wrong please tell me. I'm having the hardest time with these tables. it's just not clicking for me, so if anybody has a good way of explaining it, I would greatly appreciate it. Thank you for any help you can give. Sorry for the long post.

Share this post

Link to post

Share on other sites

Usually relationships are between the ID fields (or codes, if that's the best you've got). This would a unique value in its own table. It would not be between two "description" fields, or any long user-entered data. So it seems to me that you've got the fields doing the opposite of what you'd normally do.

I'm also confused by your term "mainframe database." Since you're not talking about FileMaker 9 connecting to an SQL database, I have no real idea what you mean; unless you mean that you're connected to a "product" file/or/table from an "inventory" file/or/table, both FileMaker.

I think your problem is using the description field instead of the item code field in the relationship. People would choose a code from a value list, which would also "show values from a 2nd field," the description field.

Item price and Item description could then be lookup fields based on the relationship. Or, only Item price would be a lookup, description would just be shown on the layout as a related field; the logic being that description is not needed as a "historical" value, it can just reference whatever it is in its main table.