9 Replies Latest reply on Feb 9, 2011 8:18 AM by philmodjunk

    Calculation and Value List Issues



      Calculation and Value List Issues


      Alright all, I think I have cleaned up my general problems with my value lists (thanks to PhilJunkMod). However, I have a calculation that should change after the second value list is validated / modified and it currently doesn't. What occurs is when you select the first "category" drop down list and select one, FM passes the first dynamic value from that category into your next (or dynamic) value list. At this point the calculation fires (which it should do); however, if I change value list 2 to anything else, the calculation never changes. Anyone have some tips? At the link below is the DB and if you goto the Est Main layout then modify the material category and material selection you will see what I am talking about under Selling S/m. Thanks all!


        • 1. Re: Calculation and Value List Issues

          Tracing backwards, Selling Price M Qty is defined as: Round(Cost Qty 1 / (1-margin1);2)

          margin 1 is a number field with this auto-entered calculation: .3

          Cost Qty 1 is a calculation field defined as: Subtotal Qty 1 / (quantity1/1000)

          Subtotal Qty 1 is defined as:

          Bindery Bag Cost Qty1 + Bindery Band Cost Qty1 + Bindery Wrap Cost Qty 1 + Box Inners  Cost Qty1 + Box Outers Cost Qty1 + Core Costs Qty 1 + Encoding Costs Qty1 + (Foil Die New Cost * If(Foil Die New="yes";1;0)*If(Foil Die Press Sep="yes";0;1)) + Foil Costs Qty 1 + (Foil Custom Cost*If(Foil Custom Sep Cost="yes";0;1)) + Foil Off Press Cost Qty1 + (Foil Off Press Die Cost*If(Foil Off Press Dies Sep="yes";0;1)) + (freightCost1*If(freightCostSep="yes";0;1)) + Graphic Proof Costs + Graphics Digital Plate Cost + Inlay RFID Costs Qty 1 + Lam Costs Qty 1 + Magnetic Ink Costs Qty 1 + Magnetic Stripe Total Cost Qty 1 + Magnetic Tape Costs Qty 1 + Material Total Cost Qty 1 + Plate Cost Total + Plate Film Cost Total + Press Cost Qty 1 +  Press Die Run Cost Qty 1 + (Press Die cost Qty 1*If(Press Die New="yes";1;0)*If(Press Die Sep Cost="yes";0;1)) + RFID Press Cost Qty 1 + (Tool Special Cost Qty1* If(Tool Special Sep="yes";0;1))

          Yikes! I'll assume for the moment none of these fields are the issue and check quantity1.

          Nope Qty 1 just auto-enters a 0.

          OK, switching tacts. Let's create a Database Design Report and search for references to your two drop down controlled fields

          I ran out of time before I traced all those fields to see which might need to change when a new material is selected. Most likely culprit would be either a field or fields that use an auto-entered calculation, but still have the "Do not replace existing value..." option selected to prevent changes in this material selction from changing the value auto-entered into that/those fields.


          • 2. Re: Calculation and Value List Issues

            Hmm, thanks Phil. I am piecing my way through them right now. The fields work correctly before I went to a dynamic value list, could this have any affect on it?

            • 3. Re: Calculation and Value List Issues

              It depends. Straight forward calculation fields should not have any issues. If you have an auto-entered calculation that references a field from another record, however, it will not update when the value in the other record is modified or another field is updated such that the relationship to this other field's record now points to a different record. That's the theory I was exploring when I found such a complex tangle of calculations here.

              I do think you should take the time to re-think your basic structure here. You have a truckload of individual fields where a table of related records might serve to greatly reduce both the number of fields defined in your record and also the complexity of your calculations.

              • 4. Re: Calculation and Value List Issues

                Phil I love yah man, I am screencapping the hell out of this. What you see is a project that fell in my lap and I am quite unhappy with. I have tried a couple of rewrites and my latest was cut short really because they want the piece to move forward. My recent plan was to break the estimate up into respective tables (mat table, die table, etc) but was running into an overal problem with linking layout to layout (plan was through _fk_estimate keys but not sure how to force the next layout to show the correct record). In addition, I don't know beyond a script to perform a "duplicate record" in that form either. Maybe you have some ideas, or some answers to those immediate issues. Thanks for the help though!

                • 5. Re: Calculation and Value List Issues

                  There are two ways to pull up a matching record when you change layouts:

                  Set Variable [$ID ; value: Yourtable::_fk_estimate]
                  Go To Layout [//specify layout]
                  Enter Find Mode []
                  Set Field [TableOfNewLayotu::_fk_estimate ; $ID]
                  Set Error Capture [on]
                  Perform Find[]

                  Go To Related Record can also be used to make this a one line script if there is a valid relationship between the two table occurrences (The relationship from the Table Occurrence for Layout 1 to a Table Occurrence of the table referred to in Layout 2.)

                  • 6. Re: Calculation and Value List Issues

                    Phil, while we are talking about scripts, is their way to sort a value list through a script? Never seen this before and am intrigued if the possibilites are there. The list wouldn't be dynamic of course, you would just the script to omit useless options in when using a specific field. Thanks for all the help!

                    • 7. Re: Calculation and Value List Issues

                      Sorting or Filtering? You say sorting at first but then talk about omitting values which isn't sorting that's filtering.

                      Conditional Value lists are what I think you are describing. If you select a value in one field, that field then controls what options are listed in a second field's value list--typically by using a relationship that reduces the list of possible values to just those that are relevant to the category specified in that first field.

                      Custom Value List? (tutorial)

                      http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list (KnowledgeBase Article)

                      • 8. Re: Calculation and Value List Issues

                        Sort of phil...more like I knew that their was a category of material on the list that needed to be show on one layout and a different type in another layout. All of the material would be composed in the material table, but the user is only sees the values that are "default" to the layout. It is dynamic in nature, but not really. Does that help?

                        • 9. Re: Calculation and Value List Issues

                          Different layouts customized to different subsets of materials records?

                          That's simply a matter of performing appropriate scripts to find records or constrain found sets so that only records from a category appropriate to that layout are in the current found set.