6 Replies Latest reply on Sep 18, 2013 11:30 AM by Matty_1

    Auto Calculation help



      Auto Calculation help



           I have an auto calculation for a field called EmployeeName that refers to another field called EmployeeNumber.  When employee number is left empty, it also clears itself.  Otherwise when an employee number is selected, it called upon it's related record and fetches the employee number.  This all works perfectly fine and is achieve by the simple calculation that follows:

           If ( IsEmpty ( EmployeeNumber ) = True ; "" ; PersonnelRecords_RunDetails::Employee Name )

           Currently I've layered the two fields one on top of the other so that the user can select the drop down menu, chose an employee number and then only see the employee's name afterwards.  The user also has the ability to type in that field and I'd like it if the user could type whatever he or she wants.  If no relational name comes up, that is if the user types a random name instead of a number from the drop-down, the formula defaults to whatever the user has typed manually thus allowing the user to insert names that aren't part of our employee pool.


           Let me know if this is possible, my attempts have not yielded the desired outcome.

        • 1. Re: Auto Calculation help

               Hi guys, wondering if someone has any insight on this?  Let me know :)

          • 2. Re: Auto Calculation help

                 You don't need any calculation field. For demo purposes, put PersonnelRecords_RunDetails::Employee Name on your layout and then use your value list to select an employee number in the EmployeeNumber field. You should see the name from that table appear automatically when a number is selected in the EmployeeNumber field. If you give the name field an opaque fill color, use the behavior settings in the Inspector to disable browse mode field access and place it on top of the EmployeeNumber field, you can then click on the name field to see the drop down list deploy so that you can select an employee.

                 But this doesn't give you the ability to type in an employee's name (or part of their name) and see the EmployeeNumber field update with the correct value. This is possible, but takes a different approach and needs a script to get the job done. Here's a demo file of the method:

                 FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                 Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7

                 This demo file, BTW, allows you to select employees by name even if you have two employees with exactly the same name.


            • 3. Re: Auto Calculation help

              I don't understand well your post, but you can try script triggers on that field. Could you post some image?

                   '= True' in your calculation is not neccesary.


              • 4. Re: Auto Calculation help

                     Hi Phil, I have already done what you mentioned.  But what I'm looking to achieve is something else.  I want to have the option of selecting an employee number and having the name populate on the EmployeeName field which already works.  They are layered one on top of the other and the browse function is disabled in the name field.  What I also want is to be able to type in whatever I want in the EmployeeNumber field.  If it's not a valid employee number the EmployeeName field then takes whatever is typed in the EmployeeNumber field otherwise it pulls the proper name from the Employee database.  This would give us the ability to type "Joe's Trucking" when work is performed by a third party.


                     Also note I mistyped one thing, here's the correct that might clear up some confusion.


                Otherwise when an employee number is selected, it calls upon it's related record and fetches the EMPLOYEE'S NAME.

                • 5. Re: Auto Calculation help

                       Please see the demo file. I would never, ever, have users type a name into the Employee Number field, but if you check out the demo, this is not necessary to get what you want.

                  • 6. Re: Auto Calculation help

                         Well ages ago I had gone the separate field route for this second level of information but for some reason I had removed all of it and felt if I could make one field smarter and make the decisions for the user I could simplify things and have less clutter on the layout but you are right Phil, it's two separate kinds of data which shoudln't be mingling so I've gone back to the original idea of having a field for employee numbers and a field for third party worker names.


                         Thank you.