10 Replies Latest reply on Jul 2, 2014 2:23 PM by philmodjunk

    Calculation to lookup information in a field not working

    AmberDavis

      Title

      Calculation to lookup information in a field not working

      Post

           I have a pop up menu for items that I want to link to the items part number. The Item pop up menu is connected to a value list that includes only related values from a table called "PurchaseOrders". The part# field has a calculated value of If ( IsValid ( ItemID ) ; Lookup ( Inventory 3::DYN Part #)) with context from Transactions. When an item is selected the only part number that appears is the first part number of our inventory. I hope this is just a simple calculation error. I will link a picture of my relationship web as well.

           Thank you.

      Screen_Shot_2014-07-01_at_3.35.18_PM.png

        • 1. Re: Calculation to lookup information in a field not working
          AmberDavis
          /files/6a3b2e7aee/Screen_Shot_2014-07-01_at_3.30.59_PM.png 953x692
          • 2. Re: Calculation to lookup information in a field not working
            philmodjunk
                 

                      The Item pop up menu is connected to a value list that includes only related values from a table called "PurchaseOrders"

                 Does that part of your set up work? It describes a conditional value list but does not indicate the "starting From" table occurrence so I can't tell if it will work for you nor do you explain the purpose behind making this a conditional value list.

                 Given your relationship map, I'd do one of the following to get what you are trying to do here:

                 Option1:

                 Remove the Part# field from your table and portal. Replace it by adding DYN Part # from Inventory 2. This sets up a dynamic link to the value in the Inventory table. If you change the data in Inventory::DYN Part #, the data shown on your layout automatically updates. This may or may not be desirable.

                 Option 2:

                 Keep the field but remove the auto enter calculation and replace it with this calculation: Inventory 2::DYN Part #. You could also clear the calculation option and select the same field using the Looked Up Value field option and get the same result. Option 2 copies this data over from the inventory table. If you later modify the data in the matching DYN Part # field, the value in this field will not automatically update.

            • 3. Re: Calculation to lookup information in a field not working
              AmberDavis

                   Yes the item pop up menu works! There is another conditional value list that has two categories "Assembly" and "Install" and depending on which item you select, different tools will show up in the ITEM pop up menu. I set that up by going into our inventory and assigning tools as either "Assembly" "Install" or " " for none of the above. Once they are assigned they appear on the list under either Assembly or Install. Does this conditional value list have something to do with why the part number won't appear?

              • 4. Re: Calculation to lookup information in a field not working
                philmodjunk

                     It has nothing to do with it.

                     I've provided 2 methods that will work in my last post and it won't matter if you use your conditional value list or a different one as long as the correct value is entered into the transactions::itemID field.

                • 5. Re: Calculation to lookup information in a field not working
                  AmberDavis

                       For option 2 should I be evaluating the calculation from the context of Transactions? Since Part# is located under the Transactions table? Because the option 2 and 1 don't work. There must be something missing on my part because the logic definitely makes sense! Do you think I have something that needs to be checked?

                  • 6. Re: Calculation to lookup information in a field not working
                    philmodjunk
                         

                              For option 2 should I be evaluating the calculation from the context of Transactions?

                         yes

                         

                              Since Part# is located under the Transactions table?

                         That's not the complete reason

                         The relationship from Transactions to inventory 2 is the relationship that matches records in transactions to Records in Inventory by itemID. And that's what you need here to refer to a DYN Part # field.

                         Adding/Changing the auto-enter calculation for the transactions::part number field will not update the part number field for any existing records. So test this by creating a new record in the portal and see if values correctly appear in it.

                    • 7. Re: Calculation to lookup information in a field not working
                      AmberDavis

                           Is there any way I can set a field so that any EXISTING record can show the dynamic part number when updated? We have a lot of inventory!

                      • 8. Re: Calculation to lookup information in a field not working
                        philmodjunk

                             That's why I suggested that you simply remove the part number field from transactions and use the DYN Part # field from Inventory 2.

                             But there are ways to force an update as well: Updating values in auto-enter calc fields without using Replace Field Contents

                        • 9. Re: Calculation to lookup information in a field not working
                          AmberDavis

                               Option 1 doesn't seem to be working either although it should!

                          • 10. Re: Calculation to lookup information in a field not working
                            philmodjunk

                                 Here's a more detailed description of how to use Option 1:

                                 You need to start with a layout that is based on either Transactions or one based on PurchaseOrders with a portal to Transactions.

                                 Use the field tool to add a new field object to your layout body or portal row.

                                 In the specify fields dialog that pops up, select inventory 2 from the drop down at the top of the dialog box. This will not work if you select any of the other occurrences of Inventory.

                                 Then select the DYN part # field from the fields listed below the drop down.

                                 My best guess is that either your layout does not match what I've specified or your selected this field from the wrong occurrence of Inventory.