Estimate Records to Contract/Invoice Records

[Sean Kennedy]

I am in the planning stages of my application and have come across a problem I am not sure how to approach. In this business model it is typical to generate an estimate for a client and that estimate will either be picked up and converted to a Contract and then an Invoice, or, it will languish for months in the estimates file and never become a contract.

I am not sure how to convert an Estimate Record into a Contract without duplicating all the data. I could set up two sets of tables, one set for Estimates and one set for Contracts (and sets for Work Orders and Invoices also) but there's something inside me that says I should be able to save on duplicating all the data since the detail items do not generally change as the Estimates progress to Contracts, Work Orders, and finally Invoice. The data is essentially the same in all these forms, only the header information changes.

Should I set up one detail table and have a field for each type of header file I will need, that is a Est_No field, Cont_No field, Work_Ord_No field and a Inv_No field? If I need to edit the Estimate details and re-submit I'll just generate a new Est_No and delete the outdated record?

Does this make any sense? I'm thinking my sets will look something like this:

Estimate Set -
Parent: Est Header
Child: Est Detail
Link: Est_No

Contract Set -
Parent: Cont Header
Child: Est Detail
Link: Cont_No

Work Order Set -
Parent: WO header
Child: Est Detail
Link: WO_No

Invoice Set -
Parent: Inv Header
Child: Est Detail
Link: Inv_No

I'm having a hard time getting my head around this. It's probably not as complicated as I want to make it. I just don't want to set up the database wrong to start with. I'd like to get the table structures right at least and then I can adjust and tweak the application as I go.

Thanks for the help. I've read some great stuff on this board. I'm an amatuer developer but my day job is running our retail fireplace store.

Sean Kennedy

[forskare]

RE: Estimate Records to Contract/Invoice Records

Sean,

While it's good to plan things out a bit, you'll make lots of changes between now and then. I'm afraid if you try to plan the whole thing before you start, you'll wear yourself out just thinking about it.

Often, it's easier to do what you want by creating a reverse set for reports. As long as you have the fields to enter the data, it's the reports that'll display the contract, etc.

kenn

[Peter.Greulich]

RE: Estimate Records to Contract/Invoice Records

It sounds like you answereed your own question. Maybe all you need is one set:
=================
Estimate Set -
Parent: Est Header
Child: Est Detail
Link: Est_No
=================
with 4 more fields in the Est Header table:
Cont_No
WO_No
Inv_No
Type

Where Type = "Estimate" or "Contract"

So when your estimate becomes the real thing, you just change the Type field. You can set it up any way you like, but simpler is better, esp. if you are just starting building your database.

[Sean Kennedy]

RE: Estimate Records to Contract/Invoice Records

Here's a quick overview of how we process these Estimates and Contracts now (not using A5):

Contact Data is collected by hand with a paper form.
Estimate is typed up using MS Excel workbook.
.xls file is saved to hard disk in a specific folder
Estimate is printed and mailed.
When Estimate is accepted it is converted by hand to a Contract.
(All Estimates are proposals and convert to contract upon signatures of acceptance)
The Work Order is printed from the Excel file.
Once the Contract is completed the Invoice is entered to the Dac-Easy Accounting software.
The .xls file on hard disk is archived and the paper Contract is filed.

I am trying to stop this non-sense of having the customer data spread out all over the place and wnt to get it all into one database management system. For now, I am trying to set up an A5 database that will allow me to track active contacts (leads, estimates, work orders, etc.) from the point of initial contact to job completion. Then I will turn the data over to Dac-Easy for accounting. (Dac-Easy does not handle estimates nor does it have a good PIM-type application)

I tried working in Outlook but it is too cumbersome and does not allow customization very easily. I have some tables started in Access that I am using for collecting contact data and tracking the progress. But it is just a flat file type setup since I am not very good at programming Access and I don't know how to integrate it with Outlook and Excel. I use Access for tracking the names and Outlook for scheduling and To-do list, and Excel for Estimates and Price Lists. But the data is all fractured and I can't look-up an Estimate for a contact from inside of Access or Outlook.

My goal is to enter the contact data into A5 and collect customer information for mailing lists, generate estimates, contracts and work orders, and track the progress using logical fields and date fields, etc. It would be nice to add a calender for scheduling jobs and to-do list also. I'de like to get all this data I use into one program where I can manipulate it and set up reports, instead of how we have it now, which is almost as useless as plain-paper records for reporting and tabulation.

Maybe there is already an application set up by someone else that I can modify. I've looked at quite a few packages over the years and keep coming away disappointed that they are not customizable. We use a specific language and process that is not common in other retail shops or contracting businesses. I thought I could set up the database myself with A5 and get just what I want. All I need is time, right? I also have to learn A5, having used A4 in the past with DOS. But also having dabbled with Access I find A5 is easier to get the hang of. I have set up some nice tables for customer data and product data. Now all I need to do is set up a system for tracking the estimates and contracts. It's not unlike an invoicing system, with a few changes in terminology.

I'm grateful for any pointers. I'm working through the manuals now and am about ready to start creating the database. Thanks.

Sean Kennedy

[Sean Kennedy]

RE: Estimate Records to Contract/Invoice Records

Yep, that's what's happening. I'm over-thinking and getting all stressed out.

Okay, so you're saying just get the detail records into the Est_Detail table using one set for the data entry and then use another set for the report? Not sure I am following how this would work.

Thanks for the input. And fast! I just stepped away to get a bite to eat.

Sean Kennedy

[Sean Kennedy]

RE: Estimate Records to Contract/Invoice Records

Hey, you may be on to something there. Actually, I can't see needing to change the estimate number at all. Maybe all I need is the "Type" field to turn the estimate into a "Work order" or a "Contract". I do have different needs in each type of document though. The Work Order has different fields in the header than the Contract or the Estimate. Maybe I will stay with the different header tables just so I can print the different types of reports.

Thanks for the quick reply!

Sean Kennedy

[forskare]

RE: Estimate Records to Contract/Invoice Records

Sean,

I think you've got the right idea. Rather than try to get it all at once, put the app together in stages; just like building a house or a road.

Stage 1: Contact Data is collected by hand with a paper form.

Stage 2: Estimate is typed up using MS Excel workbook.
.xls file is saved to hard disk in a specific folder
Estimate is printed and mailed. When Estimate is accepted it is converted by hand to a Contract.

Stage 3: (All Estimates are proposals and convert to contract upon signatures of acceptance) The Work Order is printed from the Excel file.

You can do this by using A5' letter writing capibilities. Write the contract and insert the fields in appropriate places, etc.

Once the Contract is completed the Invoice is entered to the Dac-Easy Accounting software.

You might want to think about using QLinker, an Add-on product for Alpha Five. It allows you to quickly and easily move data back and forth between QuickBooks and Alpha. Then you eliminate the hand conversion to Dac-Easy

kenn

[Peter.Greulich]

RE: Estimate Records to Contract/Invoice Records


I am trying to stop this non-sense of having the customer data spread out all over the place and wnt to get it all into one database management system.

The perfect reason to use a database tool such as Alpha

My goal is to enter the contact data into A5 and collect customer information for mailing lists, generate estimates, contracts and work orders, and track the progress using logical fields and date fields, etc.

We do the same thing in our firm.
[i]
It would be nice to add a calendar for scheduling jobs and to-do list also.
[i]
Checkout the Calendar Addin on Alpha�s main web site.

I'de like to get all this data I use into one program where I can manipulate it and set up reports, instead of how we have it now, which is almost as useless as plain-paper records for reporting and tabulation.

Alpha is your baby.

Maybe there is already an application set up by someone else that I can modify.

Post a separate request, someone may have something to sell you.

I thought I could set up the database myself with A5 and get just what I want. All I need is time, right?

Right.

-PG

[Peter.Greulich]

RE: Estimate Records to Contract/Invoice Records


I am trying to stop this non-sense of having the customer data spread out all over the place and wnt to get it all into one database management system.

The perfect reason to use a database tool such as Alpha

My goal is to enter the contact data into A5 and collect customer information for mailing lists, generate estimates, contracts and work orders, and track the progress using logical fields and date fields, etc.

We do the same thing in our firm.

It would be nice to add a calendar for scheduling jobs and to-do list also.

Checkout the Calendar Addin on Alpha�s main web site.

I'de like to get all this data I use into one program where I can manipulate it and set up reports, instead of how we have it now, which is almost as useless as plain-paper records for reporting and tabulation.

Alpha is your baby.

Maybe there is already an application set up by someone else that I can modify.

Post a separate request, someone may have something to sell you.

I thought I could set up the database myself with A5 and get just what I want. All I need is time, right?

Right.

-PG