Calculation Between 2 tables with multiple fields to calculate

[daveealex]

I have a table where we do our estimates. This table is used by 10 people. The estimates are comprised of estimates made by telephone, by email, and by a physical person entering our office. I have already created calculations and summaries in this table, even with the percentages for each type of estimate based on the total estimates.

I have another table with the the actual contracts that have been made and the summary count of contracts made by each person. This table is used by the same 10 people.

Now i need to make another table to give me the summaries of all the data from above by month. The percent of estimates that have become contracts and etc. My problem is with the relationship and the fields in the new table. I am wondering how to do this correctly?

Dave

[RalphL]

Sorry, but I find your question to vague to answer in detail. You might be able to do what you want SQL and no new relationships. A sample file and more details about what you want might hel[.

[daveealex]

I found a way to do what i was looking for in the other Filemaker forums(See below). Now this works as expected for the monthly, for the weekly I used WeekOfYear(Date).

This requires a relationship linking the two tables and different relationships will produce different results. You'll need to figure out what kind of relationship will access the correct data for you. One way is to set his up with your third table linking to records in the other two by date:

Say each record in your third table represents one Month's business. Let's call this third table MonthlyTotals. Define a date field in it called cMonth

Define a calculation field in the expenses and also in the invoices table, cMonth as: TransactionDate - Day ( TransactionDate ) + 1 with "date" specified as the result type. This calculation returns the date of the first day of the month for the given date. Use the name of your date field in place of TransactionDate.

Now you can set up two relationships to MonthlyTotals:

MonthlyTotals::Month = Invoices::cMonth

MonthlyTotals::Month = Expenses::cMonth

Now you can enter a date in Month for the first day of the month and your relationships will link to all records in Invoices and also in Expenses for that month. There are ways to automatically create one such record for each Now a calculation field with this expression will produce your monthly profit: Sum (Invoices::InvoiceTotal) - Sum ( Expenses::ExpenseAmt )

A summary field defined in MonthlyTotals can then compute the total of this calculation field to compute a total for the year, the quarter or anyother range of months that you need. You just create one MonthlyTotal record for each month and perform a find to pull up just the monthly totals that you want for your report.

PS. this is just one of many possible ways to do this. If you linked your third table by a date field directly to transactionDate fields, you could do this with daily totals rather than monthly.

David

[comment]

Good. Now we have a vague question AND an unreadable answer... :laugh2:

[daveealex]

I fixed the post