11 Replies Latest reply on Oct 15, 2012 1:35 AM by Sorbsbuster

    Basic Report

    EricaJackson

      Title

      Basic Report

      Post

           Hi

           Not havign any luck tackling basic report.  Two tables using anchor bouy

           Client::ClientID ------< Intervention::ClientID

           Intervention::Client ID >-------Client::ClientID 

           I can't seem to create a report so that it doesn't look like a spread sheet.

           I want to see a report  that looks like this :

           Joe Doe   zip code  etc   (fields from the Client table)  on one line

                              Intervention details record 1 

                              Intervention details record 2  (and so on relating to the records related to the date of intervention)

           Jane Dane  zip code

                               Intervention details record 1

                               Intervention details record 2

                               Intervention details record 3   (Etc. .....)

           I know I need to base the report layout on the Interventions table but I'm lost as to how to categorize the fields.   The report I have come up with is very cluttered having the client name and address repeated on the same line with each intervention record , only the fields in the body show up in the preview ... very confusing for a novice like me.  I would like to sort the report by the zip code which is a field from the client table so how do I set it up if I base the layout off the Intervention table?

           Thanks for the help!

           Erica

        • 1. Re: Basic Report
          Sorbsbuster

               Base the report on the interventions table.  Include a leading sub-summary part, when sorted by ClientID.  Put the fields from the Client Table in, and only in, that sub-summary part.  Put the Intervention fields in the Body part.

               Only the body fields will show in the browse or preview mode until you sort the records to trigger the sub-summary break.

               If you want to sort by zipcode you can sort it by zipcode and Client ID, but only include a sub-summary part of the Client.  (If that is what you want.  You can show both breaks, if that is what you want instead.)  Note that you don't have to sort (and break) only by fields that are in the table the report is based on - you can set the break value (and hence sort value) to be any related field.  In this case it (could be) zip code.

          • 2. Re: Basic Report
            EricaJackson

                 Good bye to the spread sheet appearance, thanks, I got as far as the sort,   "sort by zipcode you can sort it by zipcode and Client ID, but only include a sub-summary part of the Client" ??? You lost me ,  How do I trigger the "subsummary break" for  that matter  What is a subsummary break ?  I was able to add the part under the   Layouts ----> Part set up----> Part definition  but I'm not sure which box to check ...and the part doesn't seem to do anything, I put the zipcode field in it but it isn't sorting Sorry to not understand,

                 Thanks Erica 

            • 3. Re: Basic Report
              Sorbsbuster

                   Let's drop the 'zipcode' for a second, and just 'break' it by Client ID.

                   Add the 'Part', choose 'leading sub-summary', and in the right hand pane choose the field ClientID.  Add the fields from the Interventions_ClientID table occurance into that part.  (Client Name, Zipcode, etc.)

                   Have the Interventions fields you want in the Body part.

                   Go back to browse mode.  Sort the found set by ClientID.  You will now see the Client name listed once, and below that all their Interventions.

                    

              • 4. Re: Basic Report
                EricaJackson

                     There is no option (that I can find) for a "leading subsummary " there is a "Leading Grand summary"; "Subsumaary when sorted by" and "trailing grand summary".  I am mad at myself, when I sent the last post I had gotten to the point where it appeared as I want without the sort but then I altered/added something and I don't remember what I did to get it there... lost in cyberspace  Anyway the screen shots are the steps I took trying to recreate but it doesn't work. What am I missing? (besides everything?)

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                     .

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                      

                • 5. Re: Basic Report
                  Sorbsbuster

                       Sorry - you should be adding a sub-summary part.  Depending on where you drop the sub-summary part you may be asked a further dialogue - 'Do you want this part to appear above or below the data?' - it becomes either a leading or trailing sub-summary.

                       You show in the screenshots of the layout creation only one sub-summary part (by ClientID), and that would be as intended.  But you show two layout screenshots, one with one sub-summary, and one with two.  I assume the shot with one is the one 'when sorted by Client ID'.  You should place the Last and Zip fields into that part.  There is no need for the second sub-summary (at this stage).

                       You have named the layout 'Sort By Zip Intervention', but as a matter of practice I would avoid naming layouts like that, as you could well use them for a multiple of purposes. I cannot see the part of the status bar that shows me if you have sorted the records or not in your last screenshot, but I suspect not.  If you are using FM11 or 12 you do not have to go into preview mode to see sub-summary parts (you did before).

                       Sort the records by ClientID - the same field as you specified the sub-summary to have as its 'break value'.

                  • 6. Re: Basic Report
                    EricaJackson

                          

                          

                    (I had screen shots placed within this tread as a word document and I copy/pasted it into the post... the screen shots were deleted.... I'm not sure how to upload multiple shots into one post ...)

                    Base the report on the interventions table.   

                          Include a leading sub-summary part, I “include” this part when I am setting up the layout, under “organize records by category”? 

                      when sorted by ClientID.  (which is the foreign key from the interventions table, right?)         Put the Intervention fields in the Body part.  Those fields are already there as included when set up     Put the fields from the Client Table in, and only in, that sub-summary part.  I need to add these fields now from the bouy interventions_Client table?

                     Only the body fields will show in the browse or preview mode until you sort the records to trigger the sub-summary break. Ok I am there but I don’t know how to get to get it to sort to trigger the Sub summary breaks…. That is where I get hung up…..

                    If you want to sort by zipcode you can sort it by zipcode and Client ID, but only include a sub-summary part of the Client.  Where do I do this?

                    (If that is what you want.  I want the report to sort by zip code automatically.Ultimately I will want to search the date and have the report populate in sort order by zip code.  Or area code or territory assigned..or even the suite number for clients that are in the same building,   I will have several different versions of this down the road )  Maybe I need a Script for this? Sorry for more confusion!

                    You can show both breaks, if that is what you want instead.)  I’m not sure what this means Note that you don't have to sort (and break) only by fields that are in the table the report is based on - you can set the break value (and hence sort value) to be any related field.  In this case it (could be) zip code.  How do I sort it by the other fields?  They don’t come up as options. I don’t understand “break” Is that where the chunk of info gathered separates and goes on the the next chunk of information relating to the different client on that same date?

                    So then I posted the other screen shots and your reply is:

                    Sorry - you should be adding a sub-summary part.  Was this meant as “including the part in the set up or adding a second Subsummary part? I have included it in the set as shown above, I did try to add a second one but it wouldn’t add it because it was already there  Depending on where you drop the sub-summary part you may be asked a further dialogue - 'Do you want this part to appear above or below the data?' - it becomes either a leading or trailing sub-summary.

                    You show in the screenshots of the layout creation only one sub-summary part (by ClientID), and that would be as intended.  But you show two layout screenshots, one with one sub-summary, and one with two.  I assume the shot with one is the one 'when sorted by Client ID'.  You should place the Last and Zip fields into that part.  There is no need for the second sub-summary (at this stage).  I guess I am not at the right stage….

                    You have named the layout 'Sort By Zip Intervention', but as a matter of practice I would avoid naming layouts like that, as you could well use them for a multiple of purposes. Thanks for the tip… I am finding as I go along there are patterns related to the naming of tables, objects layouts etc that make much more organizational sense especially in the relationships diagram I cannot see the part of the status bar that shows me if you have sorted the records or not in your last screenshot, but I suspect not. I don’t know how to get this to sort….within the set up… If you are using FM11 or 12 FM 12   you do not have to go into preview mode to see sub-summary parts (you did before). I can see the sub summary parts only in Layout mode, they  are not visible in browse mode or in preview mode…

                    Sort the records by ClientID - This was done during the Layout set up. Am I supposed to sort somewhere else?

                    the same field as you specified the sub-summary to have as its 'break value'.  How do I specify the “Break Value”  checking any of the page break options did not change the appearance at all…I know none are checked in the screen shot I included it to show what I am looking at in case you are referring to a different place , maybe I am not supposed to be in the part defintition….

                          

                          I want to see a report that looks like this : 

                    Date

                           Zip    Joe Doe     (fields from the Client table)  on one line

                                         Intervention details record 1

                                         Intervention details record 2  (and so on relating to the records related to the date of intervention)

                           Zip     Jane Dane 

                                         Intervention details record 1

                                         Intervention details record 2

                                         Intervention details record 3   (Etc. .....)

                    I hope this makes more sense.

                    Thanks

                    Erica

                          

                          

                    • 7. Re: Basic Report
                      Sorbsbuster

                           Sort the records by ClientID - the same field as you specified the sub-summary to have as its 'break value'.

                           Leave the report as it is.  Press Ctrl+S (or Cmd+S) and from the list of fields on the left hand side choose the same field (ClientID) that you selected as the 'Break Field' (the field that, when it changes, will trigger another sub-summary line to be shown) and sort by it.

                           Does the report look like it should now?  You don't have to go into preview mode.

                      • 8. Re: Basic Report
                        EricaJackson

                        Sort the records by ClientID -  Ok in browse mode when I open the "sort" the client id is in there and  I hightlighted it and hit OK  now the client fileds show up and it is sorted in clinet id order.  (this was the missing step from before when I was mad at myself when I couldn't get back to this point)

                        the same field as you specified the sub-summary to have as its 'break value'.  I didn't set up anything with a "break vaue" I don't know where that is

                        Leave the report as it is. Press Ctrl+S (or Cmd+S) and from the list of fields on the left hand side choose the same field (ClientID) that you selected as the 'Break Field' (the field that, when it changes, will trigger another sub-summary line to be shown) and sort by it. In Browse Mode, (which took a minute because  thought all this had to be done in Layout mode... I didn't see another subsummary line to sort by I but I was able to add the zip code field , move it up past the client id and and it does work for this layout.  I don't want to have to physically sort it every time, if I don't sort it everytime the subsummary info doesn't show at all..

                        Does the report look like it should now?      Yes, Yippee! Thank You!   

                             Ok Whew!  so is there a way to set this up s that I can search the date and have it sort automatically without having to click on the sort or do Ctrl + S every time?

                              

                        • 9. Re: Basic Report
                          Sorbsbuster

                               The break value is the field that causes the sub-summary break.  You chose it in your 'Part definition' dialogue.  Sub-summary data will only show when the report is sorted by that field.

                          • 10. Re: Basic Report
                            EricaJackson

                                 Is there a way to set this up so that I can search the date and have it sort automatically without having to click on the sort or do Ctrl + S every time?

                                 basically "if the date = current date , sort by the zip code field in ascending order" . Reports have ben a challenge. Scripts are just plain intimidating!

                                 Thanks 

                                 Erica

                                  

                            • 11. Re: Basic Report
                              Sorbsbuster

                                   There are many ways to do it.  You could create a script that sorts the records, and then tie it as a script trigger to the layout, triggered by OnLayoutLoad.  I wouldn't do that.  You have suggested that you might want to use this layout for multiple purposes.  At least as a start I would make the title text of each column (ClientID, ZipCode, InterventionDate, whatever...) a button, so that when you click on the column header it will sort by that field.  You would then be able to use the same report in various ways.

                                   The script would typically look like:

                                   Enter Browse Mode
                                   Sort Records [specify the sort sequence you want; tick 'Perform without dialogue']
                                   Go to Record [First]
                                   Scroll Window [Home]

                                   You can attach each script to the different column headers.

                                   You can have more sub-summary parts, say zipcode, interventiuon date, client ID and any other value you want.  You can include all those subsummary parts on the same layout, but they will only apear when you sort by them.  So if you want to show a break by zip code, sort by that.  The intervention date subsummary will not appear.  Sort by ClientID and not zip code and the zipcode line will not appear.  Sort by Zipcode and ClientID and they will both appear.