Appropriate structure and approach

[rhody]

I'm not sure the best place to post this. It has to do with database organization, so I think this is the right forum (Database Schema and Business Logic), but I'm unclear as to the appropriate subforum.

Anyways, here's the question: the database needs to track opportunities and projects, and they need to be shown on different layouts.

Opportunities can sometimes, but not always, become projects. When an opportunity becomes a project it needs to disappear from the opportunities layout and appear on the projects layout.

Projects have almost all the fields that opportunities do (agency_id, name, address, etc.), and they also have a number of other fields (contract_date, permit_submitted_date, permit_received_date, etc.)

Would it make more sense, and is it possible in FileMaker, to use one table with a flag for project/opportunity and to show only the records with the opportunity flag on one layout and only those with the project flag on another layout? Then when an opportunity becomes a project all I'd have to do is change the flag.

If that's not possible, is it possible to do something like the SQL INSERT INTO projects (field1, field2, ...) SELECT field1, field2, ... FROM opportunities? If not, what would be the appropriate way to move the record from the opportunities table to the projects table?

[David McQueen]

Yes that is quite do-able.

1. The easiest way to accomplish this is to use a go to related record command for opportunities or projects to get all records for each.

2. You will have to script searches so that the search script puts in the correct flag behand the scenes to restrict user searches to either opportunities or projects.

3. This then leaves you open for some nice reporting on total opportunties versus complete projects versus ongoing projects versus no goes from the same table.

[rhody]

Thank you for the reply.

Would all interaction with each layout have to be scripted, then? When they go to the opportunities layout, would that have to be via a script that searched for project_opportunities with the flag set to opportunity? And then I'd have to disable the "Show All" button, or (not sure if this is possible) attach a script to the "Show All" button so that it actually only showed all records with the flag set to opportunity?