3 Replies Latest reply on Apr 6, 2010 1:55 PM by philmodjunk

    Custom Value List with Related Fields

    dvarsam

      Title

      Custom Value List with Related Fields

      Post

      Hello.

       

      I have downloaded the Trial version of FileMaker Pro.

      I have managed to connect to MS SQL Database.

       

      I am evaluating whether I can use "FileMaker Pro" in my business.

      I am experiencing the following difficulty:

       

      Tables are shown here:

      [IMG]http://i69.photobucket.com/albums/i58/dvarsam/FileMaker%20Pro/FileMakerProTables.jpg[/IMG]

       

      I have created a Layout with the following Fields:

       

      a. CompanyCode (i.e. Drop-down with company.codeid & company.name values where comid is saved into table "adv_machines_fintrade.comid" )

      b. CustomerCode (i.e. Drop-down  with customer.code & customer.name values where code is saved into table "adv_machines_fintrade.CusCode" )

      c. CustomerName (i.e. a Plain Field which just displays the Name of the Customer depending on what I select on step "b" above.

       

      Sample Data:

       

      Table Company:

      1. Value "1" (means Company named "Test")

      2. Value "2" (means Company named "Coca-Cola")

      3. Value "3" (means Company named "Pepsi")

      4. Value "4" (means Company named "Hot Springs")

      5. etc. etc.

       

      Table Customer:

      1. Values "2", "01256", "Tony", etc. (means on Company "2", Customer  with number "01256" is Mr. "Tony")

      2. Values "3", "01256", "Tony", etc. (Mr. "Tony" is also a "Pepsi" Customer)

      3. Values "4", "01256", "Tony", etc. (Mr. "Tony" is also a "Hot Springs" Customer)

      4. Values "2", "00909", "Bob", etc.

      5. etc. etc.

       

      Table adv_machines_fintrade:

      This table is connected on the Layout Fields I have created...

      All my data is saved on this Table.

       

      Questions:

       

      1. On the above "a" (CompanyCode) Drop-down I want to be able to exclude from the Dropdown "where comid=1" because such a company does not exist on my Database (data is junk & I can not Delete).

      Is there a way I can exclude values from the Drop-down menu by adding a command "where comid<>1"?

       

      2. If on the Layout I have created, I decide to make an Entry for Comid=2 (Company 2="Coca-Cola" company), when I am to select the Customer.Code, the Drop-down should ONLY show Customer Codes that exist for that Company 2 (i.e. for the "Coca-Cola" company).

      Example: If on step "a", I chose company=2, then the customer Drop-down should NOT show Table Customer Records 2 & 3.

      How can I create a "conditional Drop-down list" depending on a separate Layout Field (i.e. comid)?

       

      3. When on above question "2", I select Customer Code, I want to know on which customer I am about to write the order for, i.e. If I select customer code "01256", I want to be able to SEE that this customer is Mr. "Tony" - I can't memorize all customer Codes-Names!!!

      So this field outputs the Name of customer depending on the Code I have typed in above Question 2.

      And this Field must be locked for Editing (only viewing is allowed).

      How can I do that?

       

      Any help is appreciated.

       

      dvarsam

       

        • 1. Re: Custom Value List with Related Fields
          philmodjunk

          In addition to helping you out with conditional value lists, I also see where you can improve your database with some structural changes.

           

          Your customer table would be easier to work with if you had only one record for each customer. ("Tony/01256" would only be found in one record). You'd add an additional related table to list all the company ID's to which a given customer record is linked.

           

          Customer--<Customer_Company>----Company   (--< means one to many)

           

          For more on this look up "join table" and "many to many relationships" in this forum and any other resources you have on databases and Filemaker Pro.

           

          There's a good article on conditional value lists in the Knowledge Base (click the support link, then pull down the support menu to select Knowledge Base.)

          Also there's a tutorial on the subject in this forum: Custom Value List? Option 1 describes a way you could exclude value 1 from your list. It can be used in combination with Option 2 to do what you want here.

           

          However, this statement puzzles me: "data is junk & I can not Delete."  If you have full access to this file--which surely you do or you couldn't make these design changes--why can't you simply delete this record? An alternative is to simly clear the field storing value 1 and that will drop that value out of your value list.

           

          "If I select customer code "01256", I want to be able to SEE that this customer is Mr. "Tony" - I can't memorize all customer Codes-Names!!!"

          You can set up your values lists so that customer code is in column 1 and the name is shown in column 2. Thus, you can find "Tony", but selecting that individual actually enters the code number. You can place the related name field next to your drop down field on your layout and can select a behavior option (In Inspector in FMP 11, in Field/Control | Behavior... in FMP 10) to deny access to this field while in browse mode to prevent people from editing the name.

           

          Once you get that working, you may want to make things easier to use with a more sophisticated design that makes it easier to search by name, but enters the ID number automatically once you've made your selection: Tutorial: How to use an auto-complete drop down list when selecting records related by ID number

           

           

           

          • 2. Re: Custom Value List with Related Fields
            dvarsam

            Hello again & thank you for your quick responce.

             

            I will try to respond to your suggestions, one at a time:

             

            > ... you can improve your database with some structural changes.

            > Your customer table would be easier to work with if you had only one record for each customer. ("Tony/01256" would only be found in one record).

            > You'd add an additional related table to list all the company ID's to which a given customer record is linked.

             

            The above suggestion is something that I can NOT do, since all the above info are pulled from an ERP Database System.

            And I can NOT change how our ERP currently functions.

             

            We have 4 companies & a Customer is defined from either:

             

            1. Customer_ID (auto increment)

            2. Customer_CompanyId (referred to as "comid") & Customer_Code (together)

             

            I am trying to Design a Flexible FileMaker Pro Layout Form - I want with a SINGLE form to be able to Serve all 4 companies.

            Otherwise I will have to Design 4 separate Layouts for every single operation I might want to implement... & that is a lot of pain...

             

            On the first Question I designed a View so that I can limit the number of Companies shown & linked the Drop-down to the View instead of the Table. So this was solved. However I would prefer if I did NOT have to do this since creating numerous View slows down the Database...

             

            > However, this statement puzzles me: "data is junk & I can not Delete."  If you have full access to this file--which surely you do or you couldn't make

            > these design changes--why can't you simply delete this record? An alternative is to simly clear the field storing value 1 and that will drop that

            > value out of your value list.

             

            Basically the MS SQL Database is consisted of about 600-700 Tables & around 100 Views.

            There are "constraints" setup so that if there are Records kept in different Tables, the company=1 line would not delete from Company Table.

            I do not want to Mess with Database Constraints, since multiple Tables are related to Company=1.

            The best way to go with this would be to delete all related records to Company=1 from all Tables within the Database...

            But I assume that this would be very "time consuming"/"waste of time" than to find a way to bypass this minor problem.

            Company=1 was setup so that Our Company could see what the ERP could do in a DEMO Database.

            Since we started using this Database nobody bothered to delete the data related to Company=1 (Demo ERP).

            Now on the User Standpoint, I wouldn't want users to mess up with wrong company (i.e. company=1)

             

            > If I select customer code "01256", I want to be able to SEE that this customer is Mr. "Tony" - I can't memorize all customer Codes-Names!!!"

            > You can set up your values lists so that customer code is in column 1 and the name is shown in column 2. Thus, you can find "Tony", but

            > selecting that individual actually enters the code number. You can place the related name field next to your drop down field on your layout and can

            > select a behavior option (In Inspector in FMP 11, in Field/Control | Behavior... in FMP 10) to deny access to this field while in browse mode to

            > prevent people from editing the name.

             

            Well not exactly because I want the following from the list:

             

            ONLYforCompany 2, List of "Customer_Code - Customer_Name".

            Without limiting the "Company=2" in my Example above, I would get "01256-Mr. Tony" 2 twice!!!

             

            And in our companies, 99,9% of the times we have every customer opened at minimun in 3 companies (an SQL Stored Procedure is handling that pretty well).

             

            So "Mr. Tony" would definately show up (not twice but) 3 times!

             

            > ... You can place the related name field next to your drop down field on your layout and can select a behavior option (In Inspector in FMP 11, in

            > Field/Control | Behavior... in FMP 10) to deny access to this field while in browse mode to prevent people from editing the name.

             

            This is very useful. Thanks.

             

            > Once you get that working, you may want to make things easier to use with a more sophisticated design that makes it easier to search by name,

            > but enters the ID number automatically once you've made your selection:

            > Tutorial: How to use an auto-complete drop down list when selecting records related by ID number

             

            Basically I would want to Search by either:

             

            - Customer_Code, or by

            - Customer_Name.

             

            In any case if one of the two is filled the other one should be auto-filled since these two are co-related/work together.

            ... and I don't know how to do that either...

             

            The above provided link seems very useful. I will have to work on that tomorrow & then come back for questions.

            ... in the meantime I will try to (hopefully) find some support in my Country (Greece), because the things that I want to do seem to be difficult if not impossible...

             

            Question:

            Does FileMaker Pro provlide any Demo Tables/Databases, so that I can see some sample tables/code & how they work, so that I can jump-start faster?

            For example: it would be nice if the above "search by name" was embedded in FileMaker's Sample Tables, so user's get acquainted faster...

             

            dvarsam.

             

            • 3. Re: Custom Value List with Related Fields
              philmodjunk

              Thanks for the addtional information. I missed the reference to using My SQL as your source. That complicates things.

               

              "The above suggestion is something that I can NOT do, since all the above info are pulled from an ERP Database System.

              And I can NOT change how our ERP currently functions."

              I'm not an expert on using external tables from MY SQL, but you should still be able to do this. You can Create such a table in Filemaker and link it to your external tables. Such a table could greatly simplify some othe typical database operations you are likely to need how to do.

               

              Perhaps Mr_Vodka can chime in with more on this as he's helped any number of folks with this kind of database set up.

               

              I really don't see how you are going to get this to work for you:

               

              We have 4 companies & a Customer is defined from either:

               

              1. Customer_ID (auto increment)

              2. Customer_CompanyId (referred to as "comid") & Customer_Code (together)

               

              One way or another, setting up a single field with a value that uniquely identifies each customer will make life much easier for you.

               

              "I am trying to Design a Flexible FileMaker Pro Layout Form..."

              I understood that from your original post, however, layouts are intimately linked to the table occurrences you set up in your Relationship Graph. A less than optimum design in your graph will result in problems for your layout. This includes getting conditional value lists to work for you.

               

              "Well not exactly because I want the following..."

              If your conditional value list is correctly filtering your values to just one company, "Tony, and his number" will only appear once in the value list.

               

              With regards to your last question, I directed you to a message I posted that documents my personal solution to the problem of using a name to look up a unique ID number for in Filemaker. Hence, it's not found in any of the templates supplied with filemaker pro. You'll find a number of links to demo files in this forum and other filemaker forums. You may be able to find something useful by using the search button above or google to search using various key words of interest to you.