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
The Computer Guy, Seattle
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);
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.
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.
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.
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?
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.
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."
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.
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.
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.
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?
I got the script to work using commit record onobjectexit. Thank you for that help.