Trouble with seemingly simple relationship.

[GarrettC]

It is clear that I still do not get the relationship thing (or I don't get the way FileMaker Pro does them).

It is a simple finances database. It has a primary expenses table and 2 other tables that are connected to it: vendors and accounts. Everything works ok except the relationship between the expenses and accounts tables.

When a new expense is created an account has to be selected from a popup menu (accounts table) this indicates where the money came from for the expense. The account numbers are not very intuitive so I included the name of the institution as the second field in the popup, this is where the trouble comes in.

Some of the accounts are from the same institution but have unique account numbers, despite this, the popup will not show the different accounts from the same institution. If that name matches it will only show one from that institution. Conversely, if I switch the popup to use the account type (like credit card) again it will only show one of the credit card accounts.

I can't believe that there is a problem with having name similarities as part of the data in the record, this is a common situation. I am assuming that I have a problem with my structure or some other setting.

If someone take a look at the file I included I would greatly appreciate it. Thanks much in advance.

FINANCES_1.zip

[bruceR]

Your file is working correctly. You need unique names for your accounts.

[SlimJim]

The problem is the Value List. FMP makes a value list out of the sorted field in these two-field situations so if there is repeated data in the second field then that will only show up once and attached to the first ID. In a certain sense it is logical. You show the second field in order to enable you to identify which first field value you are going to use. If your second field does not do that then there is a problem - you can resolve it be sorting on the first field but that makes data entry harder as the institutions do not show up in alphabetical order.

One solution, no doubt there are many others, is to make a field which contains the institution name and some other data which will define the account number and use that as the second field. (In FMP8 it is possible to show only the second field in this situation and then you can make a calculated field of Institution & AccountID which will do the job).

Another alternative in your situation is to make data entry a two step affair. First choose the institution from a Value List and then choose the account number from a Value List of numbers related to that Institution. For this you would need to make a join by Institution from your Accounts Table to a second TO of the Accounts table.

[GarrettC]

SlimJim, thanks for your excellent reply!!!

It is folks like you who make forums one of the greatest benefits of all things Internet.

For now I will probably go with the first solution and when I move to 8 rejiger to another method.

Thanks again.