Use the same tables for your client and potential client data. Use a field in the client table to mark them as either a potential or actual client. you will then not have any data to enter a second time when a potential client becomes and actual client.
Finds, sorts, portal filters can all be used to keep various views of your potential and actual client data separate even though the data is in the same set of related tables.
What PhilModJunk says os indeed the simplest solution.
Just have a field "Type" or something like that with two possible values:
- Potential Client
Then if you want to create a report that shows all clients you perform a fiend for the value "Client" in the Type field.
Tell us a little more about what kinds of reports you need to make? Are you using different related tables for other info?
A slightly more difficult option would be to have one table for Potential clients and another almost axactly the same table for actual clients.
A script would then be used to turn a "Potential Client" into a "client". That script could import the data from one table into the other.
But if you have data in related fields you would have to take that with you as well, or atleast update the Foreign key ID's.
So I think the simplest method is the one suggested by PhilModJunk.
Please remember that finds and stuff like that can all be automated / scripted.
You can create buttons for reports and those buttons can perform scripts that take you to another layout, perform a find, perform a sort etc. etc.
All you want.
A script would then be used to turn a "Potential Client" into a "client". That script could import the data from one table into the other
I like this idea, that is what i was thinking. Currently when a potential client becomes a client we end entering information all a over again into our database during the intake process.
Also In the Potential Client database we only capture the services requested but in the "Client" database we capture more details about the work being done. For example how much time was spent teaching the consumer how to use a computer, or how much time was his assestment etc.."Time tracking for Services"
In the Potential Client database we only capture the services requested but in the "Client" database we capture more details about the work being done.
Sounds like information that should not be stored in fields that are defined in the client table, but rather in related tables--records that can be linked to the original table that stores records for both potential and actual clients.
So, to come back to this. You have two options:
1) Create one table "Clients" and in there you use a field "type" that is either "Potential Client" or "Client".
That way you can very easily turn potential clients into clients.
You should use related tables for some of the other data, especially if you can have multiple "services requested" or "time tracking" etc.
2) you have two different tables. One that's for potential clients. Maybe with some related table(s)
And one that's for actual clients.
The moment a potential client becomes a clients you have a button that runs a script that:
- Imports the "Potential client" information into the client table.
- reassigns the related data to this new client so all his related date is properly linked to the correct client.
- removes the potential client record.
Voila, something like that.
I suggest you do some testing to see which situation is best suited for your needs.
THanks for all of your suggestions i think i know what i have to learn now in order to take project off and running.