13 Replies Latest reply on Mar 10, 2012 5:01 PM by ClayHendrix_1

    If Function and Lookup

    ClayHendrix_1

      Title

      If Function and Lookup

      Post

      I am creating a point of sale database for a school cafeteria. It is very much like an invoice database. There are three possible prices for lunch based on a student's meal status. Meal status is stored in the students table. Price is stored in the items table. There are three price fields in the items table (price_1, price_2, and price_3).

      The sales table and sales_lines tables collect the lunch sales information (much like invoices and invoice_lines). I initially created a lookup for the price field on the sales_lines tables from the price field of the items table, then I realized there were three possible prices based on the field in the students table. So, I created an If calculation station, using lookup to return price_1, price_2, or price_3 based on the meal status in the student table. That works just fine. Unless prices are changed in the item table. Then each previous sale_lines record is changed to have the updated price. I wanted the original price from the time the line was created to be saved in the sale_lines table as that was the price on that day, but if the price of lunches is changed, then they are changed in the lines table records for all recrods, not just those created from the price change forward.

      I tried to tell the price field in the sales_lines table to store the data, but it says that is not a valid option.

      Please help if you can.

        • 1. Re: If Function and Lookup
          davidanders

          I am not sure, but could you have a calculated field called PriceToUse  number result using a Case statement

          Case (Student::StudentMode = 01, Price::Price01:
                   Student::StudentMode = 02, Price::Price02:
                   Student::StudentMode = 03, Price::Price03:
                    "Student Mode Blank")

          Then your SalesLinePrice would be a lookup from PriceToUse Field

          David Anders
          The Computer Guy, Seattle

          • 2. Re: If Function and Lookup
            ClayHendrix_1

            David,

            This is what I did. In the SALES_LINES table, I have the unit_price field. It is a calculation. Here is the formula:

            If (STUDENTS::meal_status=1; Lookup(ITEMS::price_1);
            If (STUDENTS::meal_status=2; Lookup(ITEMS::price_2);
            Lookup(ITEMS::price_3)))
            It is does choose the correct price from the ITEMS table. However, if the price of an item is changed in the 
            ITEMS table, the prices change in the existing records of the SALES_ITEMS::unit_price.
            I do not want the second part to occur. The logic is that once the sale has taken place, the price for that items 
            should remain to serve as a history in the SALES_LINES records as the price at the time the record was created.
            Most importantly, the account balance is calculated as a total of the sales minus payments.So, when the price is 
            changed, account balances are incorrectly reported. 
            • 3. Re: If Function and Lookup
              ClayHendrix_1
              /files/53dad4acee/Screen_Shot_2012-03-09_at_4.52.24_PM.png 1127x222
              • 4. Re: If Function and Lookup
                ClayHendrix_1

                I thought that the purpose of Lookup was to just place the value from the looked up field into the record of a related table one time. Since the tables of SALES_LINES and ITEMS are related, if I just put the field from the ITEMS table of unit_price, then it would make sense (in my current understanding) that the unit_price in SALES_LINES would be updated at each change in the unit_price field in the ITEMS table.

                I was using lookup so that the field unit_price field in SALES_LINES would be populated upon creation with the value in the unit_price field from the ITEMS table one time, upon creation, but never again.

                I know that there is a way to do what I am seeking to do. I know that there is an invoicing system that utilizes multiple prices and "locks" the price at the time of invoicing in as the price for that sale from the point forward regardless of whether the price for the item is changed later or not. In fact, that is a very basic concept of invoicing.

                Tongue out

                • 5. Re: If Function and Lookup
                  philmodjunk

                  One option is to define three unit price fields in Sales_Lines and look up all three prices. Then use the calcualtion in Sales_lines to select the looked up unit price that is appropriate.

                  • 6. Re: If Function and Lookup
                    ClayHendrix_1

                    In fact, the desciption field in SALES_LINES is populated using lookup from the description field in ITEMS.

                    I just changed the spelling of a description in ITEMS and the description field in SALES_LINES is not being changed for the records that were created with the original spelling of the description.

                    So, in some cases, it does work as I say, but not in all.

                    I did just realized the the unit_price field in SALES_LINES does not allow the check box on "Do not store calculation results . . ." to be unchecked. This is the error I get when I try to uncheck it. So, for some reason, it is looking up the value each time the record is accessed.

                    What solutions are there to this? What have I done wrong?

                    • 7. Re: If Function and Lookup
                      philmodjunk

                      It should not be a calculation field as that will update all records with every price change. (Even if it were possible to define it as stored, it would still update automatically with every price change.)

                      Either use 3 unit price fields that use looked up value field optionst to copy all three unit prices and the a calculation field that chooses from these three unit prices to compute a total cost for the item or define you unit price field as a field of type number with the calculation as an auto-entered calculation.

                      • 8. Re: If Function and Lookup
                        ClayHendrix_1

                        Thanks, Phil. 

                        I do not really understand this:

                        "Either use 3 unit price fields that use looked up value field optionst to copy all three unit prices and the a calculation field that chooses from these three unit prices to compute a total cost for the item or define you unit price field as a field of type number with the calculation as an auto-entered calculation."

                        • 9. Re: If Function and Lookup
                          philmodjunk

                          Define Unit Price as a field of type Number. Click options to bring up field options and click the auto-enter tab.

                          Click the calculation tab.

                          Using DavidAnders' calculation but update to use the actual field and table occurrence names, enter this:

                          Let ( Trigger = _kp_Item_ID ;
                                   Case (Students::MealStatus = 1; Items::Price_1:
                                              Students::MealStatus = 2; Items::Price_2:
                                             Students::MealStatus = 3; Items::Price_3)
                                 )

                          Clear the "Do not replace existing value" check box.

                          When you select a value in _kp_Item_ID it should trip the calculation to evaluate, using Mealstatus to determine the correct Price to enter as the unit price for the specified item.

                          • 10. Re: If Function and Lookup
                            ClayHendrix_1

                            Phil,

                            Your solution works if I create the record and then go back and modify the _kp_item_id value. It does not work the first time a record is created and _kp_item_id is entered for the first time.

                            • 11. Re: If Function and Lookup
                              philmodjunk

                              It works for me in a test file as long as I am careful to commit the record after selecting a student. (I use a value list to select a student.) Try clicking the layout background before making your first selection in the portal. If that works, you can use a script trigger to automatically commit the record each time you select a student.

                              • 12. Re: If Function and Lookup
                                ClayHendrix_1

                                Phil,

                                Thank you. You are corrent, it does consistently work if you commit the record each time after I select a student. I am not familiar with the term commit the record. So, how would I make a script to do so?

                                • 13. Re: If Function and Lookup
                                  ClayHendrix_1

                                  I got the script to work using commit record onobjectexit. Thank you for that help.