4 Replies Latest reply on Aug 8, 2012 8:36 AM by Mingrl88

    How do I populate a report, with ONLY related records data?



      How do I populate a report, with ONLY related records data?


      Hi Every one,

      Firstly, let me just say I am a n00b at database design. I have used a lot of different databases as an end user, and have a small amount of knowledge with small SQL databases for a commerce website that I built once, but as far as design fundamentals go..........fail!

      Now......I am creating a database that reports on inspections of buildings. Basically I have set it up so that Shopping Centre info (ie address, name etc) are set up in a table, The main Audit layout allows selection of the shopping centre from a drop down list, and extra info is then added (i.e. shop name, shop number, inspector name)
      from there a button click takes you to the inspection screen, which has inspection details broken down into categories, for which I have setup some conditional drop down lists. I have generated a report layout from the inspection layout, which takes information entered in and uses a <> to insert text to a report for me, which seems to work fine.

      What I cannot figure out, is how I can show only related record data in the report. All layouts are linked by a shoppingCentreID field which allows me to keep records related in the layouts. So now I have 2 questions:

      1. How do I get only the records relating to a specific shoppingCentreID into a report
      2. is it possible to have only related records from shoppingCentreID AND a date field, populate to a report?

      what i mean by this and the reason for this is, I need to generate a report on each inspection I do. if I pull the records from ONLY the shoppingCentreID field, then any inspection I have done for that shopping centre will populate that report. I need it to be only TODAY's record, created under that shoppingCentreID that populates the report. Is this possible?

      I hope this makes sense.......

      Look forward to your advice

        • 1. Re: How do I populate a report, with ONLY related records data?

          To only report data from the related table, base your layout on the related table. To limit the records in the report to a specific ID and date, there are two options:

          1) Perform a find on the report layout for all records of a specified ID and Date. This could be done manually or in a script.

          2) A script from the parent table's layout can use GoTo Related records to pull up all records linked to the current record. If this produces a found set of inspection data for more than one date, constrain found set may be used to limit the found set to records of a specified date.

          Note: If you are repeatedly inspecting the same Shopping Center, it may be helpful to set up a table where each record represents a single inspection of a specific shopping center.

          • 2. Re: How do I populate a report, with ONLY related records data?

            Hi Phil,

            Thats Great, It seems to work exactly as I want it to, as far as the information that I want on the report. Thanks for that!! The only problem now is that all of the details are going into the report layout, and I cannot figure out how to get the report to save seperate files for each individual shop.

            For example, Now the report sorts data form each individual shop that I inspect and when I hit preview, It shows a page per shop, and on each page it has a break down of inspections done in that particular shop. When I save as PDF it saves ALL of the pages into one file. What I would like to happen is have some sort of script (i guess) that saves each store page as a seperate PDF, and have it named using the Shop name information.

            Is this possible?

            • 3. Re: How do I populate a report, with ONLY related records data?

              You should limit your found set to just the records for that one store. Then your PDF will only include records from that one store when you save it as a PDF.

              If you read my last post again, I describe two methods for limiting the records in the report for a specific store and/or a range of dates.

              • 4. Re: How do I populate a report, with ONLY related records data?

                Lloyd, When I go to print preview and select "Save as PDF" I am able to select whether I want it to save all of the records in the found set, just the current record, or even a blank record showing no data. If I understand your question that might help you out I'm not sure. If you didn't know about it though, that is a nifty little feature!