The calculations should update unless they are auto-enter calculations.
I'm guessing that this is the situation. You have two or more values in different fields in the related table and an auto-enter calculation uses these values to compute and store a value. You don't want a calculation field because you do not want changes made in the look up table to change the values shown in this field or you need a stored/indexed field for other reasons.
If I'm right, here's how to fix it:
I'll use simple fields/values for this example, but you should be able to substitute your own for mine as needed:
Say you have field1 and field2 in a table called LookupTable.
Your auto-enter calculation is something like this: LookupTable::Field1*LocalField + LookupTable::Field2
If so, define two new fields in your main table: LookedUpField1, LookedUpField2 and change your number field with an auto-entered calculation into a calculation field that returns text, using this expression: LookedUpField1*LocalField + LookedUpField2.
Now your calcs will update when a new value is selected, but changes to field1 and field2 in LookupTable will not affect the value shown in the calculaiton field. (And it can be a stored, indexed calculation field if that's what you needed.)
Ok Phil, I will give this a whirl. However, if I pipe a strait reference field into the layout, why doesn't it change when I flip through records (second drill down)? I guess I am missing why FileMaker doesn't see this as this record now looking at a different reference record? Confused.
if I pipe a strait reference field into the layout, why doesn't it change when I flip through records
It can and does update (Do that all the time in my systems). If it doesn't in yours, you'll need to check the relationship details that control the reference. (That's why my last response is in terms of an auto-entered calculation as this is the only case I can think of where you don't see the automatic changes you describe.)
Phil, I have attached the DB. It opens (user phil, pass phil) in the correct layout. If you look at the very top of the "body" at Material type and Material Select you will see the issue I am having. Beside the select tab is a numerical number that is the id from the material table. In my mind, if things were working, as you selected different materials this would change to reflect the current material. What do you think? Thank you
There are problems with your relationship.
You have this relationship:
Estimates::matTypeSelection = MaterialCategory::__kp_MaterialCatID
MaterialCategory::__kp_MaterialCatID = Material::_kf_MaterialCatID
Your Drop down list enters values from Material::__kp_MaterialID into the Mat_TypeSelection_Mat field. Since this field is not part of the above relationships (Which don't look right if MaterialCategory is supposed to be a Join table.), changing its value does not have any affect on what related record displays the value in the Material::__kp_MaterialID field.
If you replaced this field with Material_SELECTION::__kp_MaterialID, you'll see that it updates with each selection of different values in the pop up menu.
Everything on your layouts, scripts and calculations are ultimately controlled by the Table Occurrence boxes in Manage | Database | Relationships and the relationships that they define. If you haven't already done so, you may want to read this thread on Table Occurrences:
your quote "If you replace this field with Material_SELECTIION..." on the second to last line is suspect to me. As I am sitting here, I can click on the matTypeSelection_Mat field and it IS sorting via the first field yet nothing is updated. When I change this field...which you didn't say how (via the actual field so it now points at the mat_SELECTION::_kp_MaterialID or in the value list) it gives me zero values. IF I change the >VALUE LIST< from reading the material::_kp_id to the material_SELECTION::_kp_id I get no values.
This is what I believe you are trying to get me to do no?
- Enter layout mode.
- Double click the material::__kp_MaterialID field to the right of the pop up menu. (looks like: ::__kp_MaterialID)
- In the specify field dialog, select Material_SELECTION from the drop down at the top of the dialogl.
- Click OK.
- Return to browse and notice the change in behavior. (This is exactly what I did when analyzing your file.)
Ok I understand that. But, why does it modify the lower values...the calculations from the default record when you change the Material Category but won't when you drill down through the actual material list (IE navigating away from that first or default value)?
Yes, if these are fields of type calculation and not auto-entered calculations. (Also double check "context" drop down at top of specify calculation dialog to make sure table occurrence name here matches the table occurence name specified in Layout Setup | Show Records From.)
Thank you for your help Phil. Ultimatly we had everything working correctly, you just pushed me to the solution. I needed to flip the material strait table and the SELECTION table. Now everything work great.