Hi,

 

I'm importing a worked hours dataset from Excel every 30 minutes and need to find new Employee and Job records within the dataset.  The Employees and Jobs also have unique ID's which I suppose should make it easier.

 

I thought I could somehow compare the newly uploaded data to the Employee table using the unique ID and find those records in the new data that don't have a corresponding record in the Employee table?  I thought this would be easy ... I tried a 'not equal' join between the ID field in the Employee table and new data Employee ID field and expected a list of data for Employees not in Employee table?

 

Any thoughts?  I'd be very grateful?

 

Thanks in advance,

 

Simon

Use List(Job::EmployeeID) to get a list of all employees in the Job table, then switch to Employee table and do a Find with "constrain" on all those employeeIDs?

I would expect that to find them quickly.

Here is another approach.  The relationship between the two tables should be based upon:

 

Employees::EmployeeID = Jobs::EmployeeID

 

Then, while on a Jobs layout create the following script:

 

Enter Find Mode [ uncheck pause ]

Set Field [ Employees::EmployeeID ; "*" ]

Set Error Capture [ on ]

Perform Find[]

# ... this gives you all Jobs records which HAVE a matching Employee record

Show Omitted

# ... this will now give you all Jobs where there is no matching Employee record