11 Replies Latest reply on Oct 31, 2011 2:45 PM by philmodjunk

    Switching between input criteria

    Malibux14x

      Title

      Switching between input criteria

      Post

      hello.

       

      i have a PO database.  when i type in the name of the product, the style #, price and color all are auto inputed.  however, sometimes people just give me the style ID.  is there a way to switch between the style name and style number as prefered input preference?

       

      i know some may say its better to use style ID but this is the way i have the whole system set up and works best using the style name as default.

       

      my idea was to click a radio button or something and have it switch to the style ID bc i dont htink you can have both bc they will be conflicting correct?

       

      thnaks ffor the help.

      POs.JPG

        • 1. Re: Switching between input criteria
          philmodjunk

          Why not set this up so that clicking/tabbing into the Style ID field opens a value list of ID's and Style Names? (You can choose to hide the StyleID's in this list if you only want to display style names.) Selecting a Style Name from this list would then enter the Style ID number and the Style Name can automatically appear in the Style Name Field you've located as the next column on your layout here.

          • 2. Re: Switching between input criteria
            Malibux14x

            i have that set up for the style name.  and all other info is linked to that info inputted. 

            i dont have the style ID linked so cant even input anything in that field.

            i thought that if i had two fields that i could input linked info into they conpete with one another?

            is there something i can take a snapshot of that would make it easier to follow?  im afraid im not experienced enough to cleary get my ppoint across.

             

            sorry

            • 3. Re: Switching between input criteria
              Malibux14x

              and i need all the info listed when i send the PO on to make sure they get the order correct

              • 4. Re: Switching between input criteria
                philmodjunk

                I think you have been quite clear, but your design choices are not the best you could have made here.

                You should link by ID instead of name. Using the name as the key field for your relationship complicates your system in some major ways.

                Imagine the problems that could insue if you have to change a file name, for example or if someone mis-enters a style name, several related records are created and then the error is discovered...

                and i need all the info listed when i send the PO on to make sure they get the order correct

                Using ID numbers instead of names does not prevent you from doing that. Take a look at either the Invoices or Purchase Order starter soultions that come with FileMaker. Both use ID numbers, but the product names are clearly displayed on each invoice and purchase order for the customer to see and verify.

                • 5. Re: Switching between input criteria
                  Malibux14x

                  i see youy point but with this system in Clothes, one style that may come in multiple colors share the style ID.  (unfortunatley i dont make the ID numbers or that ouwld change).  so i may have the ELITE in black being W211134 and the ELITE in white being W211134.  so if i put the style number in, the color isnt determined.  and when im linking photos to this it can get complicated.  so i have the Style name as ELITE WHite and ELITE black.

                   

                  i get that the other way may make sense in other industries where there are unique style ID per sku, but here it is different.  i may go back one day and redesign the whol thing but for now i relalyl like this system but wnat to find a fix if possible.

                   

                  bc if ti were the other way around and i link it to ID then if someone gave me the Name i would need to go to the database and search the name for the ID.  same problem just reveresed.  plus people mess up a lot on the style number which brings me to the style database trying to seach for the info.

                   

                  as you can see it can be confusing either way but since i am already set up this way i wanted to see if there was a way that i could switch, on the fly, between search criteria.

                  • 6. Re: Switching between input criteria
                    philmodjunk

                    i see youy point but with this system in Clothes, one style that may come in multiple colors share the style ID.  (unfortunatley i dont make the ID numbers or that ouwld change).  so i may have the ELITE in black being W211134 and the ELITE in white being W211134.  so if i put the style number in, the color isnt determined.  and when im linking photos to this it can get complicated.  so i have the Style name as ELITE WHite and ELITE black.

                    You would have the same problem with the Style Name as well since your style name is also not unique here. I don't see where that's really a problem. In both cases, data entry in additional fields fills in the remaining information. Your relationship between the table shown and a table of styles might look like this:

                    LineItems::StyleID = Styles::StyleID

                    The styles table might have as few as two fields: StyleID and StyleName and would server to set up your value list of both StyleID's and the associated style name. given that relationship, selecting an ID also selects and enters the name so this can work both for selecting by name or by ID number. Unlike a product ID number, it would not be intended to uniquely identify the product as you need additional info to do that.

                    bc if ti were the other way around and i link it to ID then if someone gave me the Name i would need to go to the database and search the name for the ID.  same problem just reveresed.  plus people mess up a lot on the style number which brings me to the style database trying to seach for the info.

                    What I am suggesting handles all of those issues for you...

                    It really shouldn't be confusing, because this one value list should be able to list both ID numbers and names in one value list so that uses can select by either name or ID number.

                    • 7. Re: Switching between input criteria
                      Malibux14x

                      i like what you are saying but do not quite get how to set it up that way.

                       

                      right now i have three consolidated three databases (from your recomendation before) to one.  Clients, Styles, POs.  now i have the one with three different Layout linking them togehter.

                       

                      the styles are link through the style name.  (i type the style name and all other info comes up).  how would i be able to have both; style name and style id?

                       

                      to avoid repeating values problems i have 20 tables in the relationship graph (Product MAtch 1-20) linking the style name to each line in the PO. 

                       

                      i feel my database is a mess but is working..

                       

                      if i could somehow switch it up to make it so i can either input the style name or the style ID from the PO that would be amazing.

                       

                       

                      • 8. Re: Switching between input criteria
                        philmodjunk

                        to avoid repeating values problems i have 20 tables in the relationship graph (Product MAtch 1-20) linking the style name to each line in the PO.

                        I agree that this is a "mess" and am suprised that it works for you given that complexity. (Hopefully, these are 20 different table occurrences not actual tables or you've got an even bigger mess than I think here.)

                        I recommend a drastic structural change. Take a look at this very simple Demo file created by another forum participant named Comment:  http://fmforums.com/forum/showpost.php?post/309136/

                        Notice how a related table is used with a portal for listing individual items on the invoice. (Yes, this example is an invoicing example and you have a purchase order system, but the relationships work identically--the only real difference is that you are buying instead of selling.)

                        That approach should eliminate the need for "1o different table[occurrence]s" in your relationship graph.

                        Now define a Styles table with at lest two fields: StyleID, StyleName. You can type style ID's and matching names into this table or you may be able to import the data to save typing.

                        In your LineItems table, define a StyleID field, but do NOT define a style name field.

                        Create this relationship between LineItems and the Styles table:

                        LineItems::StyleID = Styles::StyleID

                        Now put LineItems::StyleID in your Line Items portal and format it with a drop down list. Use Manage value lists with the specify field option to list StyleID from Styles in Field1 and StyleName from Styles in Field 2.

                        On your layout, place Styles::StyleName next to in the portal row.

                        Now, in browse mode, when you select a style from this drop down, you'll see the style ID number in the Style ID field and the Style Name entered in the Style name field.

                        • 9. Re: Switching between input criteria
                          RonCates

                           Quick observation here. I believe the style ID the OP is refferring to is not the same as a unique filemaker generated ID which should be used in the relationsghip.

                          "sometimes people just give me the style ID"

                          " (unfortunatley i dont make the ID numbers or that ouwld change)"

                          If styleID is an auto generated serial number field then please disreguard.

                          • 10. Re: Switching between input criteria
                            Malibux14x

                            no you are correct Ron.

                            the designers give me the style ID so that doesnt change nor can i change it.  i can altrer the name hence ELITE black and ELITE white.  but they both share the same style number (ID)

                            • 11. Re: Switching between input criteria
                              philmodjunk

                              That was also my interpretation of what we have here. That Style ID was a non-unique externally generated value as apposed to a unique, auto-entered serial number generated inside FileMaker.