How to get data from 1 table to another

[Rizal Az]

Hi all,

I hope that I'm in the right forum to ask this question. I am newbies in FileMaker and encounter rather annoying problem.

I have 2 tables; 1 consist of employee ID & employee name and another table consist of Employee name and everything else(address, phone, etc). I want to get an employee ID from one table and put it in another table based on Employee Name. How do I do that?

Thank you for your help.

[LaRetta]

Welcome to FM Forums, Rizal!

The two tables should be joined on EmployeeID. Then, in your second table where you want the EmployeeID, you create an EmployeeID field. To this field, you will attach a value list as a pop-up. Defining the value list would be:

Based on records from Employees table and all records. In the left side, you select the EmployeeID. In the right, you select the employee name. Below, specify show values only from second field. Then, when you select the pop-up (based upon this value list) which is attached to your EmployeeID field in your second table, the user can select the employee name but the EmployeeID is entered in the field.

BTW, you do not need NOR WANT the employee name in the second table. You can simply display the employee name by placing the Employees::Employee name directly on any layout in the second table (since they will be related). And, where you select the employeeID, it will display the employee name as well.

If any of this is a bit unclear, ask away ... there are many wonderful people here willing to assist!

Edited March 9, 2009 by Guest

[Rizal Az]

Hi LaRetta,

Thank you for the reply. Actually, I don't want to involve the users yet. I want to automatically copy the EmployeeID values from 1 table to another (kin of like vlookup in MsExcel) I have column EmployeeID & EmployeeName in 2 different tables. Since, only EmployeeName are actually filled in both table, I tried to use EmployeeName as a connection, just like this

Go to Record (First)

Loop

if ( table1::EmployeeName = table2::EmployeeName; table1::employeeID; 'none' )

endif

Go to Record (Next, exit after last)

EndLoop

But somehow the script didn't work the way I imagine it to work. Any idea?

[LaRetta]

Users? I said nothing about users. Let's assume that the table with EmployeeID and EmployeeName is your Employee table. And it has proper values in both fields through all the employee records. And you've been given another table, maybe called Assignments. And it contains EmployeeName. It also has a field for EmployeeID but that field is empty. And THAT is the field you wish to fill with the ID.

So based upon the naming and assumptions above, create a relationship as:

Assignments::EmployeeName = Employees::EmployeeName

Then, while on a layout based upon Assignments, do this (and back up your file first)

Show All Records then place your cursor in the currently empty EmployeeID field. Select Records > Replace Field Contents and select 'Replace with calculated result'. In the dialog which opens, select (double-click) your Contacts::EmployeeID field to insert it in the dialog. Say OK, then 'Replace'.

After it is done, it should have filled in the EmployeeID in all records. To be sure, perform a search for = in the field. If any are blank, it means that the name didn't match exactly and you will need to manually find the Employee ID for them. Once you have this in place, delete the EmployeeName from your Assignments table - you do not want it in both places. And then change the relationship so it is based upon EmployeeID as it should be. :wink2:

Edited March 11, 2009 by Guest

[Rizal Az]

I never thought of it to be that easy!!! duh!

Thanks LaRetta, it works!