Hi there.

I am using FMPro 5 on Windows and Mac (with web interface in mind).

I have created a relational contacts database. It has three databases: jobs, companies and contacts. The contacts database has lookups to the other databases in order to associate a contact with a job and a company. A contact may be associated with a number of jobs so I created 10 job lookup fields. In order to search through these I concatenated the selections with a space using the & operator into a text field (eg Job numbers 1027 899 456 etc). If you then do a 'contains' search on this field you would search accross the string. This works OK except if the contact works on an 11th job and a user amends the 1st job lookup to the new job then the text string would change to reflect this.

I would like to maintain a history of jobs this contact has worked on without having too many job selection fields. Is is possible to somehow append changes to the text string when the field is changed while also checking for any duplication if a user associates a contact with the job twice?

Hope this is clear! Thanks in advance.

It is clear.

IMO, you need to change the way you are looking at the jobs being done by the employees, from a calculation to a portal.

I would incorporate an employee id number into the job files, and then set up a relationship to the job file using it. A portal set up using this relationship should show you all of the jobs that employee is involved with, and by sorting this by date, and only showing 10 rows in your portal, it should give you the results you desire as I understand them.

HTH

Lee

Hi. Thanks for the response.

Sorry, maybe I'm confusing the issue. My Job database contains 2 fields, Job Number and Job Title. In the contacts database I have 10 Pop up lists, JobNumber, JobNumber2 etc looking at the job numbers. To associate the contact with a job is a matter of selecting one from the Pop-up list with a possible 10 jobs a contact can work on (the job database may contain hundreds of jobs). These selections are concatenated by having a Job combination field with a & " " operator effectively creating a string eg 1027 9999 etc. If I want to find which employees worked on job 9999 I would search the combination field. But if an employee needs to work on the 11th job you could change JobNumber1 to the new value which would also alter the JobNumberCombo field thus nullifying any history.

How would I setup a portal to satisfy a history? Are you saying that the job database has a field which records employee ID numbers. How do I set this up exactly?

Sorry if I'm not grasping this!

Regards

Adam

I think the solution is to use 0001 instead of 1.