14 Replies Latest reply on Sep 9, 2016 4:16 AM by coherentkris

    populating data with multiple parameters?

    malibux14x

      Hey there.  I have a database of product from multiple brands and a data base for purchase orders.

       

      I currently have it linked to where i type the name of the item into the purchase order and it searches the product database and i click on the correct item and it populates all the info for that item.

       

      My product database is growing very large and sometimes the names overlap so i need to add a "1" or something at the end.  Is there a way to say:

       

      i create a purchase order for BRAND A and choose BRAND A when i create a new PO so i know its only for this brand.  then the product field will ONLY show items from BRAND A?  Then if i create a PO for BRAND B it will only show items from BRAND B?

       

      Sounds simple but i cant figure this one out.

       

      thanks!

        • 1. Re: populating data with multiple parameters?
          David Moyer

          Hi,

          if you have version 14 or 15, please check out this demo ...

          (It uses non-traditional text keys to simplify data entry and product references.)

          • 2. Re: populating data with multiple parameters?
            malibux14x

            Thanks for the example.  That is SORTA what i was hoping for but thats a little too complicated.

             

            So when i create a new PO i have to choose the brandScreen Shot 2016-09-08 at 3.28.49 PM.jpg

             

            then when i choose my styles i get a drop down menuScreen Shot 2016-09-08 at 3.29.06 PM.jpg

             

            Now as you can see there are a number of "Vistas" from multiple Brands.  In the product section, i already have them labled as to which brand they belong to.  I was just wondering if there is a way to make a PO for a certain brand, only show product available that is from that brand?

             

            So if i make a PO for  Astronomy, i only have the drop down menu to show Astronomy styles.

             

            make sense?

            • 3. Re: populating data with multiple parameters?
              David Moyer

              Hi again,

              the demo takes a category and only allows choices from the appropriate sub-categories.  I think that's what you want - maybe you need "brand - product - style" ?  You can use any number of categories in your hierarchy (e.g. Size).

              I'm not sure - it's very flexible for top-down data entry.

              • 4. Re: populating data with multiple parameters?
                malibux14x

                First of all, thank you very much for assisting.   i do understand your set up.  Im just trying to think how i would incorporate it into mine without having to re-code everything.

                 

                Is there a way to make it so it only shows that brand's styles in the drop down menu just by selecting the brand that ill be using?  the way you have it, you keep selecting parameters until you get to the choice you want, and it fills in the style info.

                 

                so using that example, i would have to choose the brand, then the style, then the season, then color but even then it would narrow it down to multiple choices.

                 

                the way i do it now, is just type the name of the style, select it form the drop down menu and it fills in all the info associated with that style.  which is fine, but im just trying to find a way to shave a few seconds off of my data entry.

                 

                plus that dark blue selector color makes it tough to see the style name. 

                 

                thanks again

                • 5. Re: populating data with multiple parameters?
                  dtcgnet

                  With regard to Brands, do you have a Brands table? Do you have an ID code for each brand, or is it all by text selection (e.g., brand name: Astronomy. Brand name: Astronomy International)?

                   

                  You can certainly get to what you're after. Please post either pictures of a relationship graph or a sample of the database.

                   

                  First guess out of me? One way or the other you do a self join on the Products table. Something like Products links to Products_Brand when Products::Brand = Products_Brand::Brand.

                   

                  The details depend a lot on what your structure is.

                  • 6. Re: populating data with multiple parameters?
                    malibux14x

                    Im still somewhat of a novice on FM but the way i have it set up is i have the PO data base and the PRODUCT database.

                     

                    on the PO i have STYLENAME01...STYLE NAME 20

                     

                    Each STYLE NAME (1-20) has its own relationship table called PRODUCT MATCH 1... PRODUCT MATCH 20

                     

                    Then it matches whatever i put in StyleName02 (for example)  to the Style Name in Product Match 2 and then populates all the relative info to that style.

                    Screen Shot 2016-09-08 at 4.40.38 PM.jpg

                    On each style, i have the Brand delegated by text selection (Astronomy, Ocean Current....)

                     

                    Also, on each PO, i have the Brand delegated by text selection (Astronomy, Ocean Current....)

                     

                    Im sure the way i have it set up isnt the most optimal but it works. 

                     

                    Hope this is enough info to help you help me. haha.

                     

                    thanks!

                    • 7. Re: populating data with multiple parameters?
                      malibux14x

                      I mean, i guess i could make multiple databases, one for each brand, and then when i choose the Brand on the PO it will change the fields to match that of the brand?

                       

                      If i put ASTRONOMY on the PO then the STYLE NAME fields will change to StyleName01Astro ... StyleName20Astro

                       

                      and

                       

                      If i put Ocean Current on the PO then the STYLE NAME fields will change to StyleName01OC ... StyleName20OC

                       

                      Seems like the long way though.

                      • 8. Re: populating data with multiple parameters?
                        beverly

                        multiple databases/tables (one for each brand) would be the wrong design. However a Brand table and a Product table would be appropriate. Then each product has the brand "id" (unique primary key field associated with the Brand) as a "foreign key". if both tables are related on these fields, then many things are possible.

                         

                        but trying to get valuelists populated from different brand/product tables would be difficult.

                         

                        beverly

                        • 9. Re: populating data with multiple parameters?
                          beverly

                          more...

                          if a product can have many brands, a JOIN table would be advantageous.

                          beverly

                          • 10. Re: populating data with multiple parameters?
                            malibux14x

                            OOOOH... whats a JOIN TABLE?  never heard of that one

                            • 11. Re: populating data with multiple parameters?
                              David Moyer

                              Beverly:  multiple databases/tables (one for each brand) would be the wrong design.

                              To add to Beverly's note, my design is not the right design either.  I made it as a concept piece.

                              I think that the main point is to "not repeat any data".  That is, ultimately, you'd have one table for every level of hierarchy, with serial numbers used as ID's representing the attributes on each of those levels.  One table for PO's, one for brands, one for products, etc.

                              • 12. Re: populating data with multiple parameters?
                                malibux14x

                                Ok this is starting to make a little more sense. 

                                 

                                I have a PO table, and 20 Product Match tables (one corresponding for each Style Name foreign field  on the PO table)

                                 

                                So if i created a Brand Table will it link in between these two tables or before one?

                                 

                                Im just confused on how i would set up that relationship.

                                 

                                Thanks again guys, i know its frustrating working with a noob.

                                • 13. Re: populating data with multiple parameters?
                                  beverly

                                  JOIN TABLE is between two other tables and "mostly" has the ID's from the other tables and unique info:

                                   

                                  Products::ProductID -> ProdBrands::ProductID

                                                                        ProdBrands::BrandID<- Brands::BrandID

                                   

                                  this allow a "many-to-many" relationship where a brand may have many products and a product may be produced by many brands (but have unique features for that product by each brand)

                                  • 14. Re: populating data with multiple parameters?
                                    coherentkris

                                    You should really read up on data modeling and normalization.

                                    This will give you a solid foundation and way of thinking about the data and how to fit it into the relational model.

                                    Investing the time at the beginning will prevent you from having to redesign as you learn.

                                    Database normalization - Wikipedia, the free encyclopedia

                                    1 of 1 people found this helpful