Jump to content

SQL union join


RobW
 Share

This topic is 5754 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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!!!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Newbies

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.

Link to comment
Share on other sites

This topic is 5754 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.