7 Replies Latest reply on Dec 11, 2016 8:06 AM by erolst

    Need to add a report to a dashboard

    catlook

      Newbie question:

       

      I have a basic configuration: projects table, clients table, sales table, and invoices table, with the relationships established.  I also have my first report created (using only the projects table), a sub-summary which shows number of projects per client.

       

      I plan to add several other reports and want to show them on a dashboard.  The youtube examples I've found are very detailed but way more complex than I need at this point.  Will you just explain how I can add my report to a dashboard?  The report has a layout and a script.

       

      Thanks!  -- Cat

        • 1. Re: Need to add a report to a dashboard
          catlook

          Hmmm 26 views and no answers.  Is this a very complex request?  I'd hoped it would be easy, and I was just missing the obvious answer

          • 2. Re: Need to add a report to a dashboard
            erolst

            Well, maybe this went under everybody's radar.

             

            When you say "add a report to the dashboard", what exactly do you mean?

             

            Launch a script that displays a report, eg in a new window? Or show summarized information on the dashboard?

            • 3. Re: Need to add a report to a dashboard
              catlook

              Thanks for responding, even on a weekend, erolst   Very simple, I think.  I have 3 tables "Gigs", "Sales" and "Invoices".  Then I have some scripts/layouts to show things like "Uninvoiced Sales", "Unfinished Gigs", and "Unpaid Invoices".  No pivots or anything, just simple sorting and filtering based on the status field or amount field.

               

              Rather than clicking on the scripts, one at a time to see where I'm at (and what I've forgotten to do), I'd like a screen that contains a labeled box with the results of each script.  And maybe a refresh button.  Is that something Filemaker can do?  I'm guessing I need a Dashboard table, but I have no idea what to put in it!

              • 4. Re: Need to add a report to a dashboard
                erolst

                I'm a freelancer. What is this "weekend" I've heard people talking about ...?

                 

                Anyway ...

                 

                catlook wrote:

                 

                Rather than clicking on the scripts, one at a time to see where I'm at (and what I've forgotten to do), I'd like a screen that contains a labeled box with the results of each script. And maybe a refresh button. Is that something Filemaker can do? I'm guessing I need a Dashboard table, but I have no idea what to put in it!

                Sure. You don't necessarily need a dashboard table, but once you start expanding the features you want in your dashboard, you'll need more fields. With a dedicated table, you don't have to ask yourself where to create them, and you don't clutter the tables that hold your 'real' data.

                 

                Here is a very simple idea onto which you can build (that's all I can give you before I need to go for groceries - but don't hesitate to ask a bit later if you need assistance):

                 

                - create a new table (Dashboard)

                - create a single field; make it a global with, say, three repetitions*; type would be number if you want to return numerical results, otherwise choose text.

                - display the field on the new Dashboard layout with all three repetitions

                - add three buttons, one next to each repetition (or 'cell') of the field

                - attach one of your report scripts to each button

                - modify your scripts to write its respective result into the appropriate result cell (since that result field is a global, it can be accessed from anywhere within the file) and return to the dashboard. A refresh means: launch the script again ;-)

                 

                * You could also create three individual, non-repeating fields.

                 

                If your scripts are context-free, ie do not require to be launched from a record in a specific table, that is good**. Otherwise you have a problem - searching for all time sheets of the current person makes no sense if there is no current person, because you're in the dashboard - namely that you need to build a selection interface.

                 

                ** By which I mean: a script that goes to invoices, finds all invoices of the current month and sums them, it it is context-free, because the parameter "current month" can be calculated anywhere. (Your script of course still needs to go to an Invoices layout ...)

                 

                Note that many dashboard features can be implemented using ExecuteSQL( ), which is useful, but can be tricky ...

                 

                Hope that helps to get you started. As I said: don't hesitate to ask.

                • 5. Re: Need to add a report to a dashboard
                  catlook

                  That's given me a great start...let me play around and see how it goes!  Thanks a million

                  • 6. Re: Need to add a report to a dashboard
                    catlook

                    Ok...still need bit of help. 

                     

                    The Unfinished Gigs script has three lines:

                    Show All Records              (to get rid of any previous filtering)

                    Perform Find [ Restore ]    (to find all records with status of "In Progress" or "On Hold")

                    Go to Layout [ "Unfinished Gigs" (Gigs) ]    (to display a list layout of the gigs)

                     

                    I'm not sure how to modify the script to display the list in the Dashboard global variable (which I called Placeholder). Do I set the variable equal to the layout?  Do I have to build the list manually and assign to variable, rather than using a layout?  So close.....

                    • 7. Re: Need to add a report to a dashboard
                      erolst

                      Hi Cat -

                       

                      my solution was more suited to displaying a single result value (like "Unfinished gigs: 7").

                       

                      Assuming that you want to see a result list - and presumably do something with it - try this:

                       

                      - in your Dashboard table, create a global text field (say, gFoundGigs)

                      - in the Gigs table, create a summary field of type List of: your primary key field (say, sListOfIDs)

                      - in the Relationship Graph, create a new instance (table occurrence) of the Gigs table (say, Gigs_byFoundIDs) and relate it to your Dashboard like so:

                       

                      Dashboard::gFoundGigs = Gigs_byFoundIDs::primaryKeyField

                       

                      - put a portal pointing at Gigs_byFoundIDs on your Dashboard layout

                       

                      Now in your script, after finding the matching Gig records, use

                       

                      Set Field [ Dashboard::gFoundGigs ; Gigs::sListOfIDs ]

                      Go to Layout [ YourDashboardLayout ]

                       

                      As you can see, this is not just a static list, but a list of pointers to the found records that will show in the portal. Put the most important fields into the portal to edit the records directly, and/or use a Go to Related Record to go to a clicked portal record - ie a gig.

                       

                      catlook wrote:

                      Show All Records (to get rid of any previous filtering)

                      With a Perform Find, that is not necessary.

                       

                      Note two things:

                      - once you have that working, you could add a global status selector field to specify one or more statuses to find (ie pass the selection to the script and have it do a looping OR search)

                       

                      - you could use ExecuteSQL( ) to get that list of IDs without leaving the layout, e.g.

                       

                      Set Variable [ $unfinishedGigs ;

                        ExecuteSQL ( "

                          SELECT id

                          FROM Gigs

                          WHERE

                            status = ? OR

                            status = ?

                          " ; "" ; "" ; "In Progress" ; "On Hold"

                        )

                      ]

                      Set Field [ Dashboard::gFoundGigs ; $unfinishedGigs ]