SQL union join

[RobW]

I am new to FM and I am trying the evaluation product. My client is looking for a project management system containing the usual things: estimates, invoices, receipts, correspondence, etc. I would like to combine these documents in a single table called "activities". Then I could display a history of activities e.g. rased an estimate, raised another, got go-ahead letter, raised sales invoice.

My problem is how to incorporate an activites table. In MS Access, for example, I would just create a "union join" of the various document tables using the fields "date", "desciption" and "amount". I do not believe that I can do unions in FM 8. So I need to populate a separate table. Could someone suggest the best approach for this.

The final objective is to create a screen list of activities that have been done with hyper links to the supporting documents.

Any ideas welcomed!!!

[Reed]

Why not originally design the system with an "activities" table, and incorporate a field called "activity type" which denotes whether the record refers to an estimate, invoice, etc.?

Otherwise, the easiest thing might be to create the separate activities table with this same activity type field, and import all the records from each of the disparate tables into it, while including the primary key value, and the table from which it came. The problem with this approach is that you will have to re-perform this import each time new records are added so you'll always be able to use it for navigation. It may be easier to create a one to one relationship to the activities table from each related table, and then use that relationship to auto-create a record in the 'join' table whenever a related record is created.

[RobW]

Thanks for your reply Reed. I don't like the second method that you mention because the nature of batch processes is that they become slow and possibly mischievious. So I will be going with the first approach.

The lack of replies has shown me that there are no simple solutions. However I have been reading about scripts. I think my best approach will be to incorporate the flag that you have recommended within actitity records. Each activity record being created via script attached to a "done" button or suchlike at the foot of the estimates/invoices/correspondence documents.

Thank you.