5 Replies Latest reply on Jan 9, 2017 12:33 PM by electon

    No way I can make this chart work...

    avallejo

      Ok, now this is officially driving me mad. I've been trying for days to make this work, but I can't figure it out. It shouldn't be that difficult, even for a novice like me. Sure there's something I'm not seeing, so any help would be appreciated.

      I have this table below: (it's in Portuguese but that shoudn't be a problem)

      tabela.png

      I want to chart surgery (3rd column) by surgeon (2nd column). I have sorted the table by surgeon. So I set this up:

      count.png

      It should count how many records have the field "surgery" not empty, or valid, right? It does not. I tried the same with ID, to the same effect. Sorted as it is, I thought it should do the trick... Setup is Current Found Set and Summarized groups of records

       

      Then I try to just reference the field Id (the unique number of the table), but it only shows the first record ID of each surgeon.

       

      cir.png

       

      FM 15 here. Any light would be very welcome!

       

      Best, Andre

        • 1. Re: No way I can make this chart work...
          electon

          If you want to summarize records you will need to create summmary fields.

           

          CountOf ( ID_cir) is the key here.

           

          Use this field on the Y axis, sort the records by chirurg name and it should give you total number of surgeries performed by each chirurg.

           

          Showing counts of each surgery type by chirurg is a bit more involved, if you ever wanted that.

          Try this and let us know if it worked.

           

          Thomas.

          • 2. Re: No way I can make this chart work...
            avallejo

            Thanks, it did help. Learning this things are not very simple. I've been studying, but of course, when we try to gtd, there's usually some glitch we have to solve. Life like I guess. In the process I also found out that I also have to write and attach scripts to every charts to maintain the sort order when it loads, otherwise it picks the sort order of the last chart opened and screws it all (thought I read somewhere that fm saved this script automatically...)

            Now I'll try to showing counts of each surgery type by surgeon! I think it envolves multiple sub summaries... let's see how it goes!

            Thanks

            • 3. Re: No way I can make this chart work...
              electon

              avallejo wrote:

               

              Now I'll try to showing counts of each surgery type by surgeon! I think it envolves multiple sub summaries... let's see how it goes!

               

              It's not that easy with just one table.

              I think that would require a Cross Tab Report because a on a chart it is not the most readable, depending on how much data there is.

              Would look something like this:

               

               

              Surgeon               Segmentectomia     Mastectomia     Sentinela           Total

              Andre Vallejo                    2                              4                    0                    6

              Paula Stephanie               0                              1                    3                    4

               

              If your data is not "normalised" I strongly encourage to do this.

              Basically if you find yourself repeating the same "meaningful" data in a table means that this data should get it's own table and be related by a primary key.

               

              Surgeons and Surgery Type would need to have their own tables.

              Then your table now will become a many-to-many table with fields:

               

              ID_cir, ID_Surgeon, ID_SurgeryType.

               

              Related like this:

               

              Surgeries table

                   ID_Surgeon ---> ID ( Surgeons table )

                   ID_SurgeryType ---> ID ( Surgery Type table )

               

              It's a start. There are also ways without normalising but it's a lengthy explanation...

              Thomas.

              • 4. Re: No way I can make this chart work...
                avallejo

                I see. If by normalizing you mean only allowing predetermined data to be entered, I've done it by creating popup menus linked to value lists. But I can see the difference here, value lists to separated tables. I built the whole solution based on value lists because it was more simple (I have about 30 value lists set up), but maybe I wasn't looking ahead...is that so?

                Thanks!

                • 5. Re: No way I can make this chart work...
                  electon

                  avallejo wrote:

                   

                  I see. If by normalizing you mean only allowing predetermined data to be entered, I've done it by creating popup menus linked to value lists. But I can see the difference here, value lists to separated tables. I built the whole solution based on value lists because it was more simple (I have about 30 value lists set up), but maybe I wasn't looking ahead...is that so?

                  Thanks!

                  Kind of true, you can read up on data normalisation on the Web.

                  Its dificult to look ahead if you learn as you go developing solutions, which is what many people do with FileMaker.

                  It would be mutch easier to run this kind of report using SQL with just the table you have right now.

                  Filemaker works a bit different and there are many ways to achieve what you want.

                  1 of 1 people found this helpful