5 Replies Latest reply on Dec 5, 2013 4:36 PM by philmodjunk

    Using Looked-Up or Lookup in a portal

    JessicaThompson

      Title

      Using Looked-Up or Lookup in a portal

      Post

           I have an "invoice" table and "invoice items" table that relate on an invoice number. In a layout showing the details of a single invoice, there is a portal of "invoice items" so the items are shown in a list.

            

           When someone selects an item from the "invoice items" portal, I want the next field to autofill with the price of that item. At first I was going to create a script that had "If" for each item, followed by the price, but it seems like this would be a lot of unnecessary work. (There are many items.)

           I found out about the Looked-Up Value option and that seems better. With that, would I simply create a third table ("Item prices" or something) and relate it to "Invoice items" by the item name? 

           Also, I see there is a calculated value option called "Lookup." Is this the same? Is there an advantage to using one over the other?

           Thanks.

        • 1. Re: Using Looked-Up or Lookup in a portal
          philmodjunk

               If you use "new from starter solution", you can open an invoices starter solution that demonstrates what you are requesting. Normally, this is set up as follows:

               Start with these relationships:

               Invoices-----<LineItems>-----Products

               Invoices::__pkInvoiceID = LineItems::_fkInvoiceID
               Products::__pkProductID = LineItems::_fkProductID

               You can place a portal to LineItems on the Invoices layout to list and select a Products record for each given Invoices record. Fields from Products can be included in the Portal to show additional info about each selected Products record and the _fkProductID field can be set up with a value list for selecting Products records by their ID field.

               You'd set up a unit price field in Products and also in LineItems. The lineItems::unit Price field would use the looked up value option to copy the unit price from Products whenever a product is selected from _fkProductID's value list.

          • 2. Re: Using Looked-Up or Lookup in a portal
            JessicaThompson

                 Thank you. I got it working! I didn't follow this exactly, but I did something similar using the item name and no id number.

            • 3. Re: Using Looked-Up or Lookup in a portal
              philmodjunk

                   Using the item name may be easier to set up, but it can open the door for future problems such as discovering that a name was incorrectly entered and then having to modify the name to fix it, etc.

              • 4. Re: Using Looked-Up or Lookup in a portal
                JessicaThompson
                     

                You can place a portal to LineItems on the Invoices layout to list and select a Products record for each given Invoices record. Fields from Products can be included in the Portal to show additional info about each selected Products record and the _fkProductID field can be set up with a value list for selecting Products records by their ID field.

                This is essentially how I set it up, but without the ID field and I got the idea from looking at the starter solutions database. I guess I'm unclear on exactly how to do it using a number. Right now I have a portal on the layout and people can select an item from a drop-down menu. The very last part is what I don't understand.

                     

                the _fkProductID field can be set up with a value list for selecting Products records by their ID field.

                     Would database users have to select a product idea from a dropdown menu instead of the item name? That would not work for us. They would have to select by the item name. If there is a way for it to then, behind the scenes, select an item # that corresponds w/ an item # in the third table that contains prices, that would work. I guess I'm not sure how it would select the item #. Would I have to do a script?

                • 5. Re: Using Looked-Up or Lookup in a portal
                  philmodjunk

                       The Basic "beginner level" approach to this is to set up a value list that includes both the ID numbers and the item names in the value list. The users select the item by name, the value list enters the ID that corresponds with the name selected.

                       When you set up a value list with the "use values from a field" option, you can specify that data come from two fields. The date in the "First field" is entered into the field you format with a value list option. The "second field" provides additional info (such as a name) to help the user select the correct item from the list. And there is an option in the value list setup that hides the first field from view so that all you see is the item name.

                       More sophisticated, script supported methods, make it easier to enter/select a name yet still use that name to enter the correct ID number.