9 Replies Latest reply on Nov 6, 2009 8:30 AM by philmodjunk

    autofilter like in excel

    tria

      Title

      autofilter like in excel

      Post

      Hi there

      thanks for reading this post.

      I'm working on a filemaker db for a fashion company.

      The main problem is the following:

      I have a table with components (Category/Company/ Description/Colour/Code/Price)

      and a table with styles(garments) (Type/Fabric/Colour/Wholesale price)

      both are linked in a joint table.

       

      As a style is a summery of its components I need to quantify components to a specific style. The problem is similar to a products added to an invoice. I tried this approach but it's a rather stupid way of doing it cause one component comes in a great variety of colours and or lengths (ie zippers). If you have a lot components your workflow becomes very inaccurate. In excel I used autofilter for this, I could narrow down/serach by Company, then by colour and finally by length etc and add my quantities in a secure way.

      Is there such a way of doing so? 

       

      What is the best approach to that in Filemaker? I tried the standart invoice way and it's not working for me.

      Thanks for your help! 

       

        • 1. Re: autofilter like in excel
          tria
             I use osx and filemaker 10
          • 2. Re: autofilter like in excel
            philmodjunk
              

            I could be wrong, but it sounds like you need a conditional value list. A conditional value list filters down to show just a subset of values based on a choice made in another field. Thus, if you select "zipper" in one field, the conditional value list in another field will only display options appropriate for a zipper. Is that what you need?

             

            If so, click the Advanced search link above and search for "Conditional Value list". You'll find this is a frequent topic here on the forum.

            • 3. Re: autofilter like in excel
              tria
                 thanks for that quick replay. However it needs to work in both ways...sometimes the user knows the company or the colour....so you don't have clear primary or secondary values...
              • 4. Re: autofilter like in excel
                philmodjunk
                  

                Which doesn't necessarily mean the technique won't work. It might be possible for both value lists to be conditional on each other. I just did a little experimentation with a sample database. It can be done, but it can also get a bit tricky to set up in a way such that leaving field 1 blank causes all the values in field 2 to appear and vice versa.

                 

                Here's the demo I came up with:

                 

                I defined two tables: MainTable and WidgetsColors

                 

                I defined the following fields in MainTable:

                Type (text)

                Color (text)

                cTypeKey, calculation returning text: If ( IsEmpty( PType ); "ALL" ; PType )

                cColorKey, calculation returning text: If ( IsEmpty( Color ); "ALL" ; Color )

                 

                I defined the following fields in WidgetsColors:

                Type (text)

                Color (text)

                cColorKey, calculation returning text: Color & "¶All"

                cTypeKey, calculation returning text: Type & "¶All"

                 

                Relationships:

                MainTable::cColorKey = WidgetsColors::cColorKey

                MainTable::cTypeKey = WidgetsTypes::cTypeKey  

                 

                (WidgetsTypes is a second TO of WidgetsColors, click WidgetsColors, click the button with 2 plus signs and rename the new TO.)

                 

                Now define your two value lists:

                Colors: Specify Colors from widgetsColors, related values starting from MainTable

                Types: Specify Types from WidgetsTypes related values starting from MainTable

                 

                Now format the Color and Type fields on the MainTable layout with these value lists and try them out.

                 

                • 5. Re: autofilter like in excel
                  tria
                    

                  thanks for your reply- I found many samples where you have a sorting table, but every time you have linked tables it doesn't work anymore (I don't know how to fix it)

                   

                  I attached an image to illustrate of what I would like to achieve. The red areas is data from the table "style", the yellow from the table "components" and the orange is the "styleline" table.

                   

                  Thanks again!

                   

                   

                   

                   

                   

                  • 6. Re: autofilter like in excel
                    tria
                      

                    thanks for your reply- I found many samples where you have a sorting table, but every time you have linked tables it doesn't work anymore (I don't know how to fix it)

                     

                    I attached an image to illustrate of what I would like to achieve. The red areas is data from the table "style", the yellow from the table "components" and the orange is the "styleline" table.

                     

                    Thanks again!

                     

                     

                     

                     

                     

                    • 7. Re: autofilter like in excel
                      philmodjunk
                        

                      Wow, there's a lot there in one screen!

                       

                      It's not immediately clear to me what the relationship is between Components (yellow) and (Styleline) I can see that style line is being used to compute component costs in some way, but don't see how you are connecting Components::Price to StyleLine::Costing and StyleLine::Total--all of which may not be the least bit germaine to the question you are asking.

                       

                      I need to see if I am anywhere's close to interpreting your layout correctly. From the previous posts, I would assume that what you want is for a selection in the material column to filter the possible choices in the Color column and that both might possibly filter the choices available in code. Is that what is needed? Is that the key issue?

                       

                      "...every time you have linked tables it doesn't work anymore (I don't know how to fix it)"

                      The devil is in the details. Without knowing more about how you need to relate the different tables, there is no way I can advise you. If we can break this down into smaller chunks, we'll have a better chance of getting there.

                       

                      PS. given the complexity of this project, a consultant who can sit down with you and thrash out all these issues with you on a large whiteboard might be able to cut to the bottom line much quicker. If I lived in your part of the world, I'd offer you my services. :smileywink:

                      • 8. Re: autofilter like in excel
                        tria
                          

                        Hi there

                        first of all thank you very much for your time spending on my post. It should have been only 1 picture 

                         

                        It looks more complicated than it is:

                        The red field is a unique style (here a jacket with a unique code depending on the pattern, fabric and the colour), this information is stored in the style table in FM

                         

                        It can consist of any components  (In Excel I used a linked component table, like in FM,  which I could autofilter in Excel), the whole things works very much like a invoice system where you add products....clear?

                        The Total is calculated by Price of Component * Quantity (I named it costing) = Total, which will be later summed up to find out what the initial price of the style is.

                         

                        I guess the Total and the Quantity(Costing) is stored in a line table(Orange) which is linked to the component table and the style table in FM- yes?

                         

                         

                        How much do you charge for consulting?

                         

                         

                         

                         

                        • 9. Re: autofilter like in excel
                          philmodjunk
                            

                          I figured out the multiple images, for future reference, if you post a message and then spot problems with it, you can choose "edit" from the options menu just above the Kudos control to take it back and change it. You can even delete the message altogether if no one has responded to it.

                           

                          This is one of those situations where there are multiple issues to be resolved one at a time. Here's a basic sketch of how I am perceiving your data tables and their relationships:

                          I'm focusing on comprehending your current design, I see areas where the basic design should be changed, but first things first.

                           

                          Tables:

                          Style

                          One record appears to represent one item undergoing design/costing analysis. I see several numbers in this section presumably, one uniquely identifies the finished item. Looking at your screen shot, the "Black Leather Jacket" could be ItemID= SS10 or 9076 or some value not visible on the screen.

                          Components

                          One record here represents one component out of many that are assembled into a finished garment. Many records here relate to a single record in Style. The fields in this record describe what it is, who supplies the material, and the unit cost for the material.

                          StyleLine

                          One record here appears to compute the component cost for the material, thus, for each component there is a matching StyleLine record.

                           

                          For basic relationships between these tables, in Filemaker you'd need the following to get started:

                           

                          Style::ItemID = Components::ItemID (Delete and Allow creation options for Components will likely be needed)

                          Components:: ProductID = StyleLine:: ProductID

                           

                          Does that description make sense to you?

                           

                          With regards to any paid consulting, I've sent you a private message so that we can take that discussion "off line".