1 2 Previous Next 18 Replies Latest reply on Dec 14, 2011 3:25 PM by philmodjunk

    Making a Report with multiple types



      Making a Report with multiple types


      I am not sure how to describe my problem.

      Here is the situation. I have a list of plants. Each of those plants can be a Shrub, or a Patio Tree. Some of these plants can be both. I assign the plant type using a value list. The value list values are like this: Shrub, Patio Tree, Shrub or Patio Tree (this is the same plant pruned or grown to have a shrb shade or a small tree shape).

      It is the desired report that is causing me agita. I know how to do submummary reports and now that I have FM11 I see the reports on screen without going to Preview mode. Hooray!

      The screenshot gives a better idea of what I want. This report is sorted on Type. See where it shows 'Tree or Patio Tree'? That is not what I want. In fact, I don't want a 'Tree or Patio Tree' subsummary at all. I want, for example, Caesalpinia to appear under Tree and I want it to appear under Patio Tree. Same record, appearing twice. 

      Why do I need this to look like this? Siplicity. This report sees very wide circulation and if people want to find Patio Trees I want them to have a list of Patio Trees. Having 'A or B' introduces too many questions. Simple is best here.

      This seems so simple but I cannot figure  it out? Do I need to re-think the Category and associated Value LIst? But then how do I get the same plant to appear in two categories? I thought about using a script and I know how to loop around and get the plant names, but I don't see how to get the report to llook the way I need it to look. I also thought about portals, which I sort of understand, but I am decidedly not clear on how to do self-join relationships which, unless I am wrong, is the thing that I might need here.




        • 1. Re: Making a Report with multiple types

          What you have is a very simple many to many relationship best handled by adding two additional tables to your database. This will then give you a table where a "tree or shrub" plant is listed twice, once in each category.


          Plant::PlantID = Plant_Category::PlantID
          Categories::CategoryName = Plant::Category::CategoryName

          (The second relationship may not be needed in your specific case.)

          You can use a portal to Plant_Category to specify each category for which the plant is a member. Almost all plants, from what I can see, will be in just one category, but in cases where a plant can be "Shrub or Patio Tree", you create two records in Plant_Category, one for "Shrub" and one for "Patio Tree". This can be set up with a portal on your data entry layout where it looks and functions like a check box group if you wish. (each portal row has it's own check box.)

          Now you can set up your summary report on a layout based on Plant_Category, (the "join table") and you can list the same plant in more than one category.

          A script can loop through your existing records to create these related records as a way to make the design change less work.

          • 2. Re: Making a Report with multiple types

            Turns out I was replying directly to the email notifications instead of coming back here. Finally Mr. Kurasch kindly told me what to do.

            So, in answer to Phil's generous help, here is what I answered in those unforwardable emails, with my thanks:

            Ok, Phil, gosh, thanks a lot, and let me ask you:
            1. As of now all data in in one table It is called Data.
            2. You want me to make a table called 'Plants' and one called Plant_Category? Is this correct?
            3. are we saying that instead of having everything in 'data', we have records in a different table according to type? in fact what about this approach? 5 different tables?
            Keep in mind what I am trying to present which is a simple subsummary-type report for folks where some plants occur more than once. Only a few, actually, and at some point it might make more sense to duplicate those records! Of about 500 records, 100 or so  are in these 'double categories'. I could just go through the records and make duplicates for all of the ones that fall into two categories, but this seems like a very gory solution and hard to maintain, and I haven't gone this way because somehow it just doesn't seem true to data integrity.
            if I need a global in order to link the new tables, would you mind showing me how it is done?
            • 3. Re: Making a Report with multiple types

              2. What I call "plants" and what you call "data" are the same table. You would need to add one new table, Plant_Category so that the same plant can list more than one category.

              3. in fact what about this approach? 5 different tables?

              No, just two tables, one to list all the plants (data) and one to list, for each plant, all the categories to which it belongs. Your simple sub summary report can be based on the Pant_category table with related fields from Data (plants) included to fill in the additional, plant specific details.

              It's perfectly valid data integrity. Each table stores different data for different purposes. The only value in common between the two would be the ID field used to link them.

              There is no need for a global field to do this. You might, however, choose to use a global variable or two in a script to make your one time conversion from a single table into two tables as they can be used to transfer the ID number and category data from the current table into the new. (Once this is done, you'd delete the category field from the data (plants) table.

              Here's a demo file:http://www.4shared.com/file/P6xluvzg/PlantswManyCategories.html

              • 4. Re: Making a Report with multiple types

                Don't you love it when a mountain seems so high and difficult to climb but turns out to be a mere hill

                I get now what we are doing! When I first tried it, I got a message saying I couldn't use the target because theey were not linked. Of course not. Doh.

                Which I now know what it means. But do I need to populate Categories with all of the plant names from data (plants, you call it)? I looked t the example (thanks!) and that part isnt clear? How else does categories know what is going on? Or does it need to?



                • 5. Re: Making a Report with multiple types

                  What I mean is that I put Type from Categories on a Data/Plants record, and used a Value LIst from Plants. And that is what FileMaker showed me. I'll experiment a bit more.

                  • 6. Re: Making a Report with multiple types

                    Take a look at the categories layout while in table view so that you can see all fields and all records.

                    There are three plant records in the plants table and 4 records in the Pant_Categories table. For "Field Corn" there's just one category: Grass. For Sequioia Redwood, there's just one entry: "Tree". And for Pomegranite, there are two entries: Tree and Shrub. Thus, for your existing records, you'll need to create one or more records in the categories table to take the place of your current categories field. This can be done with a one time "batch fix" script that loops through all your records, looping through the list of categories in your current Data::category field for each such record to create one related record for each entry.

                    #Do this on a layout based on Data
                    Show All Records
                    Go to Record/Request/Page [first]
                       Set Variable [$PlantID ; value: Data::__pk_PlantID]
                       Set Variable [$Categories ; value: Data::Categories]
                       Go to layout [Plant_Categories ( Plant_Categories) ]
                       Set Variable [$I ; value; 0 ]
                          Exit If [$I > valuecount ( $Categories ) ]
                          New Record/Request
                          Set Field [Plant_categories::_fk_PlantID ; $PlantID ]
                          Set Field [Plant_Categories::Category ; GetValue ( $Categories ; $I ) ]
                          Set variable [$I ; value: $I + 1 ]
                       End Loop
                       Go to Layout [original layout]
                       Go to record/request/page [next ; exit after last]
                    End Loop

                    Note: if you do not have a serial number field in your data table for uniquely identifying each record, you should add one and use replace field contents to load the field with serial numbers before attempting the above scripted update.

                    • 7. Re: Making a Report with multiple types

                      can't wait to play with this in the morning!

                      • 8. Re: Making a Report with multiple types

                        Now see Phil here's your first mistake: you figured I was smarter than I actually am…You said this was simple. Hahhahahahaha!

                        First thing I tried was just pasting your script. This apparently only works from script to script. So, I started typing it in, the exercise being a good way to see what is happening.

                        Starting with the first line, turns out FM doesn't like brackets, so I put parenthesis, but this did not help me much, as I don't know the proper form, for example, for 'value: Data::__pk_PlantID]'

                        As to numbers, yes, I learned long ago that numbers are essential.

                        I think I see the script doing this: copying from Data any plant that has two Categories, so there would be a copy in both DBs. Correct? Of course we'd set it up so that editing either edits both. Seems like it would be easier to just call the record from Data to Categories when needed but what do I know? Apparently a lot less than I thought.

                        But where I am going to need more of your very helpful time is in cleaning up the script a bit? My db names are pfd and plant list. I've tried substituting these in logical places without success.



                        • 9. Re: Making a Report with multiple types

                          If you see brackets in my script above, you should see them also in your script editor. The trick here is not to enter these brackets at all. FileMaker will add them for you.

                          The script will need to do this for all plant records in Data--whether it has one or many types.

                          Here's what is likely the problem:

                          When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                          • 10. Re: Making a Report with multiple types

                            on the seventh line:

                            Set Variable [$I ; value; 0 ]

                            FM complains about not finding the table. I presume this is Data?


                            Making pogress. The hints helped immensly.


                            • 11. Re: Making a Report with multiple types

                              Don't try to type in "value:". Just like the semicolon, Filemaker adds this label for you. With the Set Variable dialog open, enter $I in the Name box and 0 in the value box.

                              • 12. Re: Making a Report with multiple types

                                ok, making some serious progress. Thanks for the hints.


                                Problems are:

                                $l must be the letter L, not the number 1,because otherwise FM will barf;

                                      Exit If [$I > valuecount ( $Categories ) ]  no place to add the valuecount function?

                                Set Field [Plant_categories::_fk_PlantID ; $PlantID ]  what is _fk_Plant ID? 

                                by the way unlike you  pros I eschew the underscores nd type the text without them. "Plant-categories' becomes "Plant Categories", for example.

                                Here is what I have so far (attached as cut and paste don't work). The script is called 'Phil's Script' at the end. There's quite a bit of trila trash in this DB, too.


                                (change the jpg ending to zip as the rules below say only pictures are uploaded. After changing to zip just decompress. Thanks!



                                • 13. Re: Making a Report with multiple types

                                  $l must be the letter L, not the number 1,because otherwise FM will barf;

                                  $I is the upper case i. any letter will serve as long as you use the same letter throughout the script where you see this variable used.

                                  Exit If [$I > valuecount ( $Categories ) ]  no place to add the valuecount function?

                                  There's a typo there on my part. This should be the Exit Loop IF step. Double Click it or click the specify button after you add it to your script.

                                  Set Field [Plant_categories::_fk_PlantID ; $PlantID ]  what is _fk_Plant ID?

                                  _fk_PlantID is a number field. _ as the first letter of the field name makes sure that an alphabetical sort of the field names sorts it first. fk identifies it as a "foreign key". This is just a naming convention many developers find useful. You can name this field anyway you find useful to you as long as you match it to the plantID field to your original table when you set up this relationship.

                                  As you can see in your post above, you cannot attach a file to a post in this forum except for uploading GIF, JPG, PNG files up to 20MB in size. You can upload such a file to a share site and post the download link it here, but I doubt you need to just yet...

                                  • 14. Re: Making a Report with multiple types

                                    so, zipping and changing the ext didn't work. rats! computers are too smart!

                                    1 2 Previous Next