5 Replies Latest reply on Dec 8, 2011 10:58 AM by philmodjunk

    Running Reports in IWP

    lime_tree

      Title

      Running Reports in IWP

      Post

      Hi,

      I have recently finished a sales database and published it online. The problem that I am having is reporting - I have about 15 different reports that I can run smoothly from the File Maker software, but only a couple of them work on the online versions of the database.

      I have checked compatibility of the scripts with web and there are no greyed-ut steps. I also compared the script language between the scripts that work and those that do not and could not find anything different.

      Has anyone experienced the same problem and is it possible to solve?

      An example of a script that works on both, FM and IWP is a Search script:

      - If source of contact = source #1, then perform find etc.

      An example of script that does not work in IWP is Overdue:

      - If date due < today's date, then perform find etc.

      I am aware that sub-summary reprots cannot be shown in IWP, but these reports are quite simple, resulting in a single, unsummarized list. I am wondering what I am missing.

      Thanks for your help.

        • 1. Re: Running Reports in IWP
          philmodjunk

          We'll need to see those scripts in greater detail. Both your examples look like steps that should work with both IWP and FileMaker so the fact that they don't would seem to lie with other details we can't yet see.

          To post a script to the forum:

          1. You can upload a screen shot of your script
          2. You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF.
          3. If You have FileMaker advanced, you can generate a database design report and copy the script as text from there.
          4. If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional look to your script. (Use the HTML option on the database tab panel and paste the text into the forum's HTML editor.)
          • 2. Re: Running Reports in IWP
            lime_tree

            Thank you for your quick reply. Here are the two scripts. One works just fine, the other one just results in the browser window refreshing itself:

            Working Script - Search by Source of Contact

            New Window (VIRTUAL WINDOW ON WEB) [ Name: "Search by Source of Contact"; Height: 750; Width: 800; Top: 25; Left: 100 ] If [ Contact Management::c_Source Report = "AMI 2010 (Denver)" ]

            Enter Browse Mode

               Go to Layout [ “Contacts by Source” (Contact Management) ]

               Perform Find [ Specified Find Requests: Find Records; Criteria: Contact Management::c_Source: “="AMI 2010 (Denver)"” ] [ Restore ]

               Sort Records [ Specified Sort Order: Contact Management::c_Source; ascending Contact Management::Status; ascending
                                       Contact Management::c_Stage; ascending ][ Restore; No dialog ]

            Else If [ Contact Management::c_Source Report = "New York 2011" ]

                Enter Browse Mode

                Go to Layout [ “Contacts by Source” (Contact Management) ]

                Perform Find [ Specified Find Requests: Find Records; Criteria: Contact Management::c_Source: “="New York 2011"” ] [ Restore ]

                Sort Records [ Specified Sort Order: Contact Management::c_Source; ascending Contact Management::Status; ascending
                                        Contact Management::c_Stage; ascending ][ Restore; No dialog ]

            Else If [ Contact Management::c_Source Report = "SEMA Show 2011" ]

                Enter Browse Mode   

                Go to Layout [ “Contacts by Source” (Contact Management) ]

                Perform Find [ Specified Find Requests: Find Records; Criteria: Contact Management::c_Source: “="SEMA Show 2011"” ] [ Restore ]

                Sort Records [ Specified Sort Order: Contact Management::c_Source; ascending Contact Management::Status; ascending
                                       Contact Management::c_Stage; ascending ][ Restore; No dialog ]

            End If
            Perform Script
            [ “Print Setup”; Parameter: "landscape" ]
            #Go to Preview mode for earlier versions which do not support summary reports in Browse mode. If [ GetAsNumber ( Get ( ApplicationVersion ) ) < 10 ]

            Enter Browse Mode

            Go to Layout [ original layout ]

            End If


            Script tht works only in FM

            New Window (VIRTUAL WINDOW ON WEB) [ Name: "Overdue"; Height: 750; Width: 800; Top: 25; Left: 100 ] If [ Contact Management::PI Representative Report = "BWB" ]

            Enter Browse Mode
                Go to Layout
            [ “Activities Overdue” (Contacts - Action) ] Enter Find Mode [ ]

                Perform Find [ Specified Find Requests: Find Records; Criteria: Contacts - Action::Meeting Date: “<//” AND Contacts - Action::Meeting Status: “open”  AND Contacts - Action:: Responsible: “BWB”

            Find Records; Criteria: Contact Management::PI Representative: “BWB” AND Contacts - Action::Follow-up Status: “open” AND Contacts - Action::Follow-up Date: “<//” ] [ Restore ]

                Sort Records [ Specified Sort Order: Contacts - Action::Responsible; ascending Contacts - Action::Meeting Date; ascending ]
            [ Restore; No dialog ]

            Else If [ Contact Management::PI Representative Report = "JC" ] Enter Browse Mode
                Go to Layout
            [ “Activities Overdue” (Contacts - Action) ] Enter Find Mode [ ]

               Perform Find [ Specified Find Requests: Find Records; Criteria: Contact Management::PI Representative: “JC” AND Contacts - Action::Meeting Date: “<//” AND Contacts - Action::Meeting Status: “open”

            Find Records; Criteria: Contact Management::PI Representative: “JC” AND Contacts - Action::Follow-up Status: “open” AND Contacts - Action::Follow-up Date: “<//” ] [ Restore ]

               Sort Records [ Specified Sort Order: Contacts - Action::Responsible; ascending Contacts - Action::Meeting Date; ascending ]
            [ Restore; No dialog ]

            Else If [ Contact Management::PI Representative Report = "PS" ] Enter Browse Mode
                Go to Layout
            [ “Activities Overdue” (Contacts - Action) ] Enter Find Mode [ ]

               Perform Find [ Specified Find Requests: Find Records; Criteria: Contact Management::PI Representative: “PS” AND Contacts - Action::Meeting Date: “<//” AND Contacts - Action::Meeting Status: “open”

            Find Records; Criteria: Contact Management::PI Representative: “PS” AND Contacts - Action::Follow-up Status: “open” AND Contacts - Action::Follow-up Date: “<//” ] [ Restore ]

               Sort Records [ Specified Sort Order: Contacts - Action::Responsible; ascending Contacts - Action::Meeting Date; ascending ]
            [ Restore; No dialog ]

            Else If [ Contact Management::PI Representative Report = "RS" ] Enter Browse Mode
               Go to Layout
            [ “Activities Overdue” (Contacts - Action) ] Enter Find Mode [ ]

               Perform Find [ Specified Find Requests: Find Records; Criteria: Contacts - Action::Meeting Date: “<//” AND Contacts - Action::Meeting Status: “open” AND Contacts - Action:: Responsible: “RS”

            Find Records; Criteria: Contact Management::PI Representative: “RS” AND Contacts - Action::Follow-up Status: “open” AND Contacts - Action::Follow-up Date: “<//” ] [ Restore ]

               Sort Records [ Specified Sort Order: Contacts - Action::Responsible; ascending Contacts - Action::Meeting Date; ascending ]
            [ Restore; No dialog ]

             

            Enter Browse Mode
               Go to Layout
            [ “Activities Overdue” (Contacts - Action) ] Enter Find Mode [ ]

               Perform Find [ Specified Find Requests: Find Records; Criteria: Contacts - Action::Meeting Date: “<//” AND Contacts - Action::Meeting Status: “open” AND Contacts - Action:: Responsible: “KN”

            Find Records; Criteria: Contacts - Action::Follow-up Status: “open” AND Contacts - Action::Follow-up Date: “<//” AND Contacts - Action::Responsible: “KN” ] [ Restore ]

               Sort Records [ Specified Sort Order: Contacts - Action::Responsible; ascending Contacts - Action::Meeting Date; ascending ]
            [ Restore; No dialog ]

            Else If [ Contact Management::PI Representative Report = "DG" ] Enter Browse Mode
                Go to Layout
            [ “Activities Overdue” (Contacts - Action) ] Enter Find Mode [ ]

               Perform Find [ Specified Find Requests: Find Records; Criteria: Contacts - Action::Meeting Date: “<//” AND Contacts - Action::Meeting Status: “open” AND Contacts - Action:: Responsible: “DG”

            Find Records; Criteria: Contact Management::PI Representative: “DG” AND Contacts - Action::Follow-up Status: “open” AND Contacts - Action::Follow-up Date: “<//” ] [ Restore ]

               Sort Records [ Specified Sort Order: Contacts - Action::Responsible; ascending Contact Management::c_Priority; ascending
            Contact Management::c_Application; descending
            Contact Management::Company; ascending

            Contacts - Action::Meeting Date; ascending ] [ Restore; No dialog ]

            Else If [ Contact Management::PI Representative Report = "All" ]

               Enter Browse Mode

               Go to Layout [ “Activities Overdue” (Contacts - Action) ]

               Perform Find [ Specified Find Requests: Find Records; Criteria: Contacts - Action::Meeting Date: “<//” AND Contacts - Action::Meeting Status: “open” ] [ Restore ]

               Sort Records [ Specified Sort Order: Contacts - Action::Responsible; ascending Contacts - Action::Meeting Date; ascending ]
            [ Restore; No dialog ]

            End If
            Perform Script
            [ “Print Setup”; Parameter: "Landscape" ]
            #Go to Preview mode for earlier versions which do not support summary reports in Browse mode. If [ GetAsNumber ( Get ( ApplicationVersion ) ) < 10 ]

            Enter Browse Mode

            Go to Layout [ original layout ] End If


            Any feedback is much appreciated.

             

            • 3. Re: Running Reports in IWP
              philmodjunk

              Reformatted your non working script to make it a bit easier to read:

              New Window (VIRTUAL WINDOW ON WEB) [ Name: "Overdue"; Height: 750; Width: 800; Top: 25; Left: 100 ]
              If [ Contact Management::PI Representative Report = "BWB" ]
                  Enter Browse Mode
                  Go to Layout [ “Activities Overdue” (Contacts - Action) ]
                  Enter Find Mode [ ]
                  Perform Find [ Specified Find Requests: Find Records; Criteria: Contacts - Action::Meeting Date: “<//” AND
                         Contacts - Action::Meeting Status: “open”  AND Contacts - Action:: Responsible: “BWB”
                         Find Records; Criteria: Contact Management::PI Representative: “BWB” AND Contacts - Action::Follow-up Status: “open” AND
                         Contacts - Action::Follow-up Date: “<//” ][ Restore ]
                  Sort Records [ Specified Sort Order: Contacts - Action::Responsible; ascending Contacts - Action::Meeting Date; ascending ][ Restore; No dialog ]
              Else If [ Contact Management::PI Representative Report = "JC" ]
                  Enter Browse Mode
                  Go to Layout [ “Activities Overdue” (Contacts - Action) ]
                  Enter Find Mode [ ]
                  Perform Find [ Specified Find Requests: Find Records; Criteria: Contact Management::PI Representative: “JC” AND
                          Contacts - Action::Meeting Date: “<//” AND Contacts - Action::Meeting Status: “open”
                         Find Records; Criteria: Contact Management::PI Representative: “JC” AND Contacts - Action::Follow-up Status: “open” AND
                         Contacts - Action::Follow-up Date: “<//” ][ Restore ]
                  Sort Records [ Specified Sort Order: Contacts - Action::Responsible; ascending Contacts - Action::Meeting Date; ascending ][ Restore; No dialog ]
              Else If [ Contact Management::PI Representative Report = "PS" ]
                  Enter Browse Mode
                  Go to Layout [ “Activities Overdue” (Contacts - Action) ]
                  Enter Find Mode [ ]
                  Perform Find [ Specified Find Requests: Find Records; Criteria: Contact Management::PI Representative: “PS” AND
                        Contacts - Action::Meeting Date: “<//” AND Contacts - Action::Meeting Status: “open”
                        Find Records; Criteria: Contact Management::PI Representative: “PS” AND Contacts - Action::Follow-up Status: “open” AND
                        Contacts - Action::Follow-up Date: “<//” ] [ Restore ]
                  Sort Records [ Specified Sort Order: Contacts - Action::Responsible; ascending Contacts - Action::Meeting Date; ascending ][ Restore; No dialog ]
              Else If [ Contact Management::PI Representative Report = "RS" ]
                  Enter Browse Mode
                  Go to Layout [ “Activities Overdue” (Contacts - Action) ]
                  Enter Find Mode [ ]
                  Perform Find [ Specified Find Requests: Find Records; Criteria: Contacts - Action::Meeting Date: “<//” AND
                        Contacts - Action::Meeting Status: “open” AND Contacts - Action:: Responsible: “RS”
                        Find Records; Criteria: Contact Management::PI Representative: “RS” AND Contacts - Action::Follow-up Status: “open” AND
                        Contacts - Action::Follow-up Date: “<//” ] [ Restore ]
                  Sort Records [ Specified Sort Order: Contacts - Action::Responsible; ascending Contacts - Action::Meeting Date; ascending ][ Restore; No dialog ]
                  Enter Browse Mode
                  Go to Layout [ “Activities Overdue” (Contacts - Action) ]
                  Enter Find Mode [ ]
                  Perform Find [ Specified Find Requests: Find Records; Criteria: Contacts - Action::Meeting Date: “<//” AND
                           Contacts - Action::Meeting Status: “open” AND Contacts - Action:: Responsible: “KN”
                           Find Records; Criteria: Contacts - Action::Follow-up Status: “open” AND Contacts - Action::Follow-up Date: “<//” AND
                            Contacts - Action::Responsible: “KN” ] [ Restore ]
                  Sort Records [ Specified Sort Order: Contacts - Action::Responsible; ascending Contacts - Action::Meeting Date; ascending ][ Restore; No dialog ]
              Else If [ Contact Management::PI Representative Report = "DG" ]
                  Enter Browse Mode
                  Go to Layout [ “Activities Overdue” (Contacts - Action) ]
                  Enter Find Mode [ ]
                  Perform Find [ Specified Find Requests: Find Records; Criteria: Contacts - Action::Meeting Date: “<//” AND
                       Contacts - Action::Meeting Status: “open” AND Contacts - Action:: Responsible: “DG”
                       Find Records; Criteria: Contact Management::PI Representative: “DG” AND Contacts - Action::Follow-up Status: “open” AND
                       Contacts - Action::Follow-up Date: “<//” ] [ Restore ]
                  Sort Records [ Specified Sort Order:
                        Contacts - Action::Responsible; ascending Contact Management::c_Priority; ascending
                        Contact Management::c_Application; descending
                        Contact Management::Company; ascending
                        Contacts - Action::Meeting Date; ascending ] [ Restore; No dialog ]
              Else If [ Contact Management::PI Representative Report = "All" ]
                  Enter Browse Mode
                  Go to Layout [ “Activities Overdue” (Contacts - Action) ]
                  Perform Find [ Specified Find Requests: Find Records; Criteria: Contacts - Action::Meeting Date: “<//” AND
                              Contacts - Action::Meeting Status: “open” ] [ Restore ]
                  Sort Records [ Specified Sort Order: Contacts - Action::Responsible; ascending Contacts - Action::Meeting Date; ascending ][ Restore; No dialog ]
              End If
              Perform Script [ “Print Setup”; Parameter: "Landscape" ]
              #Go to Preview mode for earlier versions which do not support summary reports in Browse mode.
              If [ GetAsNumber ( Get ( ApplicationVersion ) ) < 10 ]
                  Enter Browse Mode
                  Go to Layout [ original layout ]
              End If

              Nothing I see jumps out at me as something that would fail in IWP but work for Filemaker clients. I do see a script that is much more complext than it needs to be. Simplifying the script could help spot the issue where this fails in IWP so that we can fix it.

              Try this scripted technique to get a simpler script, it also avoids using // for the current date so that may resolve the problem here.

              New Window (VIRTUAL WINDOW ON WEB) [ Name: "Overdue"; Height: 750; Width: 800; Top: 25; Left: 100 ]
              Set variable [ $Responsible ; value: Contact Management::PI Representative Report = "BWB" ]
              Go to Layout [ “Activities Overdue” (Contacts - Action) ]
              Enter Find Mode [ ]
              Set Field [Contacts - Action::Meeting Date: Get ( currentDate ) ]
              Set Field [Contacts - Action::Meeting Status: “open”]
              Set Field [Contacts - Action:: Responsible: $Responsible ]
              New Record/Request
              Set Field [Contacts - Action::Follow-up Date: Get ( currentDate ) ]
              Set Field [Contacts - Action::Follow-up Status: “open”]
              Set Field [Contacts - Action:: Responsible: $Responsible ]
              Set Error capture [on]
              Perform Find [ ][ Restore ]
              Sort Records [ Specified Sort Order: Contacts - Action::Responsible; ascending Contacts - Action::Meeting Date; ascending ][ Restore; No dialog ]

              Note that this is a much simpler script as it does not need the If steps to perform the different finds, that appear to use the same criteria except for the "responsible" criteria. It does produce identical sort orders for each find so you may choose to use your If block to control how the records are sorted after the find is performed. This script also use set error capture to keep an error dialog from interrupting your script if no records are found. If you need to inform the user when no records are found, you can include additional scripted steps to do so by using If [ Get ( FoundCount ) = 0] to detect the case when this happens.

              • 4. Re: Running Reports in IWP
                lime_tree

                Yes, I like how the script is a lot shorter and simpler. It also runs on the web!

                However, with this script there are a couple of problems:

                - it shows the overtue tasks for all the sales reps, not just the name selected from the drop down menu on the reports page. Ideally, I would like each sales rep to put their name in and run the report to see just their own overdues.

                - it also show all tasks, regardless if they are overdue, or due in a month.

                Lastly, in this database, The Follow-up date and status fields are not used at all - I have not noticed them creep into my script. This script has been initiated by someone else and since then we have not been using those fields. I guess I can delete them, but that will probably affect the script you suggested I use.

                Here is how I have the script thus far - I got is as closely structured to yours as I could (I am not a very advanced user at all):

                New Window (VIRTUAL WINDOW ON WEB) [ Name: "Overdue"; Height: 750; Width: 800; Top: 25; Left: 100 ]
                Set Variable
                [ $Responsible; Value:Contact Management::PI Representative Report = "BWB" ]
                Go to Layout [ “Activities Overdue” (Contacts - Action) ]
                Enter Find Mode [ ]
                Set Field
                [ Contacts - Action::Meeting Date[Get ( CurrentDate )] ]
                Set Field
                [ Contacts - Action::Meeting Status["open"] ]
                Set Field
                [ Contacts - Action::Responsible[$Responsible] ]
                New Record/Request
                Set Field
                [ Contacts - Action::Follow-up Date[Get ( CurrentDate )] ]
                Set Field
                [ Contacts - Action::Follow-up Status["open"] ]
                Set Field
                [ Contacts - Action::Responsible[$Responsible] ]
                Set Error Capture
                [ On ]
                Perform Find
                [ ]
                Sort Records
                [ Specified Sort Order: Contacts - Action::Meeting Date; ascending Contacts - Action::Responsible; ascending ]
                [ Restore; No dialog ]
                Perform Script
                [ “Print Setup”; Parameter: "Landscape" ]
                #Go to Preview mode for earlier versions which do not support summary reports in Browse mode. If [ GetAsNumber ( Get ( ApplicationVersion ) ) < 10 ]
                Enter Browse Mode
                Go to Layout
                [ original layout ] End If

                Overall, I do see that is is probably the "//" that was causing the problem with IWP publishing. However, I have several more scripts that use "//", which are time-based and are not working on IWP. If we figure out what is causing your script to show all records and all due dates, will it be tough to set up the other scripts with Set Field? Just to give you an idea, they are:

                Due today

                Due in a week

                Due in a month

                Tasks completed today

                Task completed past week

                Tasks completed last month

                All these scripts need to be performed based on the name of the sales rep.

                Thank you very much for your help thus far!

                • 5. Re: Running Reports in IWP
                  philmodjunk

                  - it shows the overtue tasks for all the sales reps, not just the name selected from the drop down menu on the reports page. Ideally, I would like each sales rep to put their name in and run the report to see just their own overdues.

                  It should only find records where Set Field [Contacts - Action:: Responsible has the same data as Contact Management::PI Representative Report--which I assume is your drop down. That's why the script copies this data into a variable before entering find mode, then copies from the variable into the "responsible" field to specify that text as criteria.

                  - it also show all tasks, regardless if they are overdue, or due in a month.

                  I left out an operator in the set field step that uses the current date:

                  Set Field [Contacts - Action::Meeting Date: "<" & Get ( currentDate ) ]

                  and in the second request:

                  Set Field [Contacts - Action::Follow-up Date: "<" & Get ( currentDate ) ]

                  Lastly, in this database, The Follow-up date and status fields are not used at all - I have not noticed them creep into my script.

                  The set field steps reproduce the same criteria used in your current script's stored finds. I'd keep them first and confirm that the script is working correctly, then test a copy of the script by removing those script steps to see if they make a difference. When you test this by removing the follow up date part, remove all these steps:

                  New Record/Request
                  Set Field
                  [ Contacts - Action::Follow-up Date[Get ( CurrentDate )] ]
                  Set Field
                  [ Contacts - Action::Follow-up Status["open"] ]
                  Set Field
                  [ Contacts - Action::Responsible[$Responsible] ]

                  Your current script and mine have two requests. What this does is pull up all records that meet the criteria specified in the first request OR that meet the criteria specified in the second request.

                  It should be possible to rewrite all of your other scripts to use Get ( currentDate ) in place of //. See the two examples in this post and then look at your current script to figure out how to change the criteria.

                  Note that the method I am using produces a script that is quicker/easier to analyze as you can see all the criteria used without needing ot open up a stored find request to see what was specified inside it. It also enables you to create criteria in ways that are impossible with a stored find request. (Such as using the results of a calculation as find criteria.)