Adding Fields Together

[w4walter]

I am adding a report to my Quotes database. My database structure is like this.I have 12 Worksheet tables that are used to enter items into to get prices. They are related to my estimates table where I have a field that adds all the worksheet total fields together. The totals from each worksheet are then displayed on the Material Supply Contract form. There is also a field that adds up all 12 worksheet totals to get a grand total. The problem is sometimes a customer doesn't want to buy the entire quote at once. They want to buy a couple now. Instead of having to delete all the rest of the data out of the tables, I have created a field that shows the worksheets status. If it is sold, I want it to add those numbers together. If its not sold, it won't be added into the total.

 

I am not sure how to get this to work. Any assistance would be greatly appreciated.

[hfinger]

My brute force method would be to create a Number Field for each Worksheet which is a "1" if they want to buy that part of the quote and a "0" if they do not.  

 

Modify the grand total to look at the numbers on Worksheet #1 by this new number field plus the numbers for Worksheet #2 by its new number field, etc.

 

Am I missing something?

[Rick Whitelaw]

Why do you have 12 Worksheet tables? I suspect a data structure issue here. Does each table contain the same type(s) of data/fields?

[w4walter]

Why do you have 12 Worksheet tables? I suspect a data structure issue here. Does each table contain the same type(s) of data/fields?

The reason for the 12 worksheet tables is that it gives the sales staff a way to breakout pricing. As this is for a cabinet shop the sales staff could price the kitchen on worksheet, master bath on another. They do not have to use all worksheets but they have up to 12 of them to break out and display a cost for each room.

Since I have a field for status and I have a checkbox they can mark if its sold, I was wondering if there was a way I could add up just the worksheets that have been marked as sold.

[comment]

The reason for the 12 worksheet tables is that it gives the sales staff a way to breakout pricing.

 

I don't see how having multiple tables helps here. What exactly is the difference between them (if any), in terms of their fields - i.e. the things they are meant to describe? I too suspect this is your real problem, and the rest merely a symptom.

[w4walter]

Each table has the same fields. As I mentioned above, it gives the sales staff a way to break out the price to show the customer. This database is one that was originally designed in Access and I have converted it to FileMaker. Everything is working the way it did in Access. I am just trying to make a form that the sales staff can print off that shows which options were sold and gives a price just for those options.

 

I have 12 fields that get added together. The customer might not buy all of them. I am looking for a way to add only the ones that have been marked as sold.

 

As for having the 12 worksheet tables, how would you have done it?

[comment]

As for having the 12 worksheet tables, how would you have done it?

 

I wouldn't. I would use a related record in a single Line items table for each item included in an estimate.

 

Then, if and when the estimate becomes an order, you can select whether to include any one or all of the items - even add new ones, with no constrain on the number of items included in any estimate or order.

 

 

See also:

http://fmforums.com/forum/showpost.php?post/309136/