11 Replies Latest reply on Jun 6, 2017 4:46 PM by philmodjunk

    Interactive Chart

    JulioSandoval

      I have been looking at different videos trying to find a solution or a way to do the following:

       

      I need an interactive chart that will have Headers on the Column and Rows.

      The cells in between need to be interactive to allow the user to change/edit the cell.

       

      For example, please view image below:

       

      Screen Shot 2017-06-05 at 4.52.59 PM.png

       

      Let's call the Columns; Visit Types and the Rows; Procedures

      In between you see Xs implying that there is a charge for the column and row that cell is on.

      Example from image above - The Procedure Written Informed Consent charge is applied on Pre-Screening Visit 1. So there is an X on the cell that intersects between the two.

       

      Does filmmaker have an ability to do this?

      I know I can use script triggers for the Xs and fields to make them into checkboxes.

      Procedures and Visit types are already related through a link table. That is where this chart would live.

       

      But I can't figure out how to create this interactive chart in FileMaker.

       

      Any help is appreciated.

       

      Thank you all.

        • 1. Re: Interactive Chart
          philmodjunk

          Do you just want to click a "cell" to set or clear an X in that cell?

          • 2. Re: Interactive Chart
            JulioSandoval

            Yes. Then script trigger will do other calculations

            • 3. Re: Interactive Chart
              philmodjunk

              Doesn't need a trigger even. Each cell can be a button. Might even use button bars.

               

              What you have is basically a cross tab report and there a number of different ways to set one up

              • 4. Re: Interactive Chart
                JulioSandoval

                Ah so each "cell" needs to be created separately . Hm but the thin is that it changes dynamically. It might have more rows or more columns etc.

                 

                Where would you suggest I start?

                • 5. Re: Interactive Chart
                  philmodjunk

                  A cell can be a button and a field. Thus, it can dynamically display data and still perform a script when clicked. Also a button's label can be a merge field and the labels for buttons in a button bar can even be calculations.

                   

                  For that matter, your "X" can be a graphic object with visibility controlled by a Hide Object When expression.

                   

                  So the main heavy lifting is displaying your data in rows and columns. I'd research "cross tab" here in the forum. I'd probably pass on the webviewer option unless I knew how to make it interactive using Java scripting, but either a portal or repeating field based approach could be made to work using buttons or triggers.

                  • 6. Re: Interactive Chart
                    JulioSandoval

                    I'll give that a try Phil. Thanks.

                    • 7. Re: Interactive Chart
                      JulioSandoval

                      philmodjunk

                       

                      I have been looking at the Cross Tabs as you mentioned. I can see buttons and hiding objects work, but making the columns dynamic though?

                       

                      Also, what do you think would be the best approach when I have different tables that need to be on the rows?

                       

                      I have Procedure Table, Protocol Table, and Visits Table and those have values that would be in the row section.

                       

                      The column amounts also change as well.

                       

                      Thanks Phil.

                      • 8. Re: Interactive Chart
                        philmodjunk

                        Can you be more specific about what issues you see that I don't with making the layout "dynamic"? A button can be clicked to perform a script. The button can also display data from a field. The button can BE a field for that matter--so, as I posted before, I don't see any major issues there.

                         

                        The cross tab report posts all cover several different approaches that can work. Even using an HTML table and a Web Viewer might be used though then you can't use buttons or script triggers to perform a script--you'd have to come up with Java Script in that case.

                         

                        Portals or repeating fields that, via script or calculation "load" with data from your tables might be used here to produce the needed layout with buttons or script triggers used to perform the needed scripts to modify data. Note that any cross tab style report combines data from at least two tables, one for the rows and one for the columns. Multiple tables each providing data for different columns would also be possible.

                         

                        Much of the detail work here depends on the tables and relationships that you have in place as the basis for your layout.

                        • 9. Re: Interactive Chart
                          JulioSandoval

                          So my Base Layout is the Budget layout.

                          The rest are related tables.

                           

                          See image below:

                           

                          Screen Shot 2017-06-06 at 10.56.06 AM.png

                           

                          The goal is to have Budget Visits be the Columns.

                          Procedure Charges are the first set of rows followed by Protocol Charges and then Visit Charges.

                           

                          I want to stay away from HTML and Java since I want to use buttons and scripts.

                           

                          I found an example file where it has Months and Totals. Months are the Rows and the Grand Total are columns but that is just placing a Grand Total Summary layout part.

                          So that example file is ALMOST what I was looking for but not quite.

                           

                          Screen Shot 2017-06-06 at 11.07.47 AM.png

                           

                          What I mean by "dynamic" is the fact that it is not a static set of Columns. Those might change depending on the Budget.

                           

                          Thanks phil.

                          • 10. Re: Interactive Chart
                            bigtom

                            You can have buttons and scripts with HTML and JS. mattman has a good primer on JS charting.

                             

                            The only other way way I can see you doing this is to have the chart based on a Chart table where you truncate and rebuild all the data as needed but that will take time to process each time.

                            • 11. Re: Interactive Chart
                              philmodjunk

                              You can have a fixed number of columns, but change what appears in a given column leaving unneeded columns empty. You might look at "virtual list" for one approach, but portals and repeating fields can manage this as well.

                              1 of 1 people found this helpful