7 Replies Latest reply on May 11, 2012 6:00 PM by user14040

    New Record with Find Mode

    user14040

      Title

      New Record with Find Mode

      Post

      I have been trying to show the total of current month when adding a new record

       

      I have two Tables, Customer and Glass when I do a find for customer, month and year I can add new records and see the total added for only the month and customer

       

      What I want to be able to do is click on a button to add a new record, choose a customer (From drop menu) and I will always see the total for the current month, right now I see the full total within the database unless I do a find.

       

      Is this possible?

       

        • 1. Re: New Record with Find Mode
          philmodjunk

          It's certainly possible. To get aggregate totals such as a monthly total, you can use one of two methods: manipulate the found set to get only the records that you want for your total, or use a relationship that matches only to the records you want for your total.

          The first option requires performing a find--which can be a script that silently does this behind the scenes for you.

          The second option requires setting up a relationship--very likely a self join relationship--that matches to the records you need for your monthly total.

          • 2. Re: New Record with Find Mode
            davidanders

            FMP version?  OS?   Total is dollar amount for that client?  Month means this month or last thirty days?

            Number of clients allows  a drop down list to be managable?

            Creating A Sub-Summary By Month
            http://help.filemaker.com/app/answers/detail/a_id/790/~/creating-a-sub-summary-by-month

            Need Monthly Summaries and Year to date Summaries in Same layout
            Need Monthly Summaries and Year to date Summaries in Same layout
            Live table with summarized data (Monthly summarized in Quarterly)


            • 3. Re: New Record with Find Mode
              user14040

              The totals are not money totals but I guess does not matter, the totals are Glass Pieces(Beaker, Flask, Cylinders) this is a form filled out that I add the number of pieces washed, I have everything the way I wanted and I was wondering if it was possible to instead of me doing a find for every customer I add glass to, instead have the steps done for me.

               

              New record

              I choose customer (John)

              I add pieces (25)

              total (25)

               

              New record

              I Choose customer (John)

              I add pieces (25)

              Total (50)

               

              New Record

              I choose customer (Jim)

              I Add pieces (15)

              Total (15)

              and the totals will always change with a new month and year

               

              thanks

              • 4. Re: New Record with Find Mode
                philmodjunk

                Define an unstored calculation field, cThisMonth, as: Let ( D = Get ( CurrentMonth ) ; D - Day ( D ) + 1 )

                This computes the date of the first day of the current month. It must be unstored or it won't update as the date changes.

                In your table, define a calculation field, cMonth as: Let ( D = YourDateField ; D - Day ( D ) + 1 )

                This computes the date for the first day of the month in which that date was recorded.

                In Manage | Database | relationships, make a new table occurrence of YourTable by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as ThisMonthsData.

                We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                Add it to your relationships like this:

                YourTable::cThisMonth = ThisMonthsData::cMonth

                Define a calculation fields as Sum ( THisMonthsData::Pieces )

                and put it on your layout.

                • 5. Re: New Record with Find Mode
                  user14040

                  Thanks for your help (I added the fields you list above)

                   

                  I created this field

                  cThisMonth, as: Let ( D = Get ( CurrentMonth ) ; D - Day ( D ) + 1 ) but received a error but when I changed CurrentMonth to CurrentDate it worked is that what you meant.

                   

                  Sorry I am not sure what to do next I added Sum (ThisMothData::Pieces) to my layout out, it gives the total amount in the database.  Is the next step creating a Find script?

                   

                  thanks

                  • 6. Re: New Record with Find Mode
                    philmodjunk

                    Don't know what my brain was doing. This should have been Get ( currentDate ).

                    I added Sum (ThisMothData::Pieces) to my layout out, it gives the total amount in the database.  Is the next step creating a Find script?

                    There's no find script needed. It sounds like you don't have this relationship set up:

                    YourTable::cThisMonth = ThisMonthsData::cMonth

                    The way this works is that cThisMonth returns the date for the first day of the current month. That's May 1st at the current time. The cMonth calculation then returns a date for the first day of the month in which that date falls. Thus, all the records dated for May, 2012 should show May 1, 2012 in the cMonth field. Thus only records for the current month are added up by the Sum function

                    • 7. Re: New Record with Find Mode
                      user14040

                      Thanks so much that worked