9 Replies Latest reply on Jan 7, 2011 9:07 PM by LauraTurgeman

    Enabling certain fields in portal based on value list

    LauraTurgeman

      Title

      Enabling certain fields in portal based on value list

      Post

      Hello, I am working on an sales order solution for clothing and need multiple quantity fields per record.  Product records will have an "available sizes" value list and based on this value list I would like to have data entry possible only for fields related to that value list.  For example:  Shirts are available in 5 sizes, pants available in 11 sizes and hats only have 1 size.  I was thinking to put 11 size fields (max. available size possibilities) in a portal on my layout and depending on the value list have only the related size fields enabled.  I'm also not sure of the fields that I need to create in the sizes table and where the relationship should be.  I think the simple way would be to create a new record for each size but my records list would be too long and the invoice would be too long as well.  I don't have too much experience in scripts.  I am using FMP 11.  Any help would be appreciated.  Thanks.

        • 1. Re: Enabling certain fields in portal based on value list
          philmodjunk

          Let the data entry person add portal records only as needed for a given sale. They can simply select a size from your drop down list of available sizes--which can be controlled via a conditional value list specific to a given product and the sizes currently available for it.

          A line in your portal might look like this:

          [ItemID][Description            ][Size][Qty][Unitcost][ExtendedCost]

          This enables you to list multiple items in one portal, only listing items and sizes actually purchased in that transaction.

          • 2. Re: Enabling certain fields in portal based on value list
            LauraTurgeman

            Thank you.  Some product items come in as many as 11 sizes so I was hoping to have all 11 of these size fields on one portal line but have only the required fields active.  Otherwise I would have to create a new portal record for each of the 11 sizes ordered.  In some cases the product is only available in 5 sizes and I would only want only 5 of the 11 fields active based on a value list.  If I have a sale for hats I would only want one field (size_1) active.  Should I have 11 size fields in my size table (size_1, size_2, size_3, etc.) and a unique field for the size scale (used for the value list)?  What type of relationship do I need in the product to size table?

            • 3. Re: Enabling certain fields in portal based on value list
              philmodjunk

              The complications involved in trying to do that are why I have suggested an alternative approach.

              To put multiple size entries into the same portal row requires either multiple fields or a repeating field in the same record. You can't use a portal as that would be a portal inside of a portal which can't be done with the current version of FileMaker. You can add the fields or repetitions fairly easily, but it may complicate certain reporting actions you may need (Such as tracking sales for a given item by size.)

              Disabling selected fields are not so easy to do. It can be done, sort of by using an onObjectEnter script trigger to move the user back out of a locked field, but a bug in the current filemaker keeps the trigger's script from performing before you select a value from the value list so you can select a size and then the script kicks you back out of the field.

              Hmmm, you may want to play around with a list view layout of your portal records for entering your data. This would allow you to create a "horizontal portal" of size fields and then you can set things up to only display the sizes available for the item sold. A horizontal portal is a series of one row portals set side by side so the data is displayed horizontally. The first portal is set to display row 1 of 1, the second is set to display row 2 of 1 and so forth...

              • 4. Re: Enabling certain fields in portal based on value list
                LauraTurgeman

                Okay, I'll play around with those ideas.  Can you breakdown the steps for your suggestion of "They can simply select a size from your drop down list of available sizes--which can be controlled via a conditional value list specific to a given product and the sizes currently available for it."  I'm still a little confused as to what fields I need in the size table that I will be basing this conditional value list on.  I currently have a value list of knits, pants and hats that I use in each of my product records.  Each of these need a unique set of sizes.

                • 5. Re: Enabling certain fields in portal based on value list
                  philmodjunk

                  That's the one question that really interested me when I first read this thread. This one won't be simple to set up.

                  Do you know how to create a conditional value list?

                  Do you have this basic table structure?

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

                  Invoices have one record per sales transaction with a portal to LineItems to list each item purchased in that transaction. Prices, item descriptions and other data are referenced/looked up from Products when a ProductID is entered/selected in a LineItems row.

                  We have to establish the basic structure before we can figure out how to set things up so that only the specified values appear in our conditional value list.

                   

                  • 6. Re: Enabling certain fields in portal based on value list
                    LauraTurgeman

                    Yes, I have the Invoices, Line Items and product tables set up.  I also have tables for color, item type and fabric (I still need to add a size table).  These tables have a one to many relationship to the products table.  At the moment I am only using these tables for value list drop down only.  I do not have any conditional value lists set up yet.  I have an Invoice layout with a portal based on line items and use a unique product ID.  I am placing related fields in my portals from all of the related tables to fill in additional information for that product. 

                    • 7. Re: Enabling certain fields in portal based on value list
                      LauraTurgeman

                      Sorry, the line items portal has fields from products only and the products list uses the value lists for all of the additional color, fabric, and item type using unique ID relationships.

                      • 8. Re: Enabling certain fields in portal based on value list
                        philmodjunk

                        I'm guessing you have one Product ID for all sizes of the same item. If so, we can use one value list to set up the conditional value list.

                        Add a text field to Products called Sizes. Format it with a check box group so that you can select the sizes available for that item by clicking the relevant check boxes.

                        Now you can use your existing, LineItems::ProductID = Products::ProductID in a conditional value list that only lists the sizes specified for that product:

                        In Manage Value Lists, Create your value list using the specify Field option.

                        Select Sizes from Products as the field to supply the values for the list.

                        At the bottom of this dialog select this option: Include only related values starting from LineItems.

                        This is a beginner level set up to get you started. It's possible to use a script that checks inventory or that uses the contents of a sizes table to create this list of values in the Sizes field in Products automatically.

                        • 9. Re: Enabling certain fields in portal based on value list
                          LauraTurgeman

                          Thanks-  I do have several uses for conditional value lists.  Your instructions have made it clearer as to how to do this.