5 Replies Latest reply on Oct 27, 2011 3:23 PM by philmodjunk

    Help with Report - merge fields with two or more related records

    KristenConley

      Title

      Help with Report - merge fields with two or more related records

      Post

      Hello,

      I am new to FileMaker and though I have read a lot of the posts here, I still haven't been able to solve my issue.  I am using FMPro 11.0v4 on a Mac.  I work for an entertainment company that sends out various entertainment for parties (such as disc jockey and karaoke, or disc jockey and novelty photos. I have set up our database in the following manner:

      Client ----<Events---<EventEntertainment---<EntertainmentProducts

      My issue comes with how to create our contract. We were previously using ACT which allowed me to "write" a word document that would use merge fields to input the information but then I could edit it as needed (which created its own problem of our database not being accurate).  So, with our new FileMaker database, I have a layout for contracts and have all the info inputted via merge fields. My problem comes when I have an event that has more than one related EventEntertainment record. It will only show the first one listed.

      This is how I currently have the "Contract" layout (from the event table):

      We will provide <<EventEntertainment:Number of Hours>> of <<EventEntertainment:Product Name>> for a total price of <<EventEntertainment:Rate>> during the hours of <<EventEntertainment:StartTime>> to <<EventEntertainment:EndTime>> on <<Event Date>>

      I would like this to repeat on the contract for each entertainment product selected for that event.  If I switch the contract layout to pull info from the EventEntertainment Table then it gives me a seperate contract for each entertainment product selected. I would like it to all be contained on one contract. Basically, like an invoice with line items but much more text!

       

      I hope I've provided all of the needed information. Any help would be appreciated.

        • 1. Re: Help with Report - merge fields with two or more related records
          philmodjunk

          Assuming that you do not need to list EntertainmentProducts on this layout, base your layout on EventEntertainment and then include the fields needed from the other related tables. If you need to list EntertainmentProducts records base the layout on that layout.

          • 2. Re: Help with Report - merge fields with two or more related records
            KristenConley

            Thanks for your help!

            I have switched the layout to be based on EventEntertainment but my merge fields still only show the information from the first record. It does create a seperate contract for each of the related records which would work but is not ideal. It would be better if it could be all on one contract.  Is there any way to do what I want it to do?

            I've thought about reworking the contract completely so that I could just use fields instead of the merged information but my boss does not like that solution. If that is the only solution to the problem, then i suppose he will have to deal with it.

            • 3. Re: Help with Report - merge fields with two or more related records
              philmodjunk

              By using a layout base on EventEntertainment, the only related records shown should be from Client and Events, and there should only be one such related record from each.

              I think you need to describe the design of your layout in greater detail. Note that you can upload a screen shot of your layout by using the controls located below "Post A Answer".

              We may need to restructure the layout parts so that the "multiple items" part of your layout is the only part in the layout body and you then have "list view" specified for your layout in order to get the multiple items listed.

              You may also need to return to the original approach but use a portal or a calculation field that uses the List function in order to get what you want. It depends on how you need to set up your report here.

              • 4. Re: Help with Report - merge fields with two or more related records
                KristenConley

                Thank you!!  You hit the nail on the head - i think (haven't tried it yet!). 

                Basically, each event can have multiple entertainment products but I would like them to all be listed on one contract. I hadn't thought of doing a list view so I definitely want to give that a try.

                 

                I'm in way over my head with this - so I definitely appreciate the help!

                • 5. Re: Help with Report - merge fields with two or more related records
                  philmodjunk

                  You may find this demo file created by Comment helpful:  http://fmforums.com/forum/showpost.php?post/309136/

                  It's an invoicing system, but I think you can spot the similarities. Notice how the individual items entered in the portal can be printed from a list view layout based on the portal's table. If you can put most of your contract text in  leading and trailing grand summary parts, you can reserve teh body layout part as the place to list the individual itiems.