9 Replies Latest reply on Mar 28, 2013 4:53 PM by LukeCulleny

    Help needed with time calculation in portal information

    LukeCulleny

      Title

      Help needed with time calculation in portal information

      Post

           Hello all! Please excuse me if there are any errors or I am not providing enough information as this is my first time posting. I am still a learning user with Filemaker with little scripting experience. I am building a database to help schedule setup and shoot times for a schedule I have essentially 6 fields that I am working with and several tables. This database has been converted from a filemaker template for invoiceing to perform the tasks I need it to perform. the fields are the following:

           StartTime (Time Field) - Has the time I want the calculations to start from

           SetupTime (NumberField) - Has a value in minutes that could range anywhere from 1 minute to 120 minutes

           SetupTimeCalc (CalculationField) - Meant to calculate times for me Please see screenshot for details

           ShootTime (NumberField) - Same parameters as SetupTime field but different value

           ShootTimeCalc (CalculationField) - Meant to calculate times like SetupTimeCalc field See Screenshot for Details

           EndTime (Calculation) - Display the end time after all portal records have been calculated.

            

           The Tables are the following:

           Shots - what the portal is displaying, the SetupTime, SetupTimeCalc, ShootTimeCalc and ShootTime fields currently belong to this table

           Schedule - Start time and EndTime currently belong to this table.

            

           I Need these calculations to be able to be refreshed If i change the order of the records in the portal by using my sort order column. I will always enter the StartTime, SetupTime and ShootTime fields and would like for the program to calculate as much as possible. i plan to have this program generate a report that displays the exact same information as well.

           I am a college student that understands computers very well but as I stated earlier I am still learning filemaker so I ask that you please be as specific as needed so that I fully understand what you are explaining to me. I really appreciate you taking the time to review my question and I look forward to any help that can be provided.

      Screen_Shot_2013-03-26_at_11.44.42_AM.jpg

        • 1. Re: Help needed with time calculation in portal information
          philmodjunk

               First a note about time entered into time fields:

               Time fields internally store there values as the number of seconds from midnight. This means that any calculations that compute a time should use figures that the calculation first converts into seconds. The results of time calculations can be number or time depending on the result type you select for the calculation. In your case, Time or TimeStamp should be selected in every case.

               Also note that if there is the slightest chance that your shooting schedule may go past midnight, you should not use time fields. Instead, you should use timestamp fields which internally store a value in seconds ust like time fields, but includes the date so that time intervals that include midnight will still calculate correctly.

               Working with time fields for now, you'll need a fairly sophisticated calculation field as the first record in your portal needs to refer to the start time and subsequent records then need to refer to the preceding record in the portal.

               SetUpTimeCalc:

               If ( Get ( RecordNumber ) = 1 ; Schedule::startTime ; GetNthRecord ( Shots::ShootTimeCalc ; Get ( RecordNumber ) - 1 ) ) + SetupTime * 60

               Result type needs to be Time

               ShootTimeCalc:

               SetupTimeCalc + ShootTime * 60

               Result Type should be Time

               and EndTime can be calculated as:

               StartTime + ( Sum ( Shots::SetupTime ) + Sum ( ShootTime ) ) * 60

          Edit note: have corrected for typo noted in subsequent posts

          • 2. Re: Help needed with time calculation in portal information
            LukeCulleny

                 Hi Phil!

                  

            EDIT::::: Okay so I corrected the part that says get (record) to get (recordnumber) and that validated the calculation. The time was properly calculated!! Very exciting! But it only shows the calculated time and not the start and end time. Thanks!! Still see questions below please.smiley

                 Thank you so much for taking the time to respond to my question! I have a few questions for you. There is definatly a possibility that these times will go beyond midnight and roll over into another day so I can switch them to timestamps. How does that effect my calculation? Also the Current Table for this layout is the Schedules table and the portal/related table is the Shots table. I am a bit confused with the formula you have provided and here are my questions if you dont mind explaining a little more so I can better understand their functions and we can get this working:

                 If ( Get ( RecordNumber ) = 1 ; Schedule::startTime ;**What do semicolons represent in coding?**GetNthRecord **What does this command do?**( Shots::ShootTimeCalc **Is Actually ShootTimeCalc as it is in the current table, So I changed that** ; Get ( Record**This Returns an error when trying to confirm it says "This parameter is an invalid get function parameter" What went wrong or does this need to be defined?** ) - 1 **What is the purpose of the -1?**) ) + SetupTime * 60

                 Those are my questions regarding that formula, now when I make these fields timestamp instead of just time how does that formula change? Also will it display both the start and end time? I need the results of these calculations to look like, 8:45AM-9:05PM? And also will that be the result for the SetupTimeCalc. And lastly I want to make sure that the way these formulas are written that my sort order will have an effect on them.Thank you so much again for your help and I look forward to your reply.

            • 3. Re: Help needed with time calculation in portal information
              LukeCulleny

                   Also this is the result that Is being calculated by the software as of right now with your calculations provided. It seems to function near perfect for the first record however the following records are not calculating from the row before. Here is a screenshot. Thank you Phil!

              • 4. Re: Help needed with time calculation in portal information
                LukeCulleny

                     UPDATE: So I moved the calculations to the shots table now and the calculation seems to be working better. However getnthrecord seems to still not function. for each calculation of setup time the records are all looking to the schedules::starttime field instead of the record above in the sorted portal. And the issue with how to display the start and end time still persists. As I stated before the calculation needs to show 8:45-9:20. Thanks!

                • 5. Re: Help needed with time calculation in portal information
                  philmodjunk

                  What do semicolons represent in coding?

                       Whenever a calculation refers to a field that is not in the current table, you have to tell FileMaker where to find the record from which the specified field's data will be used. In Schedule::startTime, "Schedule" is the name of a Table Occurrence in your database. To refer to a field outside the current table, you enter the name of the appropriate table occurrence, two colons (::) followed by the field name. You can be sure to get correct syntax for this if you find the field in the list of fields at the top of the Specify Calculation dialog (you may have to select a different table occurrence from the drop down) and double click it there to add it to your calculation. (Tutorial: What are Table Occurrences? are the "boxes" found in manage Database Relationships. The relationship linking your current to that related table controls which record supplies data to the calculation.)

                  GetNthRecord**What does this command do?

                       For any function used in a calculation posted here in the forum, if you don't know what it does, please look it up in FileMaker help. GetNthRecord is a method of accessing data from the same table as your current record, but from a different record than the current record.

                  Shots::ShootTimeCalc **Is Actually ShootTimeCalc

                       Your are correct, but both references use correct syntax and will evaluate the same. Since this field is from the current record, Shots::, is not needed.

                       Timestamp fields can simply be substituted for your time fields and these calculations should continue to work. You can simply go to Manage | Database | Fields and change the field types (and the calculation return types) to TimeStamp. You then have to enter dates and times into the TimeStamp fields instead of just the time. You may want to set up separate date and time fields and redefine the timeStamp fields to be calculation fields that combine the date and time to produce the timestamp value. But don't worry about that until the current version works to produce the desired shooting schedule.

                       I may have assumed something about this design that is not actually correct. I'm goint to run a quick test and report back here shortly...

                  • 6. Re: Help needed with time calculation in portal information
                    philmodjunk

                         Ok, my ideas do work. I found that care had to be taken in creating the SetupTimeCalc as it will refer to a field that doesn't yet exist when you initially set this up. I selected a different field for ShootTimeCalc in the calculation, clicked OK. Defined the ShootTimeCalc field and then returned to SetupTimeCalc and updated it to correctly refer to the ShootTimeCalc field.

                         You can compare what you currently have in place with this demo copy: https://dl.dropbox.com/u/78737945/ShootingSchedule.fmp12

                    • 7. Re: Help needed with time calculation in portal information
                      LukeCulleny

                           Very interesting. I want to further clarify as it seems there may be a mis understanding about my tables. The layouts current table is the schedules table and the related table shown only through the portal is the shots table. originally I had the calculations in the schedules table but since your help have moved them to be part of the related shots table as I thought that may have had an effect on returning the proper record number. I look forward to your test results. Thank you!

                      • 8. Re: Help needed with time calculation in portal information
                        philmodjunk

                             My post on test results were posted minutes before yours. I'm making this post so that it pops back up into recent items to make sure that you see it.

                        • 9. Re: Help needed with time calculation in portal information
                          LukeCulleny

                               Phil!!! I dont know how or why you did it but you did! The calculation worked flawlessly! It turns out I was actually portalled into a table that was created by the template that i did not need! After realizing this and reviewing your functioning calculation I was able to get mine to work. It is not working flawlessly and I am ironing out my layouts and other tables. I will post a photo of the finished product later tonight. Thank you so much for all of your help! You are awesome!!