5 Replies Latest reply on Sep 10, 2010 10:58 AM by philmodjunk

    Help with Lookups

    BrianKolb

      Title

      Help with Lookups

      Post

      I am using the built in purchase order starter solution and am trying to incorporate our previous inventory list to help with entering data.  I want to have it so when the user clicks on the part number box, it opens with the numbers from our database, and when a part number is selected, the part description and price automatically are entered in the corresponding fields.  I have been able to do this for one part, using a lookup, but when I try to do it again, the part description for the very first part number is all that will show up in subsequent part description lookup fields in browse view. I want to make it so the part description is based off of the directly preceeding part number, so that it will lookup in the inventory database for that part number and return the correct description.  This way I can enter multiple parts drawing from the one database.  Does anyone have any suggestions please?

        • 1. Re: Help with Lookups
          philmodjunk

          The purchase orders starter solution lacks a needed table for this. You'd need to add another table so that they are related like this:

          Purchase Orders---<Line Items----<Products

          You'd import your current inventory data into Products and define looked up value settings for the Price and description fields in Line Items.

          Take a look at the Invoice starter solution or this simpler Demo created by Comment for an example of how this is done:  

          http://fmforums.com/forum/showpost.php?post/309136/

          You'd just change the name of the Invoices table to Purchase Orders in both of these two example files.

          • 2. Re: Help with Lookups
            BrianKolb

            Thanks so much!  It worked!  The problem I now have is that the actual purchase order sheet that is to be used to mail out will only accept one part at a time.  In other words, it creates a new sheet for each part entered in the purchase order record detail sheet, even if they are all entered on the same sheet.  So if I want to order 3 things from one company, it would make 3 separate purchase orders, one for each item.  Would you know how to resolve this?  Please let me know, thanks!

            • 3. Re: Help with Lookups
              philmodjunk

              That shouldn't be the case. Looking at the record detail layout, I see the typical portal to Line Items one finds in these systems. To add an additional line item, just type it into a blank line in this portal (the section headed with Product ID, Description, taxable...).

              This gives you one Purchase Order Record with multiple related line item records.

              • 4. Re: Help with Lookups
                BrianKolb

                Thanks for getting back to me so fast.  It does work in the detail record section, so I can add multiple items, but when I'm finished entering and I click on the print purchase order button, a script takes me to the page that will actually be printed, and only one item will show up.  So, if I enter 6 items on one record detail, it will create 6 separate purchase orders.  This is the page that would actually get emailed or sent to the vendor.  So I know it is being entered, but getting it to display on the right form is the problem.  Does that make sense?

                • 5. Re: Help with Lookups
                  philmodjunk

                  For some reason, the Purchase Order layout has been set to default to form view, so you only see one line item at a time while in browse mode. If you print or preview the order, it should correctly print/preview all the lineitem records on a single page. You can change the layout's view option from Form to List and you'll then see the Purchase order while in browse mode in much the same format as what you'll get when you print it.