1 2 Previous Next 16 Replies Latest reply on Jan 18, 2010 7:36 PM by PWGKing

    90 Day Forecast Reporting

    PWGKing

      Title

      90 Day Forecast Reporting

      Post

      I have completed my Asset Management Database and FileMaker Pro Advanced is Great. It was so easy to make my database with FileMaker but I now need to produce a 90 Day Forecast report on the 1st of every month which I have attempted to do with no success.

       

       

      The report that I need to develop must detail the assets and in particular must give my customers a 90 day forecast of assets to be inspected and also must detail all the overdue items for inspection. I have tried to develop a report with the following fields for reference, "Inspection Due Date" & "No. of Days Remaining" but with my limited knowledge I have not been successful with programing the report to reflect what I need.

       

      Can anyone help me or point me in the right direction to obtain the knowledge I need.

       

      Thanks for your time in advance.

       

      PWGKing

        • 1. Re: 90 Day Forecast Reporting
          philmodjunk
            

          Well it's about time for me to leave and I don't participate in the forum over the weekend... but maybe I can point you at a starting point.

           

          I'm interpreting a "90 day forecast" as finding a group of records where a date field is within 90 days of the first of a specified month.

           

          In that case, let's enter a date in a global date field, gMonth. If you set up an auto-enter calculation, like this: self - day(self) + 1, any date you pick will convert to the first day of that month.

           

          Now a script like this:

          Enter Find mode[]

          Set Field [YourTable::YourDateField; "< " & YourTable::gMonth + 90]

          Perform find[]

           

          will find all records dated within 90 days of the date selected.

          • 2. Re: 90 Day Forecast Reporting
            PWGKing
              

            Thanks for your suggestion but I must be doing something wrong. I am a newbie to this report programing and I followed your instructions but it did not work. I'll supply more info and maybe you can help.

             

            I have my asset database completed with:

             

            Currect Date (todays date)

            Inspection Required Date

            Days Until Inspection (positive whilst asset is complient, negative when past the Inspection required date)

             

            I need to produce a report that shows all assets that require inspection within the next 90 days of report generation (will be on the first of every month) and the report also needs to show overdue inspections that are required (inspections not completed prior to the Inspection Required Date).

             

            Please remember I'm new to programing so the KISS principle must apply. LOL

             

            PWGKing

            • 3. Re: 90 Day Forecast Reporting
              philmodjunk
                

              "..but it did not work"

               

              How exactly didn't it work?

               

              Days Until Inspection--I assume this is a calculation field. Please post the expression you defined to calculate this value.

              • 4. Re: 90 Day Forecast Reporting
                PWGKing
                  

                I just check a record this morning and the "Days Until Inspection Due" field did not change from 95 days remaining (yesterday) to 94 days remaining (today).

                 

                The calculation I am using is - Date Insp Due - Get(CurrentDate). This calculation only works when I change the field "Inspection Due Date". For example calculation I am trying to achieve is as follows:

                 

                Date Ispection Due -               23/04/2010

                Days Until Inspection Due -           94           (calculation used as above Date Insp Due - Get(CurrentDate) )

                 

                What I would like to achieve is the "Days Until Inspection Due" field to change automatically on ALL records without me having to adjust the "Date Inspection Due" field.

                 

                PWGKing

                 

                 

                • 5. Re: 90 Day Forecast Reporting
                  philmodjunk
                    

                  I thought that would be the case. That's why I had you take a look. I also wanted to be sure there weren't other details in that calculation that needed to be taken into account. Calculations that use get (currentDate) will not re-evaluate each day unless you set them to be unstored in the storage options for that calculation. Just to make matters worse, unstored fields will really slow you down when you search such fields in a find or use them in a sort.

                   

                  Let's avoid the days until inspection field altogether for this task. You may want to keep it for reporting purposes but we can avoid it in our find.

                   

                  A script like this:

                   

                  Enter Find mode[]

                  Set Field [YourTable:: Date Inspection Due; "< " & get(currentdate) + 90]

                  Perform find[]

                   

                  That should find all records dated from tody up to 90 days in the future. Try that script and let me know what you get.

                   

                  You did say something about the first of the month, but I think you meant that Date Inspection Due date is always the first of the month. If not, let me know and we'll refine this a bit.

                   

                  • 6. Re: 90 Day Forecast Reporting
                    comment_1
                      

                    PhilModJunk wrote:
                    Enter Find mode[]

                    Set Field [YourTable:: Date Inspection Due; "< " & get(currentdate) + 90]

                    Perform find[]

                     

                    That should find all records dated from tody up to 90 days in the future.


                     

                    No, that will find all records dated from the beginning of time until 90 days into the future.

                    • 7. Re: 90 Day Forecast Reporting
                      PWGKing
                        

                      Thanks Phil

                       

                      I have already tried the sugested script and it did not work. Probably because I did something wrong being a newbie to script writing.

                       

                      I will try again when I get time and advise what happened.

                       

                      As for the 1st of every month, that is when I wish to produce the report and send to all my managers. There is nothing significant about the 1st other than the time for reporting.

                       

                      Thanks very much for your time

                       

                      PWGKing

                      • 8. Re: 90 Day Forecast Reporting
                        philmodjunk
                          

                        "I have already tried the sugested script and it did not work."

                        What happened when you tried it? Did you get records you didn't want? No records? That might help me diagnose the issue.

                         

                        Comment, thanks for pointing that out. In this case the error was only in my explanation. "All records from the beginning of time (or at least the earliest allowable date in filemaker)" :smileywink: is exactly what the OP requested in this case.

                        • 9. Re: 90 Day Forecast Reporting
                          PWGKing
                            

                          G'day Phil

                           

                          When using the script no records were generated, just a blank fields on the report.

                           

                          PWGKing

                           

                           

                          • 10. Re: 90 Day Forecast Reporting
                            philmodjunk
                               Is Date Inspection Due a field of type "Date"? You can confirm this by finding it in Manage | Database | Fields and see what is listed in the type column.
                            • 11. Re: 90 Day Forecast Reporting
                              PWGKing
                                

                              The "Date Inspection Due" field is a DATE field. This is the date that I put for when inspections are due.

                               

                              PWGKing 

                              • 12. Re: 90 Day Forecast Reporting
                                philmodjunk
                                  

                                Just checking, that's one way this script might go awry.

                                 

                                Another check: examine your script and make sure you only see one set of [] in the set field step. Don't know how new to scripting you might be but sometimes new users don't always set up a set field script correctly on the first try.

                                 

                                Another question, with the script as written, if it's not finding records, it should either pop up a dialogue telling you it couldn't find any records or that you have incorrect/missing find criteria. Do you see any such message? If so, which?

                                • 13. Re: 90 Day Forecast Reporting
                                  PWGKing
                                    

                                  Sorry Phil

                                   

                                  I did forget to tell you that the script was being shown as a script in the "Plant No." field. The rest on the records were blank.

                                   

                                  Kindest regards

                                   

                                  PWGKing

                                  • 14. Re: 90 Day Forecast Reporting
                                    philmodjunk
                                      

                                    "I did forget to tell you that the script was being shown as a script in the "Plant No." field. The rest on the records were blank."

                                     

                                    Sorry, but I have no idea what you mean by that. "shown as a script in a field????"

                                     

                                    Let's back up a step here and walk through exactly what you are doing.

                                     

                                    You did define a script in Manage Scripts which you ran by either clicking a button or performing it from the scripts menu?

                                     

                                    You got results of zero records?

                                     

                                    The table where Date Inspection Due is defined is the same table referred to in the layout setup... of the layout where you are performing the script?

                                    1 2 Previous Next