10 Replies Latest reply on Dec 3, 2012 9:53 AM by MilutinPetrovic

    Charts and value list summary fields and some other things

    MilutinPetrovic

      Title

      Charts and value list summary fields and some other things

      Post

           Hello folks!

           I have the final problem of my database here. And it actually two problems. In the attached file is a part of my database with 4 layouts, 2 holding data, 2 holding charts.

           File: http://www.sendspace.com/file/80v8cv

           Problem 1.:

           In layout "Dokumetna" there is a chart made out of 7 fields, and the question is, in this setup (or any other) can the chart be maid to show the data from found set?

            

           Problem 2:

           A chart in layout "Imovina" is based on a Summary-field of a checker box (multiple selection) value list field. The data it shows in graph are inaccurate, plus it joins two of the selections in one!

            

           Any help would be appreciated, this is my final problem in the database, and my boss have been riding me on this for days now! Thanks!

        • 1. Re: Charts and value list summary fields and some other things
          philmodjunk

               1) probably

               2) a single field with multiple checkbox values will not work well for this as it is possible for two or more values to be selected for the same record. I don't have the time at this moment to download your file so I don't know how you have created a "summary field of a checkbox field" given the fact that a check box formatted field can store multiple values in the same field separated by returns.

          • 2. Re: Charts and value list summary fields and some other things
            MilutinPetrovic

                 Is there a way, a script (with global variables) or anything that would count every selection separately?

                  

                 Will it be smart to do a find for first, and then count maximum of records, then second, then third, etc. then store them in global variables, and using them for chart. Use the List() to create the graph? Or something similar? And than on the button back clear the global variables... Will global variables work in IWP and slow down the system in global? Keep in mind that charts will be used every once in the while...

                  

                 Please, please, please help! I need it for the day after tomorow! :(

            • 3. Re: Charts and value list summary fields and some other things
              philmodjunk

                   You can use separate fields with single value value lists for your check boxes.

                   You can also use a related table for recording this data with each value recorded in a different related record.

                   I tried downloading your file, but the site you are using litters their download page with different "download" links that all download executable files instead of your zip file. No link that I clicked would actually download your file.

                   I suggest using a different site such as drop box that does not use delays nor presents you with ad links while waiting for a counter to count down.

              • 5. Re: Charts and value list summary fields and some other things
                philmodjunk

                     1) I took a look at your chart. It IS based on the current found set, even though you have correctly selected the Current Record, delimitted data option for the data source. This is because your delimitted data for the horizontal axis is a list of summary fields and the summmary fields will compute values based on your current found set.

                     2) your summary field will return a count of how many records in your current found set have at least one value selected from your set of check box values. I am assuming that you want to chart the number of times each value was selected over your current found set of records. The easiest way to set that up that I can think of is to use a portal to a related table and then use your value list for a popup menu formatted field in the portal. Then you can create a chart that refers to the data in the portal's table instead of Imovina.

                • 6. Re: Charts and value list summary fields and some other things
                  MilutinPetrovic

                       For the question 1:
                       For some reason, in the full database, it still shows me full records chart, not the one of the found set... It may have to do something with the rest of the database setup?

                  • 7. Re: Charts and value list summary fields and some other things
                    philmodjunk

                         That should not be the case. Check and make sure that you really have the found set that you think that you have. Summary fields compute their values based on the current found set given the way you are using List to compute the values.

                    • 8. Re: Charts and value list summary fields and some other things
                      MilutinPetrovic

                           OK, I found the reason, so now I get different resaults, but unfortunately, incorect.

                            

                           OK, so here is a small explanation:

                           I have a big database, which is in 7 different tables, and all are connected to one main table via the relatinoship where Main table creates and deletes entrees in other tables. So on the subject of question 1. Whan I use the table "Dokumenta" as a basis for the layout, it shows me the records for all data... But when I use the "Main" table as a background of a layout, then I get different records, but unfortunately, wrong!

                           What to do, what to do...

                           On the subject of question two:

                           If I create a related table to "Imovina" would it be possible to create separate field for every checker box option, and the to sum checkerbox selections separately in a found set, without using scripts?

                      • 9. Re: Charts and value list summary fields and some other things
                        philmodjunk

                             1) that is how Summary Fields are designed to evaluate. When your summary fields are from a related table as will be the case when your layout is based on Main, the fields evaluate over the set of related records. If the summary fields are from the layout's table, as is the case when it is Dokumenta, they evaluate over the current found set.

                             2) With the related table, you do not create a separate field for every value. You create a separate record for every value. Then, for the reasons I just outlined for 1) above, you would base your chart layout on this related table and use a summary field defined in this new table to count the number of times each value is selected.

                        How context effects the values computed in summary fields:

                             A "non Running" Summary field produces an aggregate value (a value from more than one field in one record). The value returned is determined by the context in which it is used/displayed:

                        Summary field is referenced on a layout based on the table in which it was defined:

                        A group within a FoundSet

                             If you place the summary field in a subsummary part that specifies the "break" field that grouped the records when the found set was sorted, you get a subtotal--the total for that group.

                             In a calculation, you can use the getSummary function to access the same group based sub total.

                        All the records in a FoundSet

                             If you put that summary field in a layout part other than the sub summary part, you get the total for all the records in the current found set.

                             If you refer to a summary field in a calculation field defined in the same table as the summary field, it will also return a total for the current found set. (Which is why we have the GetSummary function to get sub totals in calculations.)

                        Summary field is referenced on a layout based on a table related to the table in which it was defined:

                        Not in a Filtered Portal

                             If you place the summary field on a layout based on a related table or refer to it in a calculation defined in a related table, the relationship controls the value that is computed. It will be based on all the records in the summary field's table that are related to the current record in this table.

                             Think of it this way, if you put a portal on this layout to the summary field's table, you'd see all the records in this portal that are used to compute the summary field's value in this context.

                        In a Filtered Portal (FileMaker 11 and newer only)

                             If you place that summary field inside a portal with a filter, you no longer get a value based on all the related records. Instead, you see a value based on all related records for which the filter expression evaluates as True.

                             This is a special case use of a summary field that is often implemented by putting a single row copy of a filtered portal below it with the summary field inside so that the user sees a value based on just the records visible in the larger portal.

                             This is a "Display Only" trick as you cannot refer to the value of this field in a calculation and get the same value shown on the layout--you get the result described in "Not in a Filtered Portal" above.

                             Note that this does not just apply to "total" summary fields, Average, Count, Maximum, standard deviation, etc all follow these same rules.

                        • 10. Re: Charts and value list summary fields and some other things
                          MilutinPetrovic

                               I am sad to say, I still have no idea how to fix these issues... :(