8 Replies Latest reply on Nov 7, 2011 8:08 AM by philmodjunk

    Portal lookup question

    JamesBlaine

      Title

      Portal lookup question

      Post

      This is probubly simple however, I just can not seem to figure it out even after reading various sections of the missing manual. Here goes:

      I have a "Sale" table that is linked to a "Product" table using a one to many link

      What I would like to do is have a Portal in the Sale table to look up records in the Product table and then be able to make the selection of the product along with it description, price and catagory into the portal.

      Thaks in advance for your help

        • 1. Re: Portal lookup question
          mgores

          Are you looking for a selection list type of portal?

          • 2. Re: Portal lookup question
            JamesBlaine

            Actually, I would like to use the portal to access the product table and pick a product along with it price and description that would than be placed in the portal record and used to print an invoice from. I can get it to work however it will only bring over one field say the category field and not the description or any other field in the record.

            I think this would be how invoices are created. Thanks for your help

            • 3. Re: Portal lookup question
              mgores

              I think you need a join table between your Sales and Product tables like a LineItems table.  This is actually a many to many relationship since one sale can have more than one product and each product will be sold more than once (hopefully?).

              If your product list is reasonably short you could have the ID field set to be a pop up menu with a value list made from the ID and product name fields.  This will let you select by and see the product name, but will enter the ID, then have the other fields in your portal row also and they will fill in once you select that ID.

              If your product list is longer, it may be better to have a separate selection list portal.  This can show all items in your product list and be set up with a button to addd the items to your line items of your invoice.

              • 4. Re: Portal lookup question
                philmodjunk

                Here's a download link to a simple demo file, created by "Comment", of the set up Mark is recommending:  http://fmforums.com/forum/showpost.php?post/309136/

                • 5. Re: Portal lookup question
                  JamesBlaine

                  Thanks to all ou have help me get to the bottom of the problem. The link to the sample file put me on the right track.

                  • 6. Re: Portal lookup question
                    JamesBlaine

                    Well the fix has created another problem. Now the look up and auto paste is working fine however, in those rare situations where information needs to be changed after it has been looked up and posted into the new table I can not change the data and have the calculation field recognize the new changed data. I think this is a result of it being a look up field.

                    This is not a show stopper as I have a work around in mind but it sure would be better not to have to impliment it. Any suggestions on the problem would be very helpfull. Thanks for you suggestions in advance.

                    • 7. Re: Portal lookup question
                      mgores

                      If there is data that will be not always be consistent it should be stored in the join table.  For example you could have a price field in the join table that is entered from the price from the product table.  If you give a customer a one time discount, you could change the price in the join table and it would only affect that one transaction.  Likewise calculation fields, like the total price for that sale, should be in that join table as well.  That will make it possible to generate reports based on customer or products and have the correct data.

                      • 8. Re: Portal lookup question
                        philmodjunk

                        To include the price from the products table into your LineItems table, make it a number field with a looked up value auto-enter setting in field options. Then the field will load a copy of the matching product record's price when you select a product. Since this is a number field that loads a copy of the data, you can edit it as needed to change the value originally looked up and price changes made in the products table will affect the next invoice you create but will not affect the prices shown in existing invoices. The demo file uses this method so you can take a look at it to see how it is done.