7 Replies Latest reply on Jan 31, 2017 6:57 AM by rcbmi1

    Calculation Based on a Portal Record and Possibly a Value List?

    rcbmi1

      Hello all,

       

      Here is what I am trying to accomplish. I would like value list choices to correspond with global fields I have previously defined, in order to calculate results based on those global fields.

       

      Here are the inputs:

      Species should correspond with one of five global fields

      Item Type should correspond with one of four global fields

       

      For example, if I should choose species ALDER (global field value $.50), and item type UPPER (global field value 175%), I would like a calculation to display the multiplicative result $0.87.

       

      I might as well ask for the moon for I'm at it, and say that I would like to evaluate these results in the context of a self-join portal.

       

      Is there any method of accomplishing this, or is there another, easier way?

       

      Thanks,

       

      James

        • 1. Re: Calculation Based on a Portal Record and Possibly a Value List?
          philmodjunk

          Please describe your set up and what you are trying to do in more detail.

           

          A calculation can certainly reference global fields, but the very use global fields here might not be the best approach to use in the first place.

          • 2. Re: Calculation Based on a Portal Record and Possibly a Value List?
            rcbmi1

            Thanks for the reply, Phil.

             

            I am attempting to create a cabinet pricing form so that we can more accurately price out cabinets here at the shop. The cabinets are priced based on the type of wood used, the amount of wood (in lineal ft.), and the type of cabinet part being built. An example might be this:

             

            2 Lineal ft. of Alder for a drawer.

             

            The fields filled would be ALDER for type, DRAWER for item spec, and 2 for quantity.

             

            Alder is rated at 114% of the base wood price, which we'll say is $60 sq/ft., so $68.40 is the value of ALDER.

             

            Drawers are rated at 49% of the base price, which might be 30$, so $14.70.

             

            My calculation would look like this:

             

            (type + item spec) * quantity

            (68.40 + 14.70) * 2

             

            RESULT is the number I want. There might be 6 to 12 of these calculations, as different woods and cabinet boxes can be used in one quote.

             

            I hope that helps better explain myself. I am trying to adapt this from a much older version which ran very poorly.

             

            James

            • 3. Re: Calculation Based on a Portal Record and Possibly a Value List?
              philmodjunk

              It's the use of global fields that I question.

               

              I should choose species ALDER (global field value $.50),

               

              What happens to the value in the global field if a different species is selected?

               

              and item type UPPER (global field value 175%),

               

              And the same question here. What happens to your global field value of 1.75 if a value other than "upper" is selected as the item type?

               

              As much as possible, you want to look up pricing information from a table so that you can manage rate and unit price values by editing records in that table. You want to look them up (copy them) from that table so that future price/rate changes do not affect past transactions and the totals computed from them.

               

              but I'm a bit confused over your use of global field values here.

              • 4. Re: Calculation Based on a Portal Record and Possibly a Value List?
                rcbmi1

                I could base them off a table, I just thought I would use global fields since the rates hardly ever change. Before this, the values were listed within a record, so we were physically typing in every price every time we wanted to estimate something. I thought the use of global fields would be a much more elegant and useful solution. Would it be easier to simply create another table and list the current values there?

                 

                James

                • 5. Re: Calculation Based on a Portal Record and Possibly a Value List?
                  rcbmi1

                  And to answer your questions, each species and item type has its own global modifier.

                  • 6. Re: Calculation Based on a Portal Record and Possibly a Value List?
                    philmodjunk

                    There are right ways and wrong ways to use global fields.

                     

                    Is this a solution that will be hosted with more than one user creating this records?

                     

                    Instead of a "global modifier" for each species, you need a table of species (Or species categories if groups of the same are to have the same modifier) so that selecting a species matches to a specific record in this table so that you can look up a modifier value to apply to the base unit price.

                     

                    I'm picturing this calculation as what you are doing:

                     

                    Qty (in board feet) X Base Price (same for all) X Species Specific Modifier

                     

                    So if you had 4 board feet of Laurel, it would work as 4 X 0.5 X 1.75 to compute the price for that value.

                     

                    You enter species and the Quantity, the database uses the species to look up the modifier and the base price can be treated as a global value--but if you will host your solution, you'll need to put some thought into how you manage that global value.

                     

                    In the above calculation, Qty would be a number field that you edit to specify the Quantity. Base Price would be a number field that auto-enters your global base price amount using a simple auto-enter calculation where you simply specify the name of your global field. The modifier would use either the looked up value or auto-enter calculation option to copy over the correct modifier value for the specified species of lumber.

                     

                    Notes on Global fields:

                    If you have a hosted database and edit the value of a global field, you are the only user that can see the new value you have entered into that field. Other users will see either the original value or some value that they have entered into the field. If you then close the database file, the global field reverts back to its original value. Thus, in a hosted database, you either take the file down off the server and open it directly with FileMaker Pro in order to make a change that "sticks", or you set up a record in a table with a non-global field for this purpose and you use a start up script to load the global with the value form the nonglobal field each time the file is opened. You can then  modify the value of the global by editing the nonglobal field used to initialize it.

                    • 7. Re: Calculation Based on a Portal Record and Possibly a Value List?
                      rcbmi1

                      Thanks Phil, that makes a lot of sense! This is a hosted database, so I will create a table with the values and then look them up.

                       

                      James