6 Replies Latest reply on Sep 19, 2011 4:26 PM by JasanPalmer

    FMP10 Novice - Year's Dues Report Trouble

    JasanPalmer

      Title

      FMP10 Novice - Year's Dues Report Trouble

      Post

      Self-taught, from scratch (Excel files), and by the skin of my teeth, I created and maintain a DB for a homeowners asso. There are 695 properties, each its own record. I also created a relational table for dues by year, so I can track info. for each year of a property's dues. See sample.

      I created a report to list each property and its 2011 dues status information (if paid, amount, postdate, check#, etc.). It worked until I added 2012 records for everyone. I usually do this at the end of the fiscal year (1 Nov). But someone prepaid. Now the report will only show results for 2012. For the life of me I can't get it to show only 2011 dues status.

      My script reads:

      Show all records
      Enter Browse Mode []
      Go to Layout ["Dues Entry Report-By ID" (SPPOA DB)]
      Sort records [Restore; No dialog]
      #Go to Preview mode for earlier versions which do not support summary reports in Browse mode.
      If [GetAsNumber ( Substitute ( Get ( ApplicationVersion ); "."; "x" )) < 1000]
         Enter Preview Mode [Pause]
         Enter Browse Mode []
         Go to Layout [original layout]
      End If 

      I added a "Perform Find" just above the Sort to find only those with 2011 in the "YearDue" field. It had no effect.

      I hope I have provided sufficient and clear enough information. Suggestions?

      DuesTableSample.png

        • 1. Re: FMP10 Novice - Year's Dues Report Trouble
          Sorbsbuster

          There is a problem with the Perform Find step (which is the only one you didn't post...)

          What happens if you manually find the records for 2012?  Does that find any (it should, obviously)?  If you then run the script your posted with that found set (note you would not include, or disable, the Perfrom Find script step) does it work the way you want it to?

          If you are using the Perform Find script step make sure you leave tagged the 'Restore Find Requests' once you have correctly set up the requests.  More often we tell people to make sure that it UNtagged as they will often build up a script portion like:

          Enter Find mode
          Set Field [YearDue ; "2011" ]
          Perform Find[Restore Find Requests]

          and that last tag undoes the request they set in the script and replaces it with whatever they had defined previously.

          You can do it either way, but try and avoid mistakenly doing it 'both way'.

          • 2. Re: FMP10 Novice - Year's Dues Report Trouble
            JasanPalmer

            OK. It's got to have to do with the layout, not the script.

            1) When I go to a layout called "Dues Table" I can see all the records from the Dues Table and I can manually "Find" all the records for a particular year, including 2012 or 2011. However, if I find 2011 (manually, not by script), and then immediatlely switch to the layout "Dues Entry Report-By ID" it only shows results from 2012.

            2) Also, when I add a "Perform Find" above "Sort…" and change the "Go to Layout" to the "Dues Table" layout instead, all the/only the 2011 is showing, like the script should. But again, if I immediatlely switch to the layout "Dues Entry Report-By ID" it only shows results from 2012.

            It's all seems to be there. But, for some reason it just won't show properly in the "Dues Entry Report-By ID" layout I had been using for this report. I don't understand why a layout would change the content.

            • 3. Re: FMP10 Novice - Year's Dues Report Trouble
              Sorbsbuster

              - does the second layout have a summary part?

              - is the filed you are viewing (with the '2011' or '2012') in a summary part or the body?

              - can you double-check that the field you are viewing on the second layout is exactly the same field as the first?

              - is the second layout based on the same table occurance as the first layout?

              - are there any script triggers associated with loading the sedond layout?

              - when you change layout does the Found Count stay the same?

              • 4. Re: FMP10 Novice - Year's Dues Report Trouble
                JasanPalmer

                The layout that will only show the latest year ("Dues Entry Report-by ID" (= "DER-BI")) does not have any summary fields.

                The DER-BI layout only has a header, body and footer. The header has the title and the column headers. The body only contains normal fields, no summaries. The footer only has date and page number.

                The DER-BI layout did not contain the "YearDue" field in the report. But, when I added it (same field), it showed 2012 all the way down.

                "is the second layout based on the same table occurance as the first layout?" If I understand what this correctly, I will need to investigate and test this more.

                I don't think there are any script triggers in the layout.

                In the "Dues Table" layout it shows 695 / 6968. This is because each main record is multiplied by each year plus, I guess, 18 more from some where. When I switch to the DER-BI layout it only shows all 695 records, with none remaining. This maybe due to the "table occuance" thing you mentioned before. I will have to try to find where to find its status and/or how to change it.

                • 5. Re: FMP10 Novice - Year's Dues Report Trouble
                  Sorbsbuster

                  "In the "Dues Table" layout it shows 695 / 6968. This is because each main record is multiplied by each year plus, I guess, 18 more from some where. When I switch to the DER-BI layout it only shows all 695 records, with none remaining."

                  I think this will show you where the problem lies.  Each table has a number of records in it.  If the first layout is based on your 'Dues' table, then there are 6968 records in that table.  The 695 is the records that match your search in that table.  If you search for 2011 in that table, on that layout, how many records do you find?  If you search for 2012 (same layout) how many do you find?

                  If you enter Layout mode and choose Layouts ->Layout Setup from the menu you will see what Table Occurance the layout is based on.  If it is the same as the layout you are doing the Find in it should have the same xxx/yyyy record count as the other layout when you change from one layout to the other.

                  • 6. Re: FMP10 Novice - Year's Dues Report Trouble
                    JasanPalmer

                    OK. I think I've got it working, with a bit of a work around. Thanks Sorbsbuster for your help.

                    I found in the Layout Setup that the non-working layout had "Show records from: 'SPPOA DB'" the main table with only 695 records. I changed it to "SPPOA Dues" table, but it still wouldn't work. I still couldn't figure out how to "fix" that one.

                    So, I created a new layout from scratch like the original report, but made sure to choose "Show records from: 'SPPOA Dues'" table. This one works. I just have to spend some more time getting the look formated the same as the other.