5 Replies Latest reply on Jul 12, 2011 10:19 AM by blur1221

    value lists that populate multiple fields

    blur1221

      Title

      value lists that populate multiple fields

      Post

      thanks in advance for any insight

      I am putting together a inventory database for my ebay business. I buy large lots of used items but overall there are really only about 200 items in total i buy over and over again in these lots. 

      I keep track of it all in a fmp database entering each item and then their $$value. I would like to make a value list saying this item is worth $xyz. I would like to select the item from a value list and have it automatically input the name of the item in one field, and the value of the item in another numerical field. I then separately use a calculation field to tally all of the values of the item to see profit loss etc. Im guessing i need to have a portal (never used that feature) with a value list on the other end for all of the 200 items. 

      Overall, if anyone could tell me how to create the master item value list and then link it to the fields in the main database, i would really appreciate it!

      *** i have attached a screenshot. Basically when i select a item from my pulldown i want it to fill in the revenue, ebay, paypal and ship fields.

      Screen_shot_2011-07-10_at_9.04.25_PM.png

        • 1. Re: value lists that populate multiple fields
          philmodjunk

          A portal might be very useful here, but isn't strictly necessary. What is needed are the right related tables so that selecting an item from your value list enables your layout to display data, such as name and value, from that related table. You can set this up so that FileMaker physically copies the data from the other table into this layout's table, or simply displays the data from that table. Both approaches have their uses, so look over both options carefully before deciding which option you want to take.

          See this thread for a description of both techniques: 

          Auto Fill

          • 2. Re: value lists that populate multiple fields
            blur1221

            thanks for the great link philmodjunk!

            I followed the steps but ran into 2 big snags.

            1. i buy my items in lots and then sell them off individually. so the way i was making my database was that each record was for a lot, and then inside i have the field list where i could list each item in the lot, what it was worth, associated fees and then alter it as i sell it with the firm numbers. I went with your number 2 sudgestion from the link you gave me, having the field in the main database do a value look up. and it all worked perfectly. my issue is i will list many many items per record. i create field of item 1, item 2 etc but each one is linked to the same "product list" when i select a product on the first item field, it populates all of the other item fields and supporting fields with the same info. how am i able to select different products as they are all linked to the same record in the product list table.

            2. slightly unrelated, i have a field calculate the paypal transaction cost (revenue field *.07+.3) which gives me my estimated fee from paypal, should i need to edit this number without changing the revenue field  on just one particular record, is that possible.

            thanks and ill attach some pics. to the main post to illustrate

            • 3. Re: value lists that populate multiple fields
              blur1221

              heres supporting pics

              main

              product list fields

              relationship

              • 4. Re: value lists that populate multiple fields
                philmodjunk

                "my issue is i will list many many items per record."

                Don't do that. Instead, define a related table of items that you can link to your Rush Products table. That way you have one record for each "lot", but on that lot record a portal lists the individual items records from the items table. This is exactly the structure you get from a invoicing or purchase order system and in fact, a Purchase Order systems is what you are creating here.

                PurchaseOrders (RushProducts)----<LineItems>-----Products

                PurchaseOrders::POID = LineItems::POID
                Products::ProductID = LIneItems::ProductID

                Is the typical table structure and relationships used for these systems.

                Here's a very simple invoicing demo created by Comment: http://fmforums.com/forum/showpost.php?post/309136/

                Just rename the Invoices table "Rush Products" or "PurchaseOrders" and you'll have an example of what I am recommending to you.

                 

                • 5. Re: value lists that populate multiple fields
                  blur1221

                  thanks philmodjunk, exactly what i was trying to accomplish! A++ thanks for the help!