4 Replies Latest reply on Jul 26, 2014 8:46 PM by dbail22@comcast.net

    If, then, look up



      If, then, look up


           I'm sure that this is a simple thing to achieve but I'm not a programer. I do enjoy developing FM for my company.

           I trying to build a field that "IF" an employee's name is John "THEN" it will look up John rate of $20 and insert $20 in to another another field.

           I have an employee Table with their names and rate. A job Table with actual vers estimated cost/times. I'm developing a total actual labor cost and do not want to use an average employee rate.

           Thank you for your help.

        • 1. Re: If, then, look up

               Not enough info I'm afraid. What are the tables and how are they related?

          • 2. Re: If, then, look up

                 1) I'm not sure how to answer in brief and explain the tables relationship excepted to say that they are matched by a project number. 2) The table are not the focus of the question. The fields are. (If "field:Emp-Name" is John and if John's "field:Emp-Rate" is $20 than insert $20 in to "field:Rate"

                 I hope that this helps.

            • 3. Re: If, then, look up

                   It helps to explain what you are trying to achieve in 'real world' terms.  What is your goal?  For example, let's assume what you want is to generate an amount due based on a rate and a period.  Let's say for simplicity sake that you need this amount generated once per job.  'Jobs' are fundamental entities in your relationship model and have their own table.  You have a layout based on this table called JOBS where users handle information about particular jobs.  

                   Likewise you have a table called EMPLOYEES  In this very simple model, each employee can have multiple jobs but each job has only one employee (probably not your situation I know).  You have a classic parent-child relationship.  The person entering the job in your database selects the employee associate with the job and inputs the amount of hours the employee worked.  You now have everything you need- no more fields need to be entered or copied or input.

                   Even in this simple model, there are many possible solutions depending on your needs.   To display EMPLOYEES::Rate on the jobs layout, simply place the field on the layout.  If you want the EMPLOYEES::Rate captured for historical purposes, then you can write a script that triggers on employees selection to set JOB::Rate to equal EMPLOYEES::Rate.  To generate the amount due, simply create a field JOBS::Amount_due as a calc which equals EMPLOYEES::Rate * JOBS::Hours.  To generate an 'historical' amount due that will not change in the future if the Employees rate changes you can use an auto-enter calculation in the field 'options.'  

                   If none of these work for you, you will at least then know 'why' they don't work, which will give you a better sense of what you need.  However if this explanation is *way* to basic, than I would suggest posing your question with more technical detail.  If this answer is to complex or uninformative, than I'd suggest giving us more info on your 'real-world' goals.

                   You say the tables aren't the focus of the questions, however people on the forums are a community not free labor.  We all attempt to give answers according to best practices (as we understand them), not according to the desires of the poster.  The idea is to learn how to properly do things rather than setting terms for the answers you expect.  


              • 4. Re: If, then, look up

                     In either case you will need a relationship between the table you are entering data into and the table you are looking for johns rate.  It will probably have to be more than john=john since names are not unique enough.  It should be another field like where john has an employee number or john+lastname+birthdate related to the same info in the other file.  The the choice is simple.  If you want to record a snapshot of his salary on the date of entry you simply create another field and make it a lookup field to copy the contents of his rate into the table you are working on. It will stay at $20 until you force another lookup by manual or script step.  If it is meant to track his rate then make the field a calculation instead. That way it will update the rate each time the record is viewed.