Lookup and relationship

[JustJoker]

Hello!

I can't get a lookup matter. The situation:

I've got 2 tables -- "List of Contracts" and "Trades". Those tables are related by the field ID Contract which is in both tables. Any Contract relates to several Trades.

The table List of Contracts has a calculated field "Multiplier" that I want to use as a lookup for the field Multiplier2 in the Trades table.

I think that I established lookup options properly (not too many options there) but still can't get any value in Multiplier2 field.

Would be grateful for any suggestions.

Thanks in advance,

Serge

Contract.zip

[LaRetta]

Hi Serge,

You have an auto-enter serial on ID_Contracts in your Trades table. It needs to be standard number which is filled in when you select the Contact ID in Trades. You will then have good, related data and your lookup in Contracts will then work. :^)

[JustJoker]

Thank you, LaRetta!

I've stupidly forgot to remove auto serial after copy. Solved the issue by establishing relationship Contract_Short Name -- Contract and using drop down list (still can't figure out how to make it using Contract_ID).

Serge

[LaRetta]

I would highly suggest that you use the IDs you have established in your table for that exact purpose, Serge. If you do not, you will run into problems. So assuming you've put it back based upon the Contract ID in both tables, here is how to solve your ID selection issue ...

Change your Contracts value list to selecting values from field and select the ID_Contract from List of Contracts in the left pane and then select the Contract Name Full in the right pane. Specify All values. Then below, check 'show values only from second field' and uncheck the 'resort by ...'

Now go to your Tasks::ID_Contract field and Inspector > Data > Control Style, select the Contracts value list as pop-up. We use pop-up because of two things ... 1) it does not allow free typing in the field which restricts from someone typing an invalid number without properly selecting the Contract and 2) when you leave the field, it displays the Contract Name instead of just the number (so in your example, you will now need to expand the field size now so you can read the entire line).

I also suggest that you remove the spaces from your field names. If you wish, use underscore. Spaces will cause problems if you ever try to interact with MySQL or many other programs. Better to get into the habit of dropping spaces now.