8 Replies Latest reply on Apr 21, 2009 3:05 PM by donjuancarlos_1

    calculation that performs a lookup



      calculation that performs a lookup


      I have a quotes/quote line items/inventory database setup.  Most of the time, I want the quote line items to perform an autolookup on the price.  However, in a couple of situations, I want the database to look up the price and then perfrom a calculation on it, based on previous input from the user. 


      I'm figuring I need to perform a calculation with a lookup, but don't know what the syntax would be.

        • 1. Re: calculation that performs a lookup



          This project is getting really familiar :smileywink:


          Let's try to clarify what you want first. 


          "The user inputs a value and that value determines what data gets looked up and used in a calculation."

          That sounds like what you are already doing to build your quote report. The user selects 5 Windows with Catalog number "x2345" and the system looks up the unit price, multiplies it by 5 and displays the results, which are then included in the calculation that provides a total estimated cost. If the user had selected Window "x5431" instead, a different unit price would be looked up.


          If that was what you wanted, you wouldn't be posting to the forum since you already do that. Give us an example of what you want to do so we can better understand the issue.



          • 2. Re: calculation that performs a lookup

            Ha ha, yeah, this is my first crack at FM10 (upgrade from 5.5), and I've been dumped in at the deep end.  :P


            I have one "install cost" field. The unit cost for this field is going to be based on both a user-selected base cost (Normal, Special circumstance X, Engineering requirement Y, etc.), and previous entries by the user (Amount of insulation, type of insulation, liner color, no liner, etc).  So my idea is to have a calculation for the line item field that adds the base cost with all extras. 


            For example, the user selects Normal, which has a unit price of         1.60


            Earlier, the user also selected R30 insulation                                  + .20

                                                    Painted Liner                                    + .15

                               Therefore, the total unit price is:                              1.95


            I  put all of the items in my inventory database, because i need an easy central location for non-database programmers to be able to change the price. So, is this doable ? 

            • 3. Re: calculation that performs a lookup

              Ok, now that's an interesting challenge.


              I am assuming that you are looking at a single line item record whose unit price can be modified by user input.


              I'm looking at two approaches and I'm not thrilled about either of them:


              You could establish multiple unit prices for each possible combination. You then design a relationship that either matches multiple pairs of fields or uses a calcluation to derive a single unique value for each possible combination. The problem here is that you could have a very large number of possible combinations for each item in your price catalog.


              You could instead set up separate look up fields for each price component. Your unit price field then becomes a calculation that adds them up. The challenge here is that you could end up with hundreds of special use fields and supporting relationships if you can't find a way to generalize the process. This might or might not be practical depending on your business model.



              • 4. Re: calculation that performs a lookup

                 I am assuming that you are looking at a single line item record whose unit price can be modified by user input.




                Approach # 1:  Hmm, there would be a lot of entries.  And if any price ever changed, it would be murder for someone to go through and find all the places where changes needed to be made.


                Approach # 2:  Along this line, I suppose I could split the Install Cost line in two.  One line would be the base price selection and the other would be the calculation of the addons.  If I did this, then all lookups for line item unit costs would become calculations, which I don't know how to do (I tried using the lookup command and was unable to get it working.) And for the second line, I would have an IF statement that adds together all of the extra unit costs. I could probably get away with not adding the two together--the total at the bottom would suffice.  I just don't know how to do the lookup in a calculation thingie.

                • 5. Re: calculation that performs a lookup

                  What you really need is a "lineitems" table for your line items. Unfortunately, that would create problems when you set up your layout, because you can't put a portal inside a portal.


                  What I'm suggesting for option 2 is this:

                  In Catalog, define several number fields

                  PriceComp1, PriceComp2, PriceComp3.....


                  Define matching fields in LineItems and set them each to lookup a different Price component field.


                  Your unit price calculation then becomes (and this is messy and if someone has a better idea, please chime in!)


                  If("comp1 is selected", LineItems:: Pricecomp1 , 0 ) +

                  If("comp2 is selected", LineItems:: Pricecomp2 , 0 ) +

                  If("comp3 is selected", .... etc.


                  You'll have to figure out the expression to put in place of each quoted "comp" clause.


                  With Option 1, perhaps the following refinement will work, If you need only a half dozen or less price components for any one line item.


                  Create one catalog record for each price component for each item.

                  You see something like this in your table:

                  Description (text)

                  ComponentName (text)

                  CatalogID (can't be a serial number now, it is no longer a unique primary key)

                  ItemId (auto entered serial number)

                  Price (number)

                  CompKey (number must be a value 1, 2, 3 or....)


                  You'll need to add fields in LineItems:

                  CompKey1(calculation returning 1)

                  CompKey2(calculation returning 2)

                  CompKey3(calculation returning 3)

                  and so forth


                  For each of these, create number fields:






                  Now it gets messy in a whole new way:


                  Create a relationship for each price component matching two pairs of fields (Catalog::catalogID = LineItems::CatalogID AND Catalog::CompKey = CompKey1)

                  Define your Comp1, Comp2, Comp3 keys to lookup Catalog:: Price each based on their own relationship.


                  LineItems::UnitPrice then becomes a calculation Comp1 + Comp2 + Comp3 + .....


                  You would manage your prices in catalog by pulling up all records with the same CatalogID. The ComponentName field would help the user tell what each price component represents. Hmm, to refine the idea further, create yet another table where one record=one catalog item and use it to generate catalogID and store the Description text. A portal from a layout that uses this table could list all the Price Components for you...

                  • 6. Re: calculation that performs a lookup

                    Okay, whew, that took awhile to digest. So if I am to do that (Option 2), how do I specify the LineItems::UnitPrice as a calculation as opposed to a lookup, when Unit Price is a lookup field? (i.e. do I change it to a calculation field and then perform lookups via a calculation? How?)  Or do I populate the field and change it later with a script?


                    Also, what is the cost of having all of these calculation fields on every line item?  It seems like it could affect the performance of my database over time.

                    • 7. Re: calculation that performs a lookup

                      I don't think you've fully digested the post yet! :smileywink:


                      With option 2, LineItems::UnitPrice is a calculation field that returns a number--instead of a looked up number field.


                      Look at each field in the expression for LineItems::UnitPrice. Each field is a separate looked up field that copies a different price component from the Catalog table into the same record of LineItems. These looked up values are then added up to give you a unit price.

                      • 8. Re: calculation that performs a lookup
                           I knew that, just testing to see if you are awake.:smileyvery-happy:  They need an "embarassed" smiley.