8 Replies Latest reply on Apr 19, 2013 8:15 AM by philmodjunk

    Auto Calculation question on related table

    henryhu91

      Title

      Auto Calculation question on related table

      Post

           Hello FM users, 

           I have a question on auto calculation field.

           I'm using Inventory FM starting point in my database. 

           I've Product table, Transaction table and Product ID is unique key for relationship. 

           Transaction table have a field called "Transaction Type" to determine what type of transaction 

           for example. Order, Invoice, Receive, and etc. 

           If a record from transaction table is order, my staff will input price, quantity and shipping location.

           Product table have a field called Price field. 
           I would like to auto fill Price field to update automatically from the price field that latest order price that my staff ordered. 

           any help is appreciated. 

           Thanks

        • 1. Re: Auto Calculation question on related table
          philmodjunk

               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.

          • 2. Re: Auto Calculation question on related table
            henryhu91

                 how can I pull the value of the last record in that calculation field? 

                 do you have any example?

            • 3. Re: Auto Calculation question on related table
              philmodjunk

                   You'll need to be more specific about what you mean by "pull the value of the last record".

              • 4. Re: Auto Calculation question on related table
                henryhu91

                     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. 

                     This is the relationship that I'm using. ID=Product ID, 

                I've Portal setup is from Product to Assembly by Child ID(Assembly table for BOM) and Product ID field in the portal is from Product to Assembly by Child ID to Product (Product Table). 

                     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?

                • 5. Re: Auto Calculation question on related table
                  philmodjunk

                       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.

                       OR

                       You can keep the relationship unsorted and use Last ( TransactionTable::PriceField ) to get the most recently recorded price.

                  • 6. Re: Auto Calculation question on related table
                    henryhu91
                         

                    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?

                    • 7. Re: Auto Calculation question on related table
                      henryhu91

                           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?

                           Thanks :)

                      • 8. Re: Auto Calculation question on related table
                        philmodjunk

                             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.