6 Replies Latest reply on Jun 18, 2015 10:33 AM by NancyatUCM

    How to deal with label formats for Individuals, Couples w/ Same Last Name and Couples w/ Different...

    NancyatUCM

      Title

      How to deal with label formats for Individuals, Couples w/ Same Last Name and Couples w/ Different Last Names?

      Post

      Three fields in our database are: individuals, couples w/ same last name, and couples with different last names, (so for example, we find for “Individual=Yes” to get that group). When we do a large merge letter mailing, we have to do a separate merge for each of these categories so that the addresses/salutations read correctly, and 3 more separate merges for the mailing labels for those categories. (ie. We have 3 different letter formats and 3 mailing label formats). When we start finding for additional criteria (donor vs. non donor, etc) it can become a time-consuming large number of merges.

      I am wondering if there is a way to do this more efficiently.  Is this is something we would use a script for? (I haven’t really worked w/ scripts yet, so please be thorough in any instructions!). Would we need to worry about the merges taking a really long time if we ran a script?  This must be a common problem and I’m wondering how others have solved it.

      Here are some of our formats: (sorry screenshot cut off part of some words - for couples different it is: Title, FirstName, LastName and SpouseFirstName SpouseLastName)

      Merge_Letter_Formats.png

        • 1. Re: How to deal with label formats for Individuals, Couples w/ Same Last Name and Couples w/ Different...
          SteveMartino

          Without knowing too much about your database, you could create or modify a few fields, and use List function and a Case statement to first figure out which type of salutation to use, and which kind of label to create.  This will be an unstored calc, and could be slow depending on how many letters you need to produce.  I did this for my letters to differentiate between people and companies, and to account for missing fields (address 2, or Attn: fields.)

          I'll dig it out and edit this post, unless someone gives you an answer sooner.

          • 2. Re: How to deal with label formats for Individuals, Couples w/ Same Last Name and Couples w/ Different...
            SteveMartino

            Always test on duplicate/backup layout. Make a new field called Label_field.  Make it a large merge field of the Label_field.

            For your label field:

            Let ([

                    @type= Table::Type_field;

                    @Line1=

                Case(@type="Individual"; title_field & " " & FirstName_field & " " & LastName_field;

                          @type="CoupleSameLastName"; title_field & " " & FirstName_field & " and " & SpouseFirst_field & " " & LastName_field ;

                          @type="CoupleDiffLastName"; title_field & " " & FirstName_field & " " & LastName_field & " and " & SpouseFirst_field & " " & SpouseLast_field) //end case

            ]; //end variables

            List (@Line1; Address1; Address2; City & ", " & State & "  " & zip)

            )

            You have to substitute all your field names for mine.  You can even do this in the data viewer and give full Table::field references and hit evaluate, and you should see the proper result, before committing it to your layouts.

                   

            • 3. Re: How to deal with label formats for Individuals, Couples w/ Same Last Name and Couples w/ Different...
              philmodjunk

              This will be faster if you can make it a stored calculation. That is something that seems very possible unless you have to reference data from another table in the above calculation.

              • 4. Re: How to deal with label formats for Individuals, Couples w/ Same Last Name and Couples w/ Different...
                SteveMartino

                @ Phil, I used auto enter calcs, to forgo the unstored calcs.

                @OP I don't know how your DB is structured.  I also wouldn't do it in this simple manner.  It's just a basic idea of some auto-enter calcs, including the one in the 'Type' field so a user cant select more then one radio button by shift+clicking (courtesy of the gifted Raybaudi).

                Here's a quick demo from FM12.  Don't forget to 'Extract All'

                https://dl.dropboxusercontent.com/u/71328624/LabelDemo.zip

                I do have a full letter writing component to my DB that builds the letters individually off of a template, so I can go thru and customize them.  Then after printing, puts a record in a portal (Customer_contact) showing which letters were sent on which dates, and gives you the option to save as a pdf (for collection letters) or not (general newsletters, etc.).  Unfortunately, the bulk of this component to my DB was purchased thru training series videos, mostly complete with layouts,scripts and all.  I never post other peoples work, that they charge for/sell, without their permission. It's here under FM Pro 12 bundle vtc.com/

                • 5. Re: How to deal with label formats for Individuals, Couples w/ Same Last Name and Couples w/ Different...
                  philmodjunk

                  But if the fields are all from the same record, it can just be a stored calc. Note that with an unstored calc that references fields from other records as may be the case, the auto-entered calc will not automatically update when you modify a value in the other record. Thus an unstored calc may be the best option unless you have really large numbers of records in the tables where this data is stored.

                  • 6. Re: How to deal with label formats for Individuals, Couples w/ Same Last Name and Couples w/ Different...
                    NancyatUCM

                    Thank you so much for all this information.  Unfortunately, I don't really understand the answers! Database work is only a small fraction of my job, so I am a pretty basic user.  What surprises me is that this is an issue almost every database user would face, so I would expect it to be part of one of the templates, or that somehow a simple solution would be built into the software. I'd say overall, that is my main disappointment with FileMakerPro.  I have a sense that it is capable of all sorts of things, but setting them up requires specialized knowledge, as opposed to more of a 'point and click' interface.

                    Looking at how long it seems it would take to learn how to do this and set it up, I think it makes more sense to stick with the current way we do things.  Thanks though - at least I know I'm not missing a really obvious solution!