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

    How to create a "vlookup" like in Excel

    wilz

      Title

      How to create a "vlookup" like in Excel

      Post

           More Lookups Q

           Background: 
           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:

      Commission_Table
           FK_ProjectID
           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:

      Project_Table
           PK_ProjectID
           NumberofStudents
           StudentFees
           Percentage          
           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 

      Commission_Table
           FK_ProjectID
           StudentNumber
           Commission

           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 
           http://forums.filemaker.com/posts/30ef5326e6


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


           Willie 
            

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

               There are some online resources you may find useful.


          http://www.filemaker.com/11help/html/create_db.8.2.html#1027557
          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:
          <SNIP>

          http://help.filemaker.com/app/answers/detail/a_id/3248/related/1
          Relational Database Design 101 (part 1 of 3): Designing a Flat File Database


          http://help.filemaker.com/app/answers/detail/a_id/3247/related/1
          Relational Database Design 101 (part 2 of 3)

          http://help.filemaker.com/app/answers/detail/a_id/3246/related/1
          Relational Database Design 101 (part 3 of 3)

          The White Paper for FMP Novices
               http://foundationdbs.com/downloads.html
               
               Key Concepts in Filemaker 7 (PDF)
               http://www.ddbainbridgeassoc.com/upload/images/fm7_key_concepts.pdf
               
               Filemaker Free - Listing of free resources  - 
               http://filemakerfree.com/

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

                 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: 

            ProjectOverviewTAble
                 PK_ProjectID
                 NumberOfStudents
                 Commission
                 OneStudnetCommissionRate       (This is the commission rate if there is 1 student)
                 TwoStudnetCommissionRate       (This is the commission rate if there is 2 students)
                 ThreeStudnetCommissionRate
                 FourStudnetCommissionRate

                 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.  

                 Cheers

                  



                  

            • 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.

              https://www.briandunning.com/cf/1740

              Best wishes,
              Doug Staubach