1 2 Previous Next 26 Replies Latest reply on Jan 5, 2012 3:03 AM by MatthewMackay

    Creating an index at the start of a report.

    MatthewMackay

      Title

      Creating an index at the start of a report.

      Post

      Morning all,

      I've recently had a client enquire about the possibility of an index of contents at the start of each report. Our solution creates property inventory reports.

       

      What they're looking for is something like;

      Pages 1 - 4: Guidance notes. (this will always be the same so could just use a text box for this).

      Pages 5 - 9: Entrance Hallway

      Pages 10 - 12: Bedroom 1

      Pages 13 - 15: Kitchen

       

      and so on.

       

      I think I understand what the script needs to do, basically search for each room record and find which pages of the PDF those records are present on. 

       

      Any ideas?

        • 1. Re: Creating an index at the start of a report.
          philmodjunk

          Not without knowing a lot more about the structure of your database and how the report layout is designed. This could be very difficult or even impossible to automate if you have sliding fields as you can't really calculate with sufficient precision (love to be wrong about this) the number of lines a given block of text will require in such a sliding field.

          Without sliding fields, you can often compute the number of pages for each section by counting the number of records in that section. If you have sub summary parts, you may also need to count the number of groups your records fall in.

          • 2. Re: Creating an index at the start of a report.
            MatthewMackay

            I'll try and give you as much relevant info on the structure as possible. It's not the be all and end all but it would be a nice little feature.

            The structure of my database is;

             

            Property (Parent)

            Rooms (Child)

            Inventory Items (Grandchild)

            Room Photos (Grandchild)

             

            The report is appended from 4 different layouts;

            Front page and guidance notes. This will always be the same number of pages. This is based on the 'Property" table. Sorted by Property::PK_Property_ID.

            Inventory Items. This is normally 1 or 2 pages but could be more. This is the body of the report, based on the 'Inventory Items' table. Sorted by Inventory Items::Category

            Photos. Based on the photos table. This can be numerous pages that are appended directly after the inventory items pages.

            Signatures. Based on the property table. This is always the same number of pages as well.

             

            Let me know what other info you'd need.

             

            Thanks

            • 3. Re: Creating an index at the start of a report.
              philmodjunk

              Inventory Items. This is normally 1 or 2 pages but could be more.

              What determines the number of pages in this section? The number of records or do you have sliding fields that have varying amounts of information?

              Need to know the same about your photos.

              • 4. Re: Creating an index at the start of a report.
                MatthewMackay

                The number of records determines the number of pages in both the inventory items and photos sections.

                 

                They are both list view layouts.

                • 5. Re: Creating an index at the start of a report.
                  philmodjunk

                  Then you simply need to pull up those records and use Get ( FoundCount ) to capture the number of records in each set. Your script can load this data into a set of records/fields or even global variables (only in FileMaker 11) for use in your report.

                  • 6. Re: Creating an index at the start of a report.
                    MatthewMackay

                    Sorry I misread your previous comments, I do have sliding fields with varying amounts of information within the body of the report

                    • 7. Re: Creating an index at the start of a report.
                      philmodjunk

                      I thought so as I think we've discussed this report before when you had trouble gettting things to slide correctly.

                      Then you won't be able to count the records as the number of records do not tell you the number of pages. If each part of your report is saved as PDF from a different layout, however, you can use a script to count the pages for that section.

                      Pull up the records you need for that report section and sort them.

                      Then perform these steps in your script to capture the total pages for that section:

                      Enter Preview Mode[]
                      Go To Record/Request/Page [last]
                      Set Variable [$$Pages; Value: Get ( PageNumber ) ]

                      You can also use set field with a global field if you prefer that over using a global variable.

                      • 8. Re: Creating an index at the start of a report.
                        MatthewMackay

                        Ah I don't think this is going to be possible.

                         

                        Each part of my report is built from seperate layouts. However the main part of my report is built up from 1 layout which lists all the inventory items with their parent (Room Name) as the header. The index needs to determine when 1 room ends and the next begins within 1 layout. If it helps there is always a page break after each room.

                        • 9. Re: Creating an index at the start of a report.
                          philmodjunk

                          If you want to do it, it'll take even more scripting. You'd have to preview the report and use get ( pagenumber ) in a loop with Go to record/request/page [next ; exit after last] to flip through each page of your report logging when each room ID changes.

                          • 10. Re: Creating an index at the start of a report.
                            MatthewMackay

                            I'm really struggling to even begin to write this script. At the moment i use the method described here: http://sixfriedrice.com/wp/custom-page-numbers-in-pdfs-and-printed-reports/ to number all my pages in the appended PDF.

                             

                            I think I've grasped what filemaker needs to do and I think I can work out the script for this but no idea how it would display the information. Just need to find the page number of the first occurrence of a Room record, find the last page of that same Room record, then I have the information I'm looking for but ss the number of rooms can always be different, how would it be possible to display this info?

                            • 11. Re: Creating an index at the start of a report.
                              philmodjunk

                              but ss the number of rooms can always be different, how would it be possible to display this info?

                              I suggest adding a related table to your database for this. Then you can use a portal enabled with sliding options specified to display your index. Each "index" entry would be one record in this portal.

                              • 12. Re: Creating an index at the start of a report.
                                MatthewMackay

                                I'm still not sure how to obtain the page numbers to display in the portal?

                                • 13. Re: Creating an index at the start of a report.
                                  philmodjunk

                                  The method I am describing is a bit tedious, but you preview the section of the report, use Get ( PageNumber ) to get the page number of the last page, then create a record in the related table listing the section info, including the page numbers. These page numbers will only be correct as long as you do not make changes that alter the page counts for a section of the report so you may want to set this up so that the table of related records is purged and updated each time you produce the report.

                                  • 14. Re: Creating an index at the start of a report.
                                    MatthewMackay

                                    Ok here is my current script for creating the report, I've created a new table and the fields required for displaying the information I require, could you help me with the script steps and where they should be placed?

                                    Inventory Report Appending

                                    Set Field [ Property::Page Start; 1 ]

                                    Set Variable [ $$PropertyID; Value:Property::PK_Property_ID ]

                                    Set Variable [ $Path; Value:"file:" & Get(DesktopPath) & Property::Test Report Address & " " & "Inventory" & ".PDF" ]

                                    Set Variable [ $CorePath; Value:"file:" & Get(DesktopPath) & Property::Test Report Address & " " & "Inventory Core File" & ".fp7" ]

                                    Go to Layout [ “Title & Overviews” (Property) ]

                                    Enter Find Mode [ ]

                                    Set Field [ Property::PK_Property_ID; $$PropertyID ]

                                    Perform Find [ ]

                                    Sort Records [ Specified Sort Order: Property::PK_Property_ID; ascending ]

                                    [ Restore; No dialog ]

                                    Enter Preview Mode

                                    Save Records as PDF [ File Name: “$Path”; Records being browsed ][ Restore; No dialog ]

                                    Go to Record/Request/Page [ Last ]

                                    Set Field [ Property::Page Start; Property::Page Start + Get (PageNumber) ]

                                    Enter Browse Mode

                                    Go to Layout [ “Rooms Append Use” (Rooms) ]

                                    Show All Records

                                    Sort Records [ Specified Sort Order: Rooms::Sort Room; ascending ] [ Restore; No dialog ]

                                    Go to Record/Request/Page

                                    [ First ]

                                    Loop Set Variable [ $RoomID; Value:Rooms::PK_Room_ID ]

                                    Go to Layout [ “Inventory Items” (Room Inventory) ]

                                    Set Error Capture [ On ]

                                    Enter Find Mode [ ]

                                    Set Field [ Rooms::PK_Room_ID; $RoomID ]

                                    Perform Find [ ]

                                    If [ Get (FoundCount) > 0 ]

                                    Sort Records [ Specified Sort Order: Room Inventory::Category; descending ] [ Restore; No dialog ]

                                    Enter Preview Mode

                                    Save Records as PDF [ File Name: “$Path”; Records being browsed ][ Restore; Append; No dialog ]

                                    Go to Record/Request/Page [ Last ]

                                    Set Field [ Property::Page Start; Property::Page Start + Get (PageNumber) ]

                                    Go to Layout [ “Photos” (Photos) ]

                                    Enter Find Mode [ ]

                                    Set Field [ Rooms::PK_Room_ID; $RoomID ]

                                    Set Error Capture [ On ]

                                    Perform Find [ ]

                                    If [ Get (FoundCount) > 0 ]

                                    Enter Preview Mode

                                    Save Records as PDF [ File Name: “$Path”; Records being browsed ][ Restore; Append; No dialog ]

                                    Go to Record/Request/Page [ Last ]

                                    Set Field [ Property::Page Start; Property::Page Start + Get (PageNumber) ]

                                    Enter Browse Mode

                                    Go to Layout [ “Rooms Append Use” (Rooms) ]

                                    Go to Record/Request/Page [ Next; Exit after last ]

                                    Enter Browse Mode

                                    Go to Layout [ “Rooms Append Use” (Rooms) ]

                                    Go to Record/Request/Page [ Next; Exit after last ]

                                    End If

                                    Else

                                    Go to Layout [ “Photos” (Photos) ]

                                    Enter Find Mode [ ]

                                    Set Field [ Rooms::PK_Room_ID; $RoomID ]

                                    Set Error Capture [ On ]

                                    Perform Find [ ]

                                    If [ Get (FoundCount) > 0 ]

                                    Enter Preview Mode

                                    Save Records as PDF [ File Name: “$Path”; Records being browsed ][ Restore; Append; No dialog ]

                                    Go to Record/Request/Page [ Last ]

                                    Set Field [ Property::Page Start; Property::Page Start + Get (PageNumber) ]

                                    Enter Browse Mode

                                    Go to Layout [ “Rooms Append Use” (Rooms) ]

                                    Go to Record/Request/Page [ Next; Exit after last ]

                                    End If End If

                                    End Loop

                                    Enter Browse Mode

                                    Go to Layout [ “Report Signatures” (Property) ]

                                    Enter Find Mode [ ]

                                    Set Field [ Property::PK_Property_ID; $$PropertyID ]

                                    Perform Find [ ]

                                    Enter Preview Mode

                                    Save Records as PDF [ File Name: “$Path”; Records being browsed ][ Restore; Append; No dialog ]

                                    Go to Record/Request/Page [ Last ]

                                    Set Field [ Property::Page Start; Property::Page Start + Get (PageNumber) ]

                                    Save a Copy as [ “$CorePath” ] [ copy of current file ]

                                    Close File [ Current File ]

                                     

                                    Thanks for your help so far

                                    1 2 Previous Next