1 2 Previous Next 20 Replies Latest reply on Feb 20, 2014 2:25 PM by rmittelman

    Multi-column "address book" report

    rmittelman

      This should be easy, I do it all the time in MS Access. I want to have an address book report which shows names, addresses, phone numbers, emails, etc.

      The names and addresses are in the main contacts table, while the phones and emails are in their own tables, linked by relationships to the contacts table. Therefore, I will need a portal for each.

       

      It should look something like:

       

      A

      Sam Adams Home: 888-888-8888

      1234 Main St. Cell: 888-555-5555

      Anytown, CA 99999 Email1: Sam@me.com

      Email2: Me@you.com

       

      Joe Anderson Home: 777-888-8999

      2345 Main St. Work: 333-333-3333

      MyTown, CA 33333 Email1: Joe@me.com

       

      etc. etc.

       

      Later I will attempt to make it multi-column so I get 2 columns on each page. for now, I need to figure out how to group this and do the portals so it comes out looking like the above example. It should group on the first letter of the last name.

       

      Any ideas on how to do this? Thanks in advance.

        • 1. Re: Multi-column "address book" report
          beverly

          don't use portals. use a "gtrr" (go to related records) from your found set of parent records. Then do a standard list report with the parent record in a subsummary part.

           

          Beverly

          • 2. Re: Multi-column "address book" report
            erolst

            rmittelman wrote:

            This should be easy, I do it all the time in MS Access.

            Then why use FM?

             

            Kidding aside, as Beverly pointed out, portals are not well suited for printing. A report from the child table is one possibility; here's another:

            • 3. Re: Multi-column "address book" report
              rmittelman

              Thanks Beverly.  You're right about portal.  There is no end of problems using that. 

               

              Here are the big challenges I see:

              1: The list of phones/emails should be even with the name/address, vertically speaking.  If I put the name/address in a leading subsummary part, it will come out above the phones/emails, right?  Also, I defined a field in contacts called "Last Letter" which is calculated as Left (Last Name, 1), and I am grouping by that, and it is appearing in a leading subsummary part.  That's how I get the "A" at the top of the section of "A" names, etc.

               

              2: I really want the list on the right-hand side to be both phones and emails for the contact. However, they come from different tables. On my contact detail layout, I have a tab panel showing a Phones tab and an Emails tab.  Each have their own portal to their respective tables.  In retrospect I probably should have had a single detail table with both phones and emails, and a type column so I could still have different portals on the main form, but combined here for the report.  Do you agree?  It's not too late to change my database design.

               

              3: Since there isn't a way to know at design-time how many phone numbers and emails there will be for a given name/address, how can I make the detail section "grow" as needed?

              This was fairly easy in MS Access, as I just created a sub-report of phones and emails, put it in next to the name/address, and set the detail section to "can grow".  I know this isn't pertinent to FMP however.  I'm having troubles making the transition.

               

              Thanks...

              • 4. Re: Multi-column "address book" report
                rmittelman

                @erolst, LOL.  The answer is obvious:  FM has more of a future for me, IMO, and it will do SO many more things SO much easier than MS Access.  That doesn't mean it's an easy transition for me.  In Access I could design a report and embed it in another report as a "sub-report".  Just like a portal, the sub records were filtered by the parent record.  I will look at your attached file.  Thanks...

                • 5. Re: Multi-column "address book" report
                  rmittelman

                  @erolst, now that I look at your attached example, this is EXACTLY what I'm after.  Well, very close.  I need to figure out this whole list thing in FMP.  There's no direct equivalent in Access.  I see you used a list of the communication records in the report layout.  This would be perfect if each name's section wasn't so tall. 

                   

                  In Access there is the concept of "Can Grow" for a section, and for a text control in the section.  You design them quite short and set "Can Grow" to True for both. 

                   

                  Does FMP have anything similar?  You can imagine that the supplied report example looks a bit funny the way it is.

                   

                  Thanks...

                  • 6. Re: Multi-column "address book" report
                    rgordon

                    You do the opposite in FileMaker.  You design then long and shrink them through the Sliding options.

                    • 7. Re: Multi-column "address book" report
                      keywords

                      FM works the other way around. You can use the Sliding & Visibility settings in the Inspector to remove blank space. You will need to experiment with this a bit to see the effect of the various options and how to craft your layout in the first place to make effective use of it.

                      • 8. Re: Multi-column "address book" report
                        beverly

                        ah, I see. then you would have problem. Look at some sort of virtual list. That and/or get the results with ExecuteSQL, but then you may have to "parse" out to make the list look right.

                         

                        I always put such "communication" information into one table and just "flag" as to type (which can be sorted as well. Then a report is not so complex.

                         

                        Beverly

                        • 9. Re: Multi-column "address book" report
                          rmittelman

                          So far, things are *almost* the way I need them to be.  I used your example file extensively, changing my 2 tables into one communications table, and added a Comm Type field.  My Comm Types table is slightly different than your example, as it only has an ID and description (1=Phone, 2=Email).  This works fine.  At first it didn't work well, but after following keywords' suggestion I played with the properties, and all was well with shrinking the fields.

                           

                          For some reason, the leading sub-summary part only displayed the first break, regardless of whether I sorted the records or not.  A little playing around with which field the sub-summary was actually breaking on solved this.

                           

                          Only thing not working now is when I add a phone or email through the portal, the Comm Type (1 or 2) doesn't get filled in.  I can see this by visiting the Communications table in table view.  When I replace the missing field values, all is ok.

                           

                          So the final question is: How do I force the value of that column to be filled in when I add a Communications record through the portal? I don't think I can use OnRecordCommit, as that would refer to the main contacts record, right?  If I'm adding a Communications record through a portal, then the value of the Comm Type column would be determined by which portal I'm creating the new record from, right?

                           

                          Thanks...

                          • 10. Re: Multi-column "address book" report
                            erolst

                            rmittelman wrote:

                            If I'm adding a Communications record through a portal, then the value of the Comm Type column would be determined by which portal I'm creating the new record from, right?

                             

                            Thanks...

                             

                            No. When you add a record via a portal, the foreign key(s) necessary to make that record a related record are set automatically per the relationship definition. Since your filtered portals are based on the same relationship, it doesn't matter in which portal you create them – they won't receive a commTypeID.

                             

                            But you could disallow record creation via portal and write a script to create a new related record, where you pass the commTypeID as a script parameter.

                            • 11. Re: Multi-column "address book" report
                              rmittelman

                              When I said the value of the Comm Type field would be determined by which portal I'm adding the record through, I wasn't referring to any foreign key relationship.  I was referring to a logical condition.  One portal's filter specifies the Comm Type be 1, while the other portal's filter specifies it be 2.  That way, I can display all of the phone records in one portal and all of the email records in another.

                               

                              So there is no way to force a field value before the record is saved?  I tried OnObjectSave to trigger a script, but the script didn't run.  I guess the "object" in the OnObjectSave event isn't a valid object?  I thought it referred to the portal record.

                               

                              What if you want to allow adding records in the portal?  How can I script something that triggers when the new record is added?  I can send a parameter to the script, depending upon which portal the record is created from, then use that parameter to set the proper value for the Comm Type column.

                               

                              This is confusing. I was advised more than once to use a unified Communications table instead of a phones table and an emails table.  So I changed the database structure to do that, and then got the address book report to work properly (thanks again for that sample BTW), but now I can't add a portal record in the portals?

                              • 12. Re: Multi-column "address book" report
                                erolst

                                rmittelman wrote:

                                What if you want to allow adding records in the portal?  How can I script something that triggers when the new record is added?  I can send a parameter to the script, depending upon which portal the record is created from, then use that parameter to set the proper value for the Comm Type column.

                                 

                                You can do it like in the attachment:

                                • 13. Re: Multi-column "address book" report
                                  rmittelman

                                  Thanks @erolst.  Got it from your example.  Needed to tweak a bit, because the Seq field was not being set:

                                   

                                  - Added a self-join to Communications table called CommunicationSelf, ID = ID.

                                  - Edited auto-enter calculation for Seq:  If ( IsEmpty ( Max ( CommunicationSelf::Seq ) ) ; 10 ; Max ( CommunicationSelf::Seq ) + 10 )

                                   

                                  This, combined with your example, allows me to just start typing in the blank row at the bottom of the portal, and adds the new dependent record with the proper Comm Type and Seq.

                                  I was expecting that there was a "record save" event, which obviously there is not.  Putting the trigger on the first field edited solved the issue.  Of course, the script must test for the target field being empty, or it would try to change it anytime a record was edited.

                                   

                                  Thanks !!!

                                  • 14. Re: Multi-column "address book" report
                                    rmittelman

                                    @erolst, everything is working fine except for 1 issue.  Sometimes an item in the compiled communications list is long enough that it wraps to another line.  This makes things look amateurish on the report.  Since the report is defined to use all available horizontal space, I can't just increase the width of the textbox holding that list.  This can be mitigated somewhat by simply making the entire list a smaller font.

                                     

                                    Preferable would be a method to dynamically reduce the font size so if the contents were too long, the font size would be small enough so the longest line would not be wrapped.  I've been googling this, and conditional formatting keeps being mentioned.  However, conditional formatting isn't really dynamic enough.

                                     

                                    I found a custom function to dynamically reduce the font size at http://www.briandunning.com/cf/280.  This requires sending the text, desired font size, width in characters to constrain to, and points to decrement by.  It repetitively reduces the font size until it will fit in the desired width.  Since it is designed for a single text string instead of a list of strings, I also found a function to determine which string in the list is the longest, and return its length at https://www.briandunning.com/cf/1244.

                                     

                                    I haven't tried either of these yet, not the least of which reason being I have no idea how to use custom functions.  I've also seen that scripts seem to be preferred to custom functions in general.

                                    Scripting this would require looping through the list items and flagging the longest.  That doesn't seem too difficult.  To me the biggest challenge would be calculating the font size required to fit that longest line of text in a known width. 

                                     

                                    Needless to say, a fixed-width font is most likely key here.  In Access VBA, there are ways to send a text string (even in a variable width font) and get it's length using Windows API's.  I have no idea of how to do this in FMP.  I have no problem using fixed-width and only constraining to number of characters limit in desired font size (IOW, I want 12-point, and can handle 50 characters at that size. So if I have 60 characters, what font size do I need to make it fit?).

                                     

                                    Any ideas how to do this, or do I need a new thread?

                                     

                                    Thanks...

                                    1 2 Previous Next