14 Replies Latest reply on Jan 19, 2012 8:52 AM by philmodjunk

    How can i show both Fields from a Combined Value List?

    DannyAccola

      Title

      How can i show both Fields from a Combined Value List?

      Post

      Dear All ... i'm a newbee .. so please be kind (:

       

      I have a COLOR database who contain two fields

      <color> and <colorcode> // example <white> <1000>

       

      In a PRODUCT database i have a field <color1> and a combined value ilst in a pop up menu which properly shows the combination of <color> and <colorcode> ... but now i would like to print a list of all the products and show both values <color> and <colorcode> .. but i only get one of both fields as a result.

       

      so .. is there a simple solution to show both values from the value list on my list ... or do i need to create a seperate container? if so ... i need your input pls. thank you very much in advance. best, danny

      Screen_shot_2012-01-13_at_1.22.04_PM.png

        • 1. Re: How can i show both Fields from a Combined Value List?
          philmodjunk

          How have you defined your value list?

          Presumably you have a table of colors and color codes. Does the value list enter the color or the color code?

          Is there a relation ship between the product table and the table of colors, codes?

          I'm a big puzzled because you descibe a typical two column value list, but your screen shot appears to show a single column value list.

          • 2. Re: How can i show both Fields from a Combined Value List?
            DannyAccola

            hi philmodjunk

             

            yes i have 2 tables .. and defined a relation ... the first printscreen shows the combined value list in a pop up menu > which shows perfectly what i need. only when i try to print a list, only the column <color> shows. thank you ...

             

            • 3. Re: How can i show both Fields from a Combined Value List?
              philmodjunk

              Ok, the "/" had me wondering, but I see now that this is part of the data in your first field.

              If you have a relation ship defined linking your main table to this table of colors, you can just add the second field from your related colors table directly to your layout. When you select a color, this will link your current record to that color table record and the additional data in any such fields you add to the layout will then appear.

              BTW, I would make the number, the first field and the text field the second in the value list...

              • 4. Re: How can i show both Fields from a Combined Value List?
                DannyAccola

                good morning PhilModJunk

                i try to manage a product database .. each product is available in various colors .. and they are fixed. so

                 

                <styleA> is available in

                <color1> <color2> and <color3> 

                 

                <styleB> is available in

                <color5> <color9> and <color2> 

                 

                this is the reason why i made a value list (with a pop up menu) ... but my problem is that as soon as i make a list of all products .. it only shows one value .. either <colortext> or <colornumber>

                • 5. Re: How can i show both Fields from a Combined Value List?
                  DannyAccola

                  maybe i need to make a calculation field that combines the informations <colortext> and <colornumber> of the POPUP field <color1> ... but this seems to be very complicated ... since i already have both informations avalable in the in the combined valuelist ...??

                  • 6. Re: How can i show both Fields from a Combined Value List?
                    philmodjunk

                    Define a relationship between your product record and the color table such as:

                    Products::ColorText = Colors::ColorText

                    Then you can add the color number field from the colors table to your products layout and it will show the correct color number. I've taken this directly from your screen shots.

                    • 7. Re: How can i show both Fields from a Combined Value List?
                      DannyAccola

                      i do have such a relationship already ... but since i have various color fields Products::<color1> <color2> ..... <color8> and each field contains one specific color ... it does not appear to be do the trick ... to simply add the field Colors::ColorNumber ... (i've tried that already ... it remains blank). there must be another trick ... hint ... solution .... thank you very much for your help ... i'm going nut's here

                      • 8. Re: How can i show both Fields from a Combined Value List?
                        philmodjunk

                        Separate color fields like this is not an ideal structure for your data, a related table of colors would be much more flexible, and would enable my suggestion to work for you by linking Colors to the individual records in this related table by colortext.

                        With separate color fields like you have here, you'd need to make 10 copies of your colors table occurence and link each separate occurrence to a a different color field by color text, then add the ColorNumber field once from each occurrence to your layout. To copy the Colors table occurrence, you'd select it and click the duplicate button (two green plus signs). This does not duplicate your table just creates a new reference to the existing table so you can define a new relationship.

                        You would not define the relationship as shown, as this will not show any data from colors unless all 11 color fields had exactly the same color text specified.

                        • 9. Re: How can i show both Fields from a Combined Value List?
                          DannyAccola

                          PERFECT >>> THANK YOU! >>> This worked just fine for now! Now you suggest a related table ... it's not too late to change things now ... and i'm getting my teeth into this .. what would your suggestion of a related color table be .. resp. how would i have to implement this onto my PRODUCT Table .. 

                          i wanna make it as good as possible from the start (and i might have more questions - since it's getting very complex already) ... or are you tired of answering all these questions to a newbe who obviously dosen't have a clue.

                          Thank you again ... 

                          • 10. Re: How can i show both Fields from a Combined Value List?
                            philmodjunk

                            If it were true that you "didn't have a clue" you wouldn't have gotten this far with your design. Wink

                            Presumably, you have a field in your Products Table that uniquely identifies each product record. I'll call that field ProductID for this example.

                            Then define a new table, ProductColors with these fields:

                            ProductID
                            ColorText (As said before, I'd be inclined to use the number here, but am staying consistent with your current set up.)

                            Now you can establish these relationships:

                            Products------<ProductColors>-----Colors

                            Products::ProductID = ProductColors::ProductID
                            Colors::ColorText = ProductColors::ColorText

                            To display and work with this color info, you can place a portal to ProductColors on your Products layout and you can include the Colors::colornumber field in the rows of this portal to show both color text and number for each such record you create.

                            • 11. Re: How can i show both Fields from a Combined Value List?
                              DannyAccola

                              ... honestly .. first time filemaker user .. probably a prrof how user friendly this actually is ... but now i'm attacking the next challenge. i want to create a delivery note ... i managed a proper <clientlookup> to select the clients address and complete all fields. but now i want to use a portal where i can select items from ::Products

                              i did not get very far i have selected ::Products as content of my portal and selected all the items i would want to have in the fist line .. but somehow they don't show .. nor do i have the possibilities to add rows ... ughhh (: help again ... 

                               

                              • 12. Re: How can i show both Fields from a Combined Value List?
                                DannyAccola
                                /files/6a606768f1/Screen_shot_2012-01-19_at_5.38.53_PM.png 1082x641
                                • 13. Re: How can i show both Fields from a Combined Value List?
                                  DannyAccola

                                  and now i'm stuck again ... 

                                  • 14. Re: How can i show both Fields from a Combined Value List?
                                    philmodjunk

                                    You need more tables. One table for your orders and one to list the items selected for that order. Usually, this second table is called line items and a portal to it on the orders layout is used to list the items ordered.

                                    The invoices and Purchase Orders starter solution are both set up with such a combination of tables. So is this much simpler invoicing demo file created by Comment and uploaded to a different Forum: http://fmforums.com/forum/showpost.php?post/309136/