Need some help

[laker_42]

I posted this question earlier and I thought I had come up with a solution for it but it isn't working. So I will try to explain what I am doing.

We have 8 different dbs with one of them being an index db that only contains links to the other dbs. The 8 dbs contain different forms that our Human Resources uses (Ex. Termination, Promotions, New Employee...). What they want to be able to do is have a search button on the index db that will allow them to search the other 8 dbs to see if an employee has a record created in any of the dbs. If they do, then they would like to be able to see which dbs the employee is in. I tried writing a script that would copy the employee number and then call an external script in each of the dbs. Then enter find mode and paste the employee number into the corresponding field and perform a find. I then returned the results using a similar tactic by capturing the currentFindCount in a field and copying that then pasting it back into the Index db. I would then recopy the employee number and do the whole thing over again for each file. It worked the first time I ran it but after I closed the dbs and reopened them the next day, the copy and paste functions got messed up. I really didn't want to use this approach but I didn't have anything else. Does anyone have any ideas for me? Do I need to go through and create some relationships inorder to accomplish this?

Thanks for any help you can offer!

John

Sorry for the long post.

[Kurt Knippel]

Hmm, seems like an overly complicated system to me. Any possibility of combing those databases into a single EmployeeRecord.fp5 database? Your life would be made much easier.

Otherwise I migh suggest displaying a screen in the "IndexDB" which shows a portal to each of the other DBs by a user entered Employee number.

[trevorg]

Relationships is the way to go. You would then have a lot of power at your disposal without having to copy/paste/run scripts/jump around all the databases.

With the Relationship, as soon as you choose your Employee and set the Employee's ID (Primary Key), all the data from the other databases would be instantly available.

Use the IsValid calculation function to determine if the Employee has any records on the other end of the relationship. (Foreign Keys).

Then you can create some fancy dynamic statistics such as Counts, Sums, etc.. all from within the Index File.