10 Replies Latest reply on Apr 22, 2010 4:58 PM by james211

    Pretty simple formual question



      Pretty simple formual question


      So I think what I want is pretty simple.  I have to two fields, one is a vendor name, the other is a vendor ID number.  What I want to happen is everytime I select a specific vendor name, the vendor ID field is automatically filled in with the proper ID number.  So for instance, if the vendor was CDW and the vendor number was 051, when I select CDW in the vendor name drop down list, 051 will automatically be filled in, in the vendor ID number field.


      Any help is appreciated.


      Thank you!



        • 1. Re: Pretty simple formual question

          On the VendorID field, use a pop-up (important).  Then your value list would be 'use values from field' and all values.  In the left pane, select VendorID (also important) and in the right pane, select your vendor name.


          Below, specify show values only from second field.


          This, when you select a vendor name, will insert the ID into the field.  You do not want the vendor name in the table (only the vendor ID).:smileyhappy:

          • 2. Re: Pretty simple formual question

            Apparently I've done something wrong some where.  Where do I define the values?  As in, CDW=051?  Right now I have two value lists, one with the name of the vendor names in alphabetical order, and the other with vendor ID's in numerical order. 

            • 3. Re: Pretty simple formual question

              You want to use one table called Vendors.  In there, you will have two fields (usually more) - a VendorID preferably a FM-generated unique auto-enter serial (see field Options > Auto-Enter tab) and the Vendor Name.  Then you create a value list (File > Manage Database > Value Lists) using all values from that table and spec the value list as I described above.

              • 4. Re: Pretty simple formual question

                I have uploaded a sample here (called Vendors.fp7)


                Pay particular attention to 1) the relationship graph, 2) the value list and 3) the field setup on the yellow field (this is where you attach your pop-up).  The field in green shows that same field (your VendorID) as regular text just so you can see that, although you are selecting the vendor name, it is inserting the vendor ID.


                Any information about the vendor, that you place now in the vendor table, can be easily displayed in other tables (such as Purchases) simply by taking the vendor field and placing it on the purchases layout.  Welcome to the wonderful world of normalized data and relationships ... you don't need a vendor name in all your tables, just the one vendor table.  Then you put the VendorID in the rest.


                Ask questions if you get stuck.  There are many wonderful people willing to assist!

                • 5. Re: Pretty simple formual question

                  ha..I give up.  Not sure why this is seemingly to be so hard for me, but I'm obviously a giant idiot, I'm not an avid filemaker user. 

                  • 6. Re: Pretty simple formual question

                    I don't show that you've even downloaded the file I provided.  It is simply too soon to give up; in fact, it is always too soon to give up.  You are no idiot.  You are doing things you've never done before.  We all feel lost at first. :smileyhappy:

                    • 7. Re: Pretty simple formual question

                      I didn't even see the sample before, thank you, I'll download it now. :smileyhappy:  I love nice people!

                      • 8. Re: Pretty simple formual question

                        Maybe I'm partially confused because I already have a set list of vendors with specific numbers that identify them.  Your sample uses an auto generated number which I'm not sure I can do since every vendor already has a specific number.  I'll keep working, I did notice a few things I didn't see before.  

                        • 9. Re: Pretty simple formual question

                          You say you already have a table with the VendorID and the VendorName?  Cool!


                          You don't NEED to create a new auto-enter serial but it is highly recommended that you use one to tie your relationships together.  What if someone types the wrong ID when they create a Vendor?  And you've created invoices or purchases etc from it?  If you then change the vendorID, you break your relationships (which depend upon this VendorID).


                          Truly, you may put that vendorID in a field for viewing by Users or to track external needs tied to that number, but best to let FM bind your relationships on meaningless auto-generated numbers where a human can't make a mistake.  Either way, this should move you forward.  Let us know how it goes.

                          • 10. Re: Pretty simple formual question

                            Okay, so I pretty much have it, the only problem I'm having now is not all of my vendors are showing up in the drop down menu in the main inventory layout/table, only four of them show up.  But when I look at the vendor layout/table all of them show up.  Any reason why this would happen?