6 Replies Latest reply on Jul 15, 2011 8:27 AM by philmodjunk

    calculationfield / month

    WP

      Title

      calculationfield / month

      Post

      I’ve got two tables:

      1. Clients
      2. Sales

      They are related with a idnumber

      Now I want to have a calculationfield in table 1 (Clients) with all the sales on that client/month.

      I prefer to make for every month a different field so I can make calculations on it.

      How do I have to set up this field? 

        • 1. Re: calculationfield / month
          Abhaya

          Hi

          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.

          Thanks

          • 2. Re: calculationfield / month
            WP

            This is the formula i have now.  But it doens't works

            If ( TVERKOPEN::Z_PERIODE = "042010" ; Sum ( TVERKOPEN::Z_BEDRAG ) ; 0)

            • 3. Re: calculationfield / month
              Abhaya

              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

              • 4. Re: calculationfield / month
                WP

                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.

                • 5. Re: calculationfield / month
                  Abhaya

                  Ok

                  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)

                  i>id=id

                  ii>Salesdate=salesperiod

                  3>Make another calculationfield that brings the SUM of total amount of sales field from the client-SALES_date occurance.

                  Thanks

                  • 6. Re: calculationfield / month
                    philmodjunk

                    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.