Re: Creating new record in one file using data from another

In article <1133157611.929036.160640@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>,
"justin" <justin@xxxxxxxxxxxx> wrote:

> I'm making a database for my wife's gardening business and need to find
> a way to turn a proposal record into a new job record (once a client
> approves the estimate). Unfortunately the data is spread across three
> files and I can't see how to transfer it.
>
> This is the current set up:
>
> I have a file called Master which contains all my client contact info.
>
> I have a file called Proposals which is linked to Master and joins
> proposals with clients.
>
> I have a file called Proposal Line Item Labour which is linked to
> Proposals and joins labour costs to proposals. There may be several
> labour records related to one proposal.
>
> I have another file called Proposal Line Item Materials which is also
> linked to Proposals and joins materials costs to proposals. Again there
> may be several records related to one proposal.
>
> I also have three files: Jobs, Jobs Line Item labour and Jobs Line Item
> Materials which mirror the Proposal files but contain records of actual
> jobs.
>
> I can see how I can create a new record in the Jobs file and set its
> fields using data from the Proposals file - but I am aboslutely lost in
> how to create the related records from the Line Item files.
>
> Any help would be greatly appreciated.

The usual approach would be to have one file (or in your case one set
of three files) that stores both Proposals and Jobs, with a simple
field to indicate whether each record is a Job or a Proposal. Then when
the Proposal becomes a real Job you simply change that one field.
ie.
Set Field [ProposalandJobs::RecordType, "Job"]

Whenever doing reports you simply have to remember to add RecordType =
"Job" or RecordType = "Proposal" to the Find requests (unless you want
records of both types).


If you've got no data or not much data already entered it might be
easiest to alter the system to do it this way.

Otherwise, it's very painful (and likely unneccesarily laborious) task.
It'll be a matter of copying / importing the data from the Proposal
files to the Jobs files and then presuambly deleting the Proposal
records. This would require scripting the copying / importing of the
appropriate records, which could get complicated and messy with three
files in each section.

Importing is probably the easiest method. Basically the script would be
something like:

Find Proposal record
Go to Jobs file and import record from Proposal file
Copy new Jobs ID to global field

Find related Proposal Line Item Materials file
Go to Jobs Line Item Materials file and import
from Proposal Line Item Materials file
Update ID from global stored Jobs ID

Find Proposal Line Item Labour file
Go to Jobs Line Item Labour file and import
from Proposal Line Item Labour file
Update ID from global stored Jobs ID

Delete Proposal record (which should delete all the
related records if the Relationship is set-up
that way).

FileMaker will only import the records from the found set (as long as
the file is not closed in newer versions of FileMaker!), but you'll
also have to probably re-link each line item record with it's new ID
data as well. This could be done by importing the Proposal Line Item ID
data into a second ID field and having a second set of relationships
linking the records sot hat you can script each end copying the new Job
Line Item ID data to each other.






Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
.

Quantcast