5 Replies Latest reply on Oct 13, 2015 10:18 AM by erolst

    3 tables one layout - portal fields not auto filling

    AmberDavis

      I have three tables: Inventory, PartsOrders and Transactions.

       

      Under Inventory their is a list of our inventory and it is categorized by a drop down menu as either Part or NYPD.

      Under PartsOrders this layout is used to sell parts to customers. We have a portal showing related records from the Transactions table.

      When selecting an item from the portal row you first have to select Parts or NYPD from a different dropdown menu to filter the items. The second dropdown menu is a value list (from first field Inventory 3::Item include only related values from PartsOrders 2). <--This makes it so it only shows either Parts or NYPD. Once the item is selected I would like the part number and the price correlated with the customer type to appear but it does not. I think having a conditional value list is messing up the autofill. Any suggestions would be greatly appreciated.

       

      Example:

      Inventory_Layout

      Inventory_Item: Cylinder

      Inventory_Part #: Cylinder1

      Inventory_Price_Retail: $5

      Inventory_Price_Distributor: $2

      Inventory_Department: Part

       

      PartsOrder Layout

      Customer = Retail

      (Portal)

      Item = Cylinder  Part# = Cylinder1  Price = $5

        • 1. Re: 3 tables one layout - portal fields not auto filling
          erolst

          AmberDavis wrote:

          Once the item is selected I would like the part number and the price correlated with the customer type to appear but it does not.

           

          Did you just wish for it or did you do anything about it?

           

          If this is your data model

           

          PartsOrders --< Transactions >-- Parts

           

          i.e. there is a relationship in place like

          Transactions::InventoryID = Inventory::ID*

           

          then you can define Transactions::price as an auto-enter calculation =

          Inventory::Price_Retail

           

          Analogous for other values from Inventory that you want to capture in Transactions.

           

          *You should use auto-entered, meaningless serial keys/IDs to uniquely identify your records, not business data like itemName, ItemSerialNo etc. Using such data will work until it doesn't – because someone fiddled with business data – as they should be able to –  or the same name appears multiple times, or you just want to correct that embarrassing typo in the item name  … using IDs, OTOH, will simply work, if set up correctly.

           

          To use IDs with your value list, use 1. field: ID, 2. field: itemName, Show only values from 2. field. You'll then select a name from the popup*, but actually insert a key. (*Be aware that this only works with a popup control, not a dropdown.)

           

          And while you're at it: maybe use better table occurrence names than Inventory 3 and PartsOrders 2; e.g. Inventory_byCategoryForVL …

          • 2. Re: 3 tables one layout - portal fields not auto filling
            AmberDavis

            I've been wishing this whole journey...as you can tell I'm pretty new to this!

             

            I attempted what you said:

            Inventory 2::ItemID = Transactions::ItemID

            Transactions::CustomerID = Customers 3::CustomerID

            Changed Transactions::Prices to an auto-enter calculation of

            Case ( Customers 3::Customer Type = "Retail" ; Inventory 2::Retail Price ;

                       Customers 3::Customer Type = "Distributor" ; Inventory 2::Distributor Price ;

                       Customers 3::Customer Type = "Local" ; Inventory 2::Repair Center|Local Cost ;

                       Customers 3::Customer Type = "Repair Center" ;Inventory 2::Repair Center|Local Cost

                     )

            Changed Transactions::DYN Part # to an auto-enter calculation of

            Inventory 2::DYN Part #

             

            But still no luck on any results.

            And thank you for the advice. Once I get this little bit to work I will definitely clean up my database with those suggestions!

            • 3. Re: 3 tables one layout - portal fields not auto filling
              erolst

              AmberDavis wrote:

               

              I've been wishing this whole journey...as you can tell I'm pretty new to this!

               

              I attempted what you said:

              Inventory 2::ItemID = Transactions::ItemID

              Transactions::CustomerID = Customers 3::CustomerID

              Changed Transactions::Prices to an auto-enter calculation of

              Case ( Customers 3::Customer Type = "Retail" ; Inventory 2::Retail Price ;

                         Customers 3::Customer Type = "Distributor" ; Inventory 2::Distributor Price ;

                         Customers 3::Customer Type = "Local" ; Inventory 2::Repair Center|Local Cost ;

                         Customers 3::Customer Type = "Repair Center" ;Inventory 2::Repair Center|Local Cost

                       )

              Changed Transactions::DYN Part # to an auto-enter calculation of

              Inventory 2::DYN Part #

               

              New, but quite good – you came up with that Case() all by yourself , and AFAICS, that is not the cause of any issue here.

               

              One thing I would suggest is to not store the customerID in Transactions; it doesn't do any harm, but it's one more moving part you need to keep track of, and it shouldn't be necessary, because in this model

               

              Customers --< PartsOrders --< Transactions >-- Parts

               

              a transaction “can see” its customer and attributes via its PartsOrder. So no need to add a Customers TO for that purpose; the existing one should do just fine.

               

              btw, try

               

              Let (

                theType = Customers 3::Customer Type ;

                Case (

                  theType = "Retail" ; Inventory 2::Retail Price ;

                  theType = "Distributor" ; Inventory 2::Distributor Price ;

                  theType = "Local" or theType = "Repair Center" ; Inventory 2::Repair Center|Local Cost

                )

              )

               

              AmberDavis wrote:

              But still no luck on any results.

               

              Be aware that (unlike with a calculation field) adding an auto-enter calc to the price field doesn't change the value for existing Transactions records; you'd have to create new ones to see this in action.


              Here's a troubleshooting approach: put the fields the auto-enter calc refers to onto a layout based on the context from where the calc is evaluated (well, it would simply be Transactions, but you should be aware of these subtleties …).


              If you cannot see any related values (especially from Customers 3::Customer Type, since that is what drives the Case(), but also from Inventory 2::Retail Price etc.), then neither will the calculation …

              • 4. Re: 3 tables one layout - portal fields not auto filling
                AmberDavis

                You're right. I tested it on the referenced layout and it is not showing. What could I possibly be missing..I feel this is a relationship error. A little more in-depth description might help:

                 

                Department value list : Values from Inventory 3::Department

                Item value list: Values from Inventory 3::Item including only related values from PartsOrders 2

                 

                Relationships:

                Inventory 2::ItemID (allow creation of records in this table) = Transactions::ItemID  (allow creation of records in this table)

                Transactions::Parts Number (allow creation of records in this table) = PartsOrders::Parts Number (allow creation of records in this table)

                • 5. Re: 3 tables one layout - portal fields not auto filling
                  erolst

                  The value list shouldn't pertinent here, since it is only a device that facilitates setting an Inventory ID (identifier) into the Transaction foreign key field. OTOH, compare what you're inserting by using the value list, and what should be in there.

                   

                  Also, try creating a new Transactions record and manually put in known existing values for the PartsOrder and Inventory keys; if you still cannot see related values, then I'd check the field types of the fields used as match keys in the relationship(s).