6 Replies Latest reply on Jun 12, 2009 7:33 AM by TSGal

    Stats form main db

    p1nf0ld

      Title

      Stats form main db

      Post

      Hello,

       

      I am new to FMP and a complete novice, although I am finding it a great database program but I am wrestling with a problem of getting stats from a main database into another table. I will try to clearly explain what I am trying to do.

       

      table1 contains information about calls taken.

       

      table2 is where I want to create a monthly and 6 monthly report from information in main db but not sure if I am making this too complicated? I have linked the db's with a relation

       

      I am trying to get the total number of calls for the previous month and 6 months and then two figures for one field that has 2 options (callline1 and calline2) I can get the figure into a field by doing a count but can't see how to make count only see say callline2. I would like to have a drop down list for the month names and a text input field for the year. Then do a search for matching records. I started writing a script but can't get it to work. Having looked for info on the web I think I have confused myself more.

       

      So I think my requirement is:-

        

      select month

      enter year

      days to be *

      find all records for that month in table1::callline

        fill table2::stat_calline_total field 

        find total number records in table1::callline call type = callline1

        fill table2::stat_calline1 field

        find total number records in table1::callline call type = callline2

        fill table2::stat_calline2 field

      end find

       

      Just can't seem to sort it out. Would appreciate any advice..

       

      Regards

      Tom 

        • 1. Re: Stats form main db
          p1nf0ld
            

          I have made a little step forward, I now am able to script search recs with "searchpattern" and output to field in same table

           

          perform find

          set field [maindb::main_count_of_line1: Get ( FoundCount )

           

          I then tried to duplicate this for the second search but it is putting text into the second field so I assume the first search is not being flushed. Am I getting warmer? :(

           

          t. 

          • 2. Re: Stats form main db
            TSGal

            p1nf0ld:

             

            Thank you for your post.

             

            Make sure you are performing the finds in the main table.  You can then store that information to variables and update the information in the second table.  For example:

             

            Go to Layout [ <layout with first table fields> ]

            Perform Find [ Restore ]

            Set Variable [ $value1 ; Get ( FoundCount ) ]

            Perform Find [ Restore ]

            Set Variable [ $value2 ; Get ( FoundCount) ]

            Go to Layout [ <layout with second table fields> ]

            Set Field [ <second table>::field1 ; $value1 ]

            Set Field [ <second table>::field2 ; $value2 ]

             

            This should get you pointed in the right direction.

             

            If you need clarification for any of the above steps, please let me know.

             

            TSGal

            FileMaker, Inc. 

            • 3. Re: Stats form main db
              p1nf0ld
                

              Hi TSGal

               

              Thank you for your reply, I was starting to tear my hair out..

               

              I have got as far as Set Field [ mytable::secondtabfield ; $value1 ] but I'm not sure if these are right? I get

                  Set Variable [$value1; Value:Get ( FoundCount )] and Set Field [statsdb::stats_line1_count = $value1]

              The script runs to the end but doesn't put any values in and comes up with a 102 error in the debugging window.  

               

              This all makes sense now you have set it out for me. 

               

              Regards

              Tom 

              • 4. Re: Stats form main db
                TSGal

                p1nf0ld:

                 

                The first thing I see is that you are using "Set Field" on two different tables.  My script was not set up for that, so make sure you switch to the appropriate layout when you use Set Field. 

                 

                Let's see if you can do this manually using the script as the model.

                 

                Go to the first table layout (mytable) and find for that subset of records.  You should get a number of records found.  If none are found, then that may cause a problem.  Write down that number.

                 

                Perform a second find for the second group of records, and again, you should get a number of records found.  Write down that number, too.

                 

                Now, switch to the layout that has your fields from the second table (statsdb) and enter the numbers into the appropriate fields.  If one of those numbers go into one table, and the other into another table, make sure you switch to the appropriate layouts before entering the information.

                 

                Also, if you want to add a record before entering these values, then be sure to include a "New Record/Request" into the script before the Set Field step(s).

                 

                I hope this gets you closer to what you want.

                 

                TSGal

                FileMaker, Inc.

                • 5. Re: Stats form main db
                  p1nf0ld
                    

                  TSGal

                   

                  I now have the script working and have 2 numbers entered into to fields on the same table, but :) it seems to be blanking the second Perform Find. I have set up the 2 specify find requests with different parameters i.e. "live" and "closed" and it finds the first set but then reverts back to the full found set when it comes to carry out the second Perform find. Apart from that it works a treat.

                   

                  Is it best to keep the found totals in the same field or split them off into a separate one? My thought was that the stats would only be carried out once a month so to store the data on the same table would be wasteful as you would have blank fields. Is it a lot harder to script saving data to external table?

                   

                  Thanks for your help, much appreciated.

                  Tom

                   

                  Edited for typos 

                  • 6. Re: Stats form main db
                    TSGal

                    p1nf0ld:

                     

                    It sounds like your second Perform Find is restoring incorrect criteria.  In your script, put a "Halt Script" command after your second "Perform Find".  This halts the script, and you can view the results of the Find.  Then, pull down the Records menu and select "Modify Last Find".  This will show you the criteria that is being used to find the data.  If this is not what you expected, then go back into the script and reset the criteria for the second Perform Find.

                     

                    It is better to put the two values into separate fields.  Otherwise, you may overwrite one value with the other.  If the stats are carried out once a month, you may want to create a table with three fields:

                    Month

                    Live

                    Closed

                     

                    That way, you can enter a new record for each month and present the totals.

                     

                    TSGal

                    FileMaker, Inc.