3 Replies Latest reply on Feb 13, 2015 11:04 AM by Doug Staubach

    How to create a "vlookup" like in Excel



      How to create a "vlookup" like in Excel


           More Lookups Q

           I run a number of English projects that last for 4 weeks each.  Each project cosists of a number of Classes (project A has 2 classes  10:00-11:00 is English level 1, 11:00-12:00 is English level 2).  For each class I have to give a % commission to my partner based on the number of student numbers enrolled.  Also For each Project, the % commission might be different.  The commissions might change over time too, so I want a record of what was used before.  

           When I used Excel I had 1 spreadsheet for each project, and a "table" to tell me what the commission was.  I used a "vlookup" formula to find and return the % commission. 

           So I had something that looked like this : 
           Student Number     Commission
                    1                                  10%
                    2                                  10%
                    3                                  25%
                    4                                  30%

           The problem: If the commissions were always all the same, then I know how to do it.  But becuase it can change, I need to make a record of the % for each project. 

           I created a table for commissions:

           1StuPercnt        (to record % for 1 student) 
           2StuPercnt        (to record % for 2 students) 
           3StuPercnt        (to record % for 3 students) 
           4StuPercnt        (to record % for 4 students) 

           and a project table to manage the projects:

           MyProfit (This would be StudentFees x Commission)

      What I would like: 
           When viewing the Project_Table I would like to enter the student number then have the commission field update automatically (from the commission table) and then show me MyProfit.  

      Possible Solutions:
           #1 Script:
      Iknow i can write a script and have it search the "commissions" table and then find the corrrect field, but that just seems complicated...  also for scripts do I have to click a button to run it each time? 

      #2 Coded into the formula itself.  BUT I dont want this as its compelex to update, change each time i open up a new project.

      #3 Different Table design
           Commission Table could be 


           but I find this "inefficient" (i.e. why can't I just store them all in one record? vs Creating 4 records for each project?) I also found this a bit troublesome to fill in each time i start a program.  When I create a new project I can sometimes copy values from a previous project.   But it seems easier if i had all the commission in one record rather than a separte record for each %.  

           I did search forum for the answers, but they seem to be a bit differnt from my situation: 
           http://forums.filemaker.com/posts/0488577112 and 

           Im still new to FM so apologies if its a real noob Q.. but i really cant seem to figure it out. 


        • 1. Re: How to create a "vlookup" like in Excel

               There are some online resources you may find useful.

          Home > Designing and creating databases > Creating a database > About planning a database
               A well-designed database promotes consistent data entry and retrieval, and reduces the existence of duplicate data among the database tables. Relational database tables work together to ensure that the correct data is available when you need it. It’s a good idea to plan a database on paper first.

               Follow these general steps to plan a database:

          Relational Database Design 101 (part 1 of 3): Designing a Flat File Database

          Relational Database Design 101 (part 2 of 3)

          Relational Database Design 101 (part 3 of 3)

          The White Paper for FMP Novices
               Key Concepts in Filemaker 7 (PDF)
               Filemaker Free - Listing of free resources  - 

          • 2. Re: How to create a "vlookup" like in Excel

                 Thanks for that, problem solved.  (if there is someone wiser than me please correct me!)

                 The reason I was struggling was becuase it was NOT an issue of tables or relationships. I tried to make one (pic below) and it just made no sense. 
                 BUT found that a simpler way was to use a formula. 
                 So my table now looks like this: 

                 OneStudnetCommissionRate       (This is the commission rate if there is 1 student)
                 TwoStudnetCommissionRate       (This is the commission rate if there is 2 students)

                 The main problem was that each project might have a different commission rate, and also the rate depeneded on the number of students enrolled (which mich change over the duration of the program) 

                 Sor for the "Commission" field, I used an "auto-entered -> Calculated Value" and used the follwing formula: 
                 Case (  NumberOfStudents=1 ; OneStudentCommRate ; 
                               NumberOfStudents=2 ; TwoStudentCommRate ;
                               NumberOfStudents=3; ThreeStudentCommRate;
                               NumberOfStudents=4; FourStudentCommRate;)

                 And this gave me the desired result of a "vlookup" with all the data in one table (rather than many), and let me look back historically to what happened in the past, and also let me simply "replicate" a previous project into a new one without having to type it all in again.  




            • 3. Re: How to create a "vlookup" like in Excel
              Doug Staubach

              Hi Willie:

              I created a custom function called VLookup that might meet your needs (not a sales pitch - it's free).

              You'll need to have FileMaker Pro Advanced (to create your own functions), and it would only work with version 12 or higher (because it uses ExecuteSQL and recursive loops).

              Just copy the source code from the following website, and paste it into a new function (called VLookup), using the menu command sequence File - Manage - Custom Functions.


              Best wishes,
              Doug Staubach