just do 1 thing make a calculation field that bring the sum of the sales field on the relationship(client-------sales)
it will show in client layout the total sales for a perticular client.
This is the formula i have now. But it doens't works
If ( TVERKOPEN::Z_PERIODE = "042010" ; Sum ( TVERKOPEN::Z_BEDRAG ) ; 0)
Actually i am not getting your problem
you have client and sales table
for 1 client more no of record in sales table.In sales tables sales prices are listed by a field with corresponding date.
So you want to see the sales record in client table for corresponding client and want to see records on aperticular month.
my question is how to get the month means you are in client table to find month wise record (i want for month april the total no of recors for client X)you have to 1st get the month.
No doubt date is in sales table .
So 1st explain what you want
Atauf, thx in advanced for your patience,
Let me try to explain,
You now I have two tables: Sales and Clients
In the sales table I have a calculation field ‘salesperiod’:
every sales between 01/06/2011 and 30/06/2011 will have the result 062011.
every sales between 01/05/2011 and 31/05/2011 will have the result 052011
Now I want to have a calculationfield in table Client that makes a summery of all the sales in the period 062011 on that client.
I am explainning the sol'n.
client---sales is the relationship
so make a calculation field(e.g Salesdate) in client that takes the date value from the sales.keep in mind this field is only keeping the date value.
Now for your requirment
1>make a occurance of sales e.g client-SALES_date
2>make another relationship among the cllient and sales( e.g client-SALES_date)
3>Make another calculationfield that brings the SUM of total amount of sales field from the client-SALES_date occurance.
WP wants totals for each month in succession for a given customer as part of the same report--not one month at a time.
FileMaker cannot dynamically add fields so you cannot use that method--though you can add related records--which may be the solution here.
Before we go that way, consider creating a summary report based on sales but limited via a find to just the sales records for a given client and date range, grouped to show just a sub total for each month. If that will meet your needs, it's much simpler to set up.
Such a report would look like:
Sales Report for John Smith, January, 2011 ... March, 2011
January, 2011 $2,000.23
February, 2011 $500.12
March, 2011 $8,000.34
Grand total: $10,500.69
That's a report you can do where the user specifies a date range and either selects a customer or clicks a button on your customer layout to run a script that finds these sale records and uses summary fields to compute a monthly total.
Let me know if this option works for you. If it doesn't please indicate why not so that I can take that feedback into account when I respond.