Then you should not use an auto-enter calculation for this. Auto-entered calcuations do not automatically update when data referenced from another table/record changes. A field of type calculation will be automatically unstored when it references related data, but will also update automatically any time that related data updates.
how can I pull the value of the last record in that calculation field?
do you have any example?
You'll need to be more specific about what you mean by "pull the value of the last record".
Hi Phil, I just find out that I'm trying to get the price of the combined parts in Products table.
here is the situation. I've Product table which has all the products including combined parts and individuals parts. if it is individual parts(only one part not assemblies(more than one part)), the calculation will get the value from Transaction table where my staff input price. I want to get the latest input value. so that I get the latest price that we buy this part for.
If they are assemblies/combined parts, we normally dont' have the price. the price will be all the child parts that needed to get the combined part.
I actually have the idea but it doesn't work out. if I make a Find in Assembly layout by inputting Product ID into the field of Parent ID, I have a list of child products, and If I can basically sum up the price, it will solve it. but the thing is how can I put all together in auto Calculation field? am I going in a right apporach?
I gather that the pricing information is not in any of the tables shown in your screen shot, but is in a table that is related to Product? And that for a given product ID, there are multiple records in that table with different prices. Thus, you want the most recent price for that item in that table.
If your transactions table has an auto-entered serial number field, you can specify a sort order in the relationship between products and transactions that sorts by the serial number field in descending order. Then, from products, a reference to Transactions::PriceField will return the the most recently recorded price for that item.
You can keep the relationship unsorted and use Last ( TransactionTable::PriceField ) to get the most recently recorded price.
I gather that the pricing information is not in any of the tables shown in your screen shot, but is in a table that is related to Product?
that is correct. price is in Transaction table. price field in Product look up the value by relationship.
Thanks. how about assemblies parts (combined parts) price? how can I create auto calculation? because all the individual product ID and combined parts ID are all gonna be product table and how can I auto creation calculation it?
hi Phil, I found a way and all I need is now two condition in Calculation field.
If ( Assemblies = "1"; T15s_Products_Assembly||ID_Parent|::Sum_RMB Price ;T15p_Products_Transaction||Product ID|::RMB Price )
Assemblies is the field in Product to determine if the product is assemblies/combined parts or not. if yes, there will be checkbox with value of 1.
T15s_Products_Assembly||ID_Parent|::Sum_RMB Price is the related field in Assembly table which sum up the total of all the child products.
the calculation now is working till this point.
but it doesn't copy out the last result for Assemblies which doens't value in the field.
T15p_Products_Transaction||Product ID|::RMB Price is the price that my staff buy product. I believe I need to put it like
Last (T15p_Products_Transaction||Product ID|::RMB Price)
can you point out what did I do wrong that make the result two in calculation field not working?
Side note: If Assemblies is a field of type Number, you can simplify your if function to:
If ( Assemblies ; T15s_Products_Assembly||ID_Parent|::Sum_RMB Price ;T15p_Products_Transaction||Product ID|::RMB Price )
I am assuming that Assemblies will be zero or empty if the product is not an assembly.
I believe I posted what you needed in my previous post. Either sort the relationship so that the relationship to T15p_Products_Transaction||Product ID|::RMB Price Makes the most recent record the first related record, or, just as you have posted, use the last function. I am assuming that this is an occurrence of the Transactions table.