6 Replies Latest reply on Sep 16, 2015 1:43 PM by samcourtenay

    Calendar Repeating Field (calculation question)

    samcourtenay

      Hello,

       

      I have a job schedule calendar table, each record is one day of the year.

       

      I use repeating fields which look up data from another table in my database.  Basically I can enter a job number and it will add all the relevant information on the repeating field line.  It works well.

       

      I am looking to use a calculation repeating field (z_target) which will pull data from another table (z_hours_to_aim_for).

       

      The reason I want to use a calculation field is because the field stays empty unless I re-enter the job number, and I have about a years worth of records.  I could make a script to do it but easy if I could just get this calculation to work.

       

      The reason I am using repeating fields is because the database was written a long time ago and I don't have the time to change it.

       

      Currently I have tried this calculation:

      Let(

      R = Get ( CalculationRepetitionNumber ) ;

      CustomerDataBase::z_hours_to_aim_for

      )

       

      It adds up the data for all repeating fields and puts it in the first repeating field.  Is there a way to change this calculation so that the hours show up in each individual line instead of adding all up in the first repeating field?

        • 1. Re: Calendar Repeating Field (calculation question)
          thurmes

          There really isn't enough info here to answer your question. How does the calendar table relate to whatever table holds z_target? You say z_target pulls data from another table, when it must in fact pull data from a field or several fields in that other table. Is that field you're pulling data from a repeating field?

          In your Let statement you get the calculation repetition number, but don't use it. (R is ignored in the calculation you show.) Maybe what you really want is

          CustomerDatabase::z_hours_to_aim_for[Get ( CalculationRepetiionNumber ) ]?

           

          Repeating fields are sometimes quite useful, so you don't have to apologize for using them.

           

          Give us a bit more info, and we'll try to help you.

          • 2. Re: Calendar Repeating Field (calculation question)
            samcourtenay

            tldr:

             

            Get data from Table::CustomerDatabase:TotalACThrs (not a repeating field)

            Insert data to Table::JobSchedule:z_actual_hours (repeating field 30)

             

            The two tables have a relationship established through the job number.  Other repeating fields lookup data from corresponding field in CustomerDatabase once job number is entered into the schedule.

             

            So what I am attempting to do is get data from the Customer Database table and bring it across to the Job Schedule table and display in repeating fields via a calculation.

            I need it to be a calculation because actual hours are not known until after the job has been put in the schedule, so if it's not a calculation the data will not appear unless the job number is re-entered.

             

             

            More Info

            The calendar table is named Job Schedule.  It has many repeating fields (address, phone, est job hours, etc)  I have a relationship between this table and my customer database.  When I enter a job number it brings up all the details on that job and fills the repeating fields (which have lookup to the corresponding field on customer database)

            I have 30 lines so can have 30 jobs booked in each day.

             

            I have just created 3 new repeating fields, one is for actual hours the job took, the other is for target hours, and the last one is the difference.

             

            Actual hours are pulled from the CustomerDatabase once the job has been completed.  Target hours is pulled from the CustomerDatabase as well but the data is there at the time its entered into the schedule.  Difference is a calculation between the two.

             

            So the same calculation would work for both the repeating fields.

             

            I am sure this can be done, I just do not know about coding calculations (as you can tell from my original post)

            • 3. Re: Calendar Repeating Field (calculation question)
              thurmes

              The new info helps a lot. So you have 30 repetitions because you might have 30 jobs going in a single day. Does each repetition consistently signify a specific job, or does that change? For instance, does repetition 1 on January 1 signify the exact same job as repetition 1 on November 15th?

               

              The way that FM repeating fields work, if you're trying to do math on them, is as follows:

              Fld1, Fld 2, Fld3 are all repeating, 30 reps

              set Fld3 as a calculation = Fld1 + Fld2

              Then the first line of Fld3 = the first line of Fld1 + first line of Fld2 = Fld1[1] + Fld2[1]

              So, if the first line of Fld1 always means the same thing (refers to the same job), then you can easily set up the calculations.

               

              I suspect that the answer to my question above is that repetition 1 signifies one job in January, another job in November, and that there's a second repeating field which says which jobs the first field refers to. if that's the case, I can't think of a simple way to set up the calculation. For instance, you can't use ExecuteSQL because that command only recognizes the first repetition of a repeating field. Maybe someone cleverer than I could suggest a calculation that could do this?

               

              Setting up a script to go through the records one-by-one would be reasonably easy. You could start with the start day of the project, end with the end date, and step through the records looking for records that have a given job number in the second field, and grabbing the number of hours from the first field. (Do you have FMP Advanced? If so, go to Brian Dunning's site and get the custom function GetValueNumber; if you don't, put the calculation in a script that you pass a ScriptParameter to and send a ScriptResult from. Then Set Variable {$JobHrTotal = $JobHrTotal + Field1 [ GetValueNumber ( List (Field2) ; $JobNumber ) ] } will allow you to add up all hours assigned to $JobNumber.)


              Hope this helps.

              • 4. Re: Calendar Repeating Field (calculation question)
                samcourtenay

                Thanks for you replies thurmes.

                 

                You are correct the repeating fields have different data in them for each day.

                Each day is a new record in the table.

                 

                Might be time for me to build a new schedule I think...

                • 5. Re: Calendar Repeating Field (calculation question)
                  thurmes

                  I was thinking about this this morning, and I realized that it may be easy to change your database around to something easier to work with. Set up a new table that will hold these fields:

                  the date

                  the date records serial number (not necessary, if the date itself can act as a unique foreign key)

                  a serial number for this record

                  the project number

                  the number of hours worked

                   

                  It's pretty easy to write a script that will take the contents of the repeating field and dump each line into a record in the new table. Now, instead of having a repeating field, you can have a portal to this new table. Doing all the calculations to determine how many hours were worked for a project will now be quite simple, either using a Sum or an ESQL calculation. The hard part is dealing with any scripts that currently work with the repeating field, and deciding how to make the user's expectations - they're used to the repeating field - and experiences seamless.

                   

                  By the way, I tried to come up with an example file that I thought would do what you wanted, but could never quite get the Sum calculation to work in a repeating field the way FileMaker repeating field calculations are supposed to work. See the attached file...

                  • 6. Re: Calendar Repeating Field (calculation question)
                    samcourtenay

                    Great thanks for that, that's a good idea i'll get onto that next week