AnsweredAssumed Answered

How to create a "vlookup" like in Excel

Question asked by wilz on Dec 9, 2012
Latest reply on Feb 13, 2015 by Doug Staubach

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 
      

Outcomes