8 Replies Latest reply on Mar 12, 2013 2:30 PM by philmodjunk

    Display in header the 1st and last record of the page

    LyleMillander

      Title

      Display in header the 1st and last record of the page

      Post

           Hello all.

           I have a simple and flat FMP database that is a catalog of content formatted very much like the old white pages phone book. Just like in a phone book, I wish to display in the header of each page the contents of one field from the first and last records on that page. So basically, if the page starts with the last name "Miller" and the last name on that page is "Numbskull," the header would display, "Miller - Numbskull."

           If I place the desired field in the header, I get the field from the first record on the page - great. If I place that field in the footer, I get the value from the last record on the page - okay. I just can't figure out how to grab that last record value and display it in the header!

           Many thanks in advance for any guidance on this quandary.

           -Lyle

        • 1. Re: Display in header the 1st and last record of the page
          philmodjunk

               Is the number of records per page exactly the same? (If you have sliding fields, the number of records per page may vary with each page.)

               Either way, this can be done, but the details are simpler if the number of records per page are exactly the same for every page save the last one.

          • 2. Re: Display in header the 1st and last record of the page
            LyleMillander

                 Hi PhilModJunk,

                 Thanks for the response.

                 My intention is to avoid vertically sliding fields and keep the number of records per page consistent (excluding the final page, of course). I wouldn't mind having info on both solutions in case something changes, but I'd be thrilled just to have the one.

                 Regards,

                 Lyle

            • 3. Re: Display in header the 1st and last record of the page
              philmodjunk

                   I will assume that this is a found set of records sorted into a specific order. They may or may not be all the records in the table.

                   A script can set up the values needed just before printing, previewing, saving as PDF to make a self join able to access the last record on a given page.

                   I will also assume that 30 records are the maximum number to fit on a page. Substitute the actual number for 30 in the following example script:

              Show Omitted Only
                   Replace Field Contents [no dialog ; YourTable::Sequeence ; "" ]
                   Show Omitted Only

                   Sort Records [Restore ; no dialog ]
                   Replace Field Contents [no dialog ; YourTable::Sequence ; serial numbers ]

                   The steps shown in red can be removed if your report always displays all records in your found set.

                   You might think that you can set up Sequence as a calcualtion field with Get ( RecordNumber ) instead of using a script, but this must be a stored, indexed field for this method to work.

                   Now a calculation field, cPageNumber, can compute the page number:

                   ceiling ( Sequence / 30 )

                   Now we can set up a self join relationship by page number:

                   YourTable::cPageNumber = YourTable|SamePage::cPageNumber

                   And now you can add a calculation field that uses the Last function to display the last value of your text field from YourTable|SamePage to your header to show that last value.

                   Doing this with sliding fields works much the same, but you have to use this script to assign a page number to every record:

                   Enter Preview Mode []
                   Replace Field Contents [no dialog ; YOurtable::pageNumber ; Get ( PageNumber) ]

                   I am suggesting the alternative approach to this on the assumption that it will execute more quickly than this last method which has to preview every page in your report.

              • 4. Re: Display in header the 1st and last record of the page
                LyleMillander

                     Brilliant!

                     I will attempt to execute this tonight and will post back with any feedback.

                     Many thanks for taking the time to assist.

                     I found another posting of yours with info on how to use the Mod function to place page numbers as appropriate for facing pages. So, thanks x2.

                     Regards,

                     Lyle

                • 5. Re: Display in header the 1st and last record of the page
                  LyleMillander

                  Hi PhilModJunk,

                       First, let me thank you again for your kind assistance.

                       Initially, I attempted to integrate your suggestions into my existing database. It didn't go well for me, so I created a fresh database with test data built around your guide.

                       I have a table named "YourTable" with the following fields (only including fields involved in scripts and calculations):

                       N2 (text, indexed)

                       SEQUENCE (number, indexed)

                       cPageNumber (calc, indexed, from YourTable, = Ceiling ( SEQUENCE / 104 )

                       N2_Last (calc, unsorted, from YourTable, = Last (YourTable2::N2)  ...  YourTable2 being the name of the related table created by the self-join.

                       I have a self-join relationship (embarrassingly, my first one ever): YourTable::cPageNumber = YourTable2::cPageNumber

                       ^ none of the allow, delete, or sort options are enabled for the self-join relationship.

                       I have a script titled 'sort_and_sequence" that appears as follows:

                       Go to Layout ["Document" (YourTable)]  ... document is the 4-column 'book' page with 104 records displayed.

                       Enter Browse Mode

                       Sort Records [Restore; No dialog]  ... sorts by YourTable::N2, ascending.

                       Replace Field Contents [No dialog; YourTable::SEQUENCE; Serial numbers]

                       I activate the script and enter preview mode. cPageNumber, located in the header, accurately shows the page number. Looking at a table view layout, I can see that records 1-104 are assigned a cPageNumber of 1. Record 105 increments to a cPageNumber value of 2. So far, so good.

                       My failing is with N2_Last, also located in the header. On page 1, this field displays the 23rd record. On page 2, N2_Last displays the 75th record of the page. Page 3, it displays the 46th record of the page. Etc, etc.

                       At one point during my experiementation, I sorted YourTable2 by N2. N2_Last was then correct, but my records were no longer sorted alphabetically.

                       I'm clearly not smart enough to get the light bulb that takes me past this. Any additional assistance you can provide would be hugely helpful.

                       Regards,

                       Lyle

                  • 6. Re: Display in header the 1st and last record of the page
                    philmodjunk

                         Try specifying a sort order for  the self join relationship that sorts the related records (YourTable2) in the same order that you are sorting your records with this script.

                    • 7. Re: Display in header the 1st and last record of the page
                      LyleMillander

                           OMG! I had been sorting YourTable2 both manually, and in the script with no success. Specifying the sort in the 'edit relationship' pane did the magic. I am so jazzed!

                           Thank you, thank you, thank you!

                      • 8. Re: Display in header the 1st and last record of the page
                        philmodjunk

                             YourTable and YourTable2 are two references to the exact same set of records.