Lookups based on a calculation drop down
Client is using FMP12, Windows XP (soon to be W7, and Mac 10.9)
One table ("Chemicals") has chemicals, listed by Formula, and other attributes. The Formula can appear in more than one record, with differing values in other fields.
In another table ("Projects") there is a formula "chooser" field, which displays a drop down list from the Chemicals table. In order to choose the correct iteration of the formula, some of the other field values (from the Chemicals table) need to be known. To enable this, in the Chemicals table, there is a calculation field which combines, in sequence, three significant fields which determine the correct choice for the project. The drop down list in the "chooser" field displays values in the combined calculation field from Chemicals, sorting and viewing first by Formula, so all iterations are displayed together, with the varients to the right (separated by "_").
When the calculated value is selected, three fields are "looked up" from the Chemicals table, pulling in the Formula, and 2 other field values from Chemicals. All good so far.
One thing is that, like normal, as you begin typing in the drop down, FM goes to the nearest match, from which you can scroll up or down if your typing speed isn't 150 words per minute.
Here's the thing - if you paste in an accurate formula, it will not find that formula in the drop down, it just takes the paste, which is not enough to trigger the lookups (which makes sense, since the entire calc value is not entered).
Is there a way to be able to paste the formula in and activate the drop down?
One other thing - by necessity, the "chooser" field is a repeating field.