3 Replies Latest reply on Jun 14, 2013 8:34 AM by philmodjunk

    Returning Lists From Complex Finds: Assistance Needed



      Returning Lists From Complex Finds: Assistance Needed


           I've got a file with multiple tables in many-to-many relationships.


           Project <---> Join_1 <---> Research_trips<----> Join_2<-----> Research_details

           For each project, there can be many research trips (which might also join to other projects, hence the join table); similarly, there are many research details, each of which might apply to one or more research trips.

           I'm trying to produce a list for export to Word or Pages that contains several details: I need to organize pretty much in descending order:

           WHICH PROJECT (this table will spawn the reports)

           TRIP DATE (and some details from the Research Trips table)

           All the research details for that trip date.


           I'm sure this is ridiculously easy, but I can't seem to get a handle on the best way to export this stuff out to a word-processor that will allow me to order it in reports.   And while I can generate a nice portal that presents this data, I'm even having trouble "Getting Related Records" from the Project Table that produces a list of Research Details for the current Project record.

           If you've dealt with a similar issue and have some ideas of the best way to order and export data, I'd be really grateful: general strategies are welcome, as I've tried several and am sure that I'm missing something conceptually here.  


        • 1. Re: Returning Lists From Complex Finds: Assistance Needed

               THe presence of two join table to facilitate two many to many relationships is why this is anything but simple.  If you report just needed details from Project and Research_Trips, your report could be produced from a found set of records in Join_1. But you also want to liat data from the Research_Details table. Since any given Research detail can be a detail for any number of trips and any number of projects, this becomes a potentially complex issue.

               First cut at producing the needed report would be to create a report from the join_1 layout and use a portal to Research_Details to list all of the research details. If printing or saving as PDF from that layout, you can make the portal many rows of data tall and set it to slide up and to resize the enclosing part.

               Such data could be exported as text to be opened and edited in Word, but many quite sophisticated reports are possible straight from FileMaker so it may not be absolutely necessary to do that export.

          • 2. Re: Returning Lists From Complex Finds: Assistance Needed

                 I'm finding that this portal solution will get the job done.... Using merge fields and a bit of slick formatting, it's quite close to working and the portal looks fine.  (For newbies who've torn their hair out: you actually CAN make your report look like plain paper in spite of the themes.  Right click the "Body" tab in the left hand margin and set the fill-color to white.)

                 But I'm having some trouble because the portal doesn't seem to want to shrink with less data.   Some reports have five details and others thirty, and the portal won't shrink or expand around the amount of data.  I set it to resize the enclosing part, but it doesn't seem to want to do that, so I'm stuck with 8 lines or so of blank space in some reports. 

                 Am I missing something easy? 


            • 3. Re: Returning Lists From Complex Finds: Assistance Needed

                   Sliding fields and portals in FileMaker cannot expand, they can only shrink. Thus you over size your sliding stuff to be larger than you expect to need even for extreme cases and then set them both to slide up and also to resize the enclosing part.

                   And Portals are a special case. Sliding will enable you to get  A portal to reduce down to just the needed number of portal rows, but you can't set a field inside the portal row to slide. For example, if some of the data in your portal row might need two text rows of height instead of one, you can't size the portal row and field for two rows of text and set the field to slide up/resize--you'll end up with the larger portal row size in each portal row where you have data.

                   Key facts about sliding layout objects:

              1.           It's only visible in preview mode and when you print/save as PDF...
              3.           Sliding fields will shrink but not expand.
              5.           All layout objects below and in the same layout part as the slide/resize field need to also be set to slide up and resize.
              7.           Objects in headers and footers will not slide.
              9.           Portals will shrink/slide to fit the number of rows of records, but fields within the portal row will not shrink/slide.
              11.           Fields will slide up only if Top alignment is specified for it and will slide left only if Left alignment is specified.
              13.           Consistent side borders are difficult to achieve with sliding fields.