Can't use value list based on related field

[judyray]

Table A contains investors names

Table B contains various funds

A unique identifier is a name, fund, and account number. This is a calculation field.

I want to fill out a form and I have to choose the correct person and fund.

I thought I could use the unique identifier in a value list, but FM won't let me. It won't store the calculation since it uses values from related fields.

I can make my own unique identifier instead of calculating one, but this seems an unnecessary thing to do, as the computer can calculate the info. So I do I get this to work in a value list?

[-Queue-]

Which field is related? You should probably use a serial instead of name or fund, in order to be truly unique and constant.

[judyray]

Both names and funds are related. I don't mind using a serial number, but the user would not know which one to pick.

I need the calculation which has a result in English so that it can be used as the viewable one while putting in the serial number for the selection.

[transpower]

I think you need a join table, as well: investor_fund. You could have two popups in the join table, allowing you to pick the person and the fund. You could allow new persons and new funds to be added.

[bikergeek]

The value list can be based on the serial number field, but can also display the name field when used in pop-ups. The name field can even be the sort criteria for the list. When selecting a serial from the list, the user will be shown the name of the user associated with each serial, but only the serial will be placed into the target field.

[judyray]

I do have a join table, investors. And I can pick which person and which fund.

It is in another table, when I need the unique investor, that I cannot use the unique calculation as a lookup in the value list. It won't use it because it is calculated and not stored.

[judyray]

While I could use the name field to get the serial number, the name field is not unique because any name can have more than one fund. Also, any fund can have more than one name belonging to it.

That is why I need the calculation of the unique investor, a name and a fund that has an account number. But account numbers are meaningless to the humans.

Also, one company uses the same account number for several different funds, so even the account number is not unique.

So that is why I made the calculation to give me the unique entity. But it won't allow me to use this calculation to do the lookup in the value list.

[judyray]

Problem solved.

I need to create the calculation of the unique investor, a name and a fund that has an account number as an auto-entered calculation during data entry, not as a calculated field.

Once that data is entered, it is unique.

So it will create this unique identifier when entering data in new records.

But, if I click on a field that has a popup value list, and choose the same value, then the unique data is also created at that time, since it thinks I am changing the value. I don't have to do any typing in the old records to eliminate the blanks.