Please describe your relationships and exactly how you've set up this look up (There are several ways this could have been set up). Maybe then someone will be able to spot something that explains this.
I have established a relationship between "Billing:Material" and "Material Charge:Abbreviation"
"Billing:Material" is set to copy information (look up) from "Material Charge:Material"
"Billing:Material Cost" is set to copy information (look up) from "Material Charge:Cost"
I have some screen shots, but not sure how to upload them. If I get that option after I click Post Answer they will be included. If not, I will need further instructions as to how to do that.
Check your Material and Abbreviation fields for characters such as spaces, * or . that are in one field and not the other. I don't know which version introduced the change in indexing ( I skipped from 5.5 to 10, 11 ), but I know older versions ignored some characters when text was used to match records that the newer versions do not. Make sure that they are exactly character for character the same.
If this is the problem, you can repair things with a replace field contents operation.
I'm not sure I follow.
The main table I work with is "Billing". This contains fields for the Department name, date, Job Description, etc., as well as the fields for "Material", "Number Used", "Cost", and "Total Material Charge"
In the "Material List" table, I have 3 fields. For my example, I have "w" in the "Abbreviation" field, "8 1/2 x 11 20# white" in the "Material" field, and ".0056" in the "Cost" field. I have over 400 records in this table, each corresponding to different paper or supplies.
When I enter "w" in the "Billing:Material" field, it will find the first record in the "Material List" table that has "w" in the "Abbreviation" field. It will then copy the information from the "Material List:Material" field in this same record, (in this case "8 1/2 x 11 20# white"), and it should copy the information from "Material:Cost" field in that same record (in this case ".0056)
Since, in creating these relationships and lookups, I don't actually type in the name of the fields or tables I want to refer to, I don't know how they could have extra characters. And the lookup has always had to find the exact abbreviation I entered, such as "w" for "8 1/2 x 11 20# white", or "xcanary" for 8 1/2 x 11 110# Canary index". Some of the abreviations are a little more involved than this, but it's hard to mispell "w".
I can open this file in Filemaker 7 (I reinstalled it so I could test it), and it works just like it should. Although, I just discovered, when I open it FM7, it doesn't work until I redo the lookup for "Billing:Material Cost" (selecting the same thing as already appears to be selected). That doesn't help in FM11.
I was thinking more in terms of there being a "w." in the abbreviation field which would match to "w" in the Billing::Material field in older versions of filemaker but not in more recent versions. That doesn't seem to be the case here. If both fields use the same relationship, then scratch that last post. That issue would affect both fields that look up values.
I'd take a careful look at each auto-enter detail that is specified for the two fields to see what difference might exist for the two fields since one works and the other doesn't. I'd also check field types. Since each of your examples show text is being copied, make sure your looked value fields are also text fields.
Finally, if all else fails, you might try reindexing the fields just to make sure nothing is out of kilter in the database indexes. Start by turning indexing off and back on for the Billing::Material field. (Double click the field definition, click storage tab, turn indexing off; exit manage database, return to manage database and turn indexing back on.)
Still not working.
Is there a different way to accomplish what I want?
btw, I noticed when I set both "Billing:Material" and "Billing:Material Cost" to lookup from the same table, neither works. When I set them to look up from separate tables (I duplicated the "Material List" table), both work, though I still have enter the abbreviation in both fields. Don't know if that helps or not.
I'm really suprised this worked at all in filemaker 7. I suspect it worked only if you defined the fields in the right order. The problem that occurs is that the change in billing::Material's value (replacing w with 8 1/2 x 11 20# white) breaks the link to your Material List table before it can look up the cost. I'd add a new field to Billing, Abbreviation, and use it to link to Abbreviation in the Material List table. Then you can enter or select "W" in this new field and both values should be looked up.
Success! I suspect it worked in FM7 because I didn't know it wasn't suppposed to. I guess ignorance works as a charm for only so long, though. Thanks for your help.