11 Replies Latest reply on Apr 22, 2011 12:14 PM by BradenDouglass

    Drill Down Choice Menu and Calculations

    BradenDouglass

      Title

      Drill Down Choice Menu and Calculations

      Post

      Alright everyone, I know I have asked this perhaps (in some form) before but I didn't find a solution before. What I have is a simple 2 step drill down selection process. When you select the first item it prepopulates the second selection field with the first value of the "new (drilled down) list." This populated value fires, allowing my calculations to occur, lookup values, etc and generally works. When you go to change that pre-populated value to something else, none of the calculations change. If you have values being pulled from the table that is supporting the drill down, nothing changes. If I change the fields from reference to lookup, things function (not calculations because they originally point to the reference table, where lookups have focus on the current table). Any help would be amazing, you all rock, ciao.

        • 1. Re: Drill Down Choice Menu and Calculations
          philmodjunk

          The calculations should update unless they are auto-enter calculations.

          • 2. Re: Drill Down Choice Menu and Calculations
            philmodjunk

            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.)

            • 3. Re: Drill Down Choice Menu and Calculations
              BradenDouglass

              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.

              • 4. Re: Drill Down Choice Menu and Calculations
                philmodjunk

                 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.)

                • 5. Re: Drill Down Choice Menu and Calculations
                  BradenDouglass

                  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

                  http://cl.ly/6BWC

                  • 6. Re: Drill Down Choice Menu and Calculations
                    philmodjunk

                    There are problems with your relationship.

                    You have this relationship:

                    Estimates-----<MaterialCategory>-----Material

                    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: 

                    Tutorial: What are Table Occurrences?

                    • 7. Re: Drill Down Choice Menu and Calculations
                      BradenDouglass

                      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?

                      • 8. Re: Drill Down Choice Menu and Calculations
                        philmodjunk
                        1. Enter layout mode.
                        2. Double click the material::__kp_MaterialID field to the right of the pop up menu. (looks like: ::__kp_MaterialID)
                        3. In the specify field dialog, select Material_SELECTION from the drop down at the top of the dialogl.
                        4. Click OK.
                        5. Return to browse and notice the change in behavior. (This is exactly what I did when analyzing your file.)
                        • 9. Re: Drill Down Choice Menu and Calculations
                          BradenDouglass

                          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)?

                          • 10. Re: Drill Down Choice Menu and Calculations
                            philmodjunk

                            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.)

                            • 11. Re: Drill Down Choice Menu and Calculations
                              BradenDouglass

                              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.