5 Replies Latest reply on Oct 17, 2014 5:03 PM by erolst

    Looping Script Help

    Scottiet

      Hello all.

       

      I am still learning a lot about Filemaker and rely heavily on the superb discussion that occur on these forums but I have a slight problem trying to find the right direction for my hopefully simply dilemma.

       

      We are a Not for Profit organisation that has to capture the use of video systems use:

       

      Basically we have a Value list of sites that we need to enter the duration of the session for #1, #2 & #3

       

      This form or Record is completed once a month with the total amount of minutes/hours.

       

      My first approach was to create one Table with each site/1/2/3/total etc. as individual fields... Crazy I now know as there were loads of them... I'm led to believe that this can be achieved by implementing a Looping script to perform the task with limited need for field creation?

      Unfortunately I’m not quite there yet with scripting and especially looping...

       

      Reduced site image below.

       

      Any direction would be Greatly Appreciated.


      Thanks in Advance


      Scott

       

      P.S. This is the only table and has no relationships apart from maybe maintaining the site list seperatly in a 'Value list' DB

       

       

      Capture.JPG

        • 1. Re: Looping Script Help
          Mike Duncan

          Are you wanting to create fields with a looping script? Even if possibly, it's not practical.

           

          Looking at your screen shot, it looks like you just need a handful of fields (site name, one, two, three, site_total) and just show the recors in list view. You can use summary fields for the totals. Or am I missing something?

           

          -Mike

          • 2. Re: Looping Script Help
            Scottiet

            Thanks for such a quick response.

             

            Forgive me, my terminology probably isn't correctly expressed yet.

             

            Yes, there basically will be 5 Fields, and I need to capture the #1 minutes, 2, 3 and total for each site.  There is approximately 40 sites that I have maintained in a value list table.

             

            Hope that makes sense.

             

            Scott

            • 3. Re: Looping Script Help
              erolst

              Have a look at the attached sample that uses 2 tables; this will allow you to keep your historical data.

               

              The only loop required would be one to create a monthly set of related records (or just create the records via e.g. a portal on demand …)

              1 of 1 people found this helpful
              • 4. Re: Looping Script Help
                Scottiet

                Thank you for the example, i like the way the navigation happens and this gives me a good structure to go by.

                 

                I was wondering if the list of 'Companies' could all be captured with the duration times all in 1 record per month as opposed to individual records each month.

                 

                For statistical reasons, the report each month will have to show the total per 'Site'(Company) and the Total for each number. (as you have set up) however we will also need to do Ad-Hoc reports on a specific site(Company) between date ranges to indicate total times for that period.

                 

                If you can further assist i would really appreciate it.  I don't mean to ask or expect the direct answer but I am learning as much as i can as i progress with these DBs.


                Scott

                • 5. Re: Looping Script Help
                  erolst

                  Scott Taylor wrote:

                  I was wondering if the list of 'Companies' could all be captured with the duration times all in 1 record per month as opposed to individual records each month.

                  If you had one record per month with all values for all companies, you a) had to constantly add fields for new companies (and what about defunct ones?), and b) would have quite a hard time setting up a layout.

                   

                  I would really recommend the structure as set up in the sample file (if I say so myself). You can always summarize (semi)-properly normalized data, but it's difficult and contra-productive to try to rip data apart that has already been summarized (or is stored in an inadequate structure).

                   

                  Semi-properly because a really normalized structure would hold one record per company per value type; that structure generates more records, but needs less fields, and is again that little bit more flexible …As it stands, and with three fields, it's still usable as one record woth several combined fields; but should you find yourself having to store and summarize a fourth value type (and will that be the end of it?), you know it's time for proper normalization.

                   

                  Note that summary fields simply summarize a given found set; if you do that ad-hoc search for all entries of a given company within a date range, they will summarize just these found records. If you implement e.g. a dashboard- or picker-type layout that lets you choose you a company and a date range, you can feed these choices into the script.

                   

                  You can also make the summary layout much more versatile by inserting the header as a $$variable that you can calculate (company name, date range) and optionally hiding the body part.