7 Replies Latest reply on Apr 24, 2014 10:13 AM by FentonJones

    Sending Letter to Multiple Records



      Sending Letter to Multiple Records



           I was wondering if I could be pointed in the right direction. 

           I have a database with various reports.  What I would like to do is when someone runs a particular report to be able to select multiple recipients to send letters to. 

           I have placed a tick box on the report which when I tick it simply places a "1" in the field.  My thought was that you run the report, select which of the found records you want to send a letter to then select the letter.  I have a global field that shows a value list with all the letter titles (I have a letter template table), the user would tick the records from the report, select the letter from the drop down and it would go to the layout finding that letter.  Where my idea fails me, is I'm not sure how do I get it to find those I ticked.  Does the button running the script to go to the letter have to run the original find from the report all over again with a loop to look for the records with the "1" in that tick box field?  Then I was gonna add a button that when they close the letter it clears the tick box field for all those records.  Am I on the right path or is there an easier / better way?  I'm going to need to do this in multiple different areas of the database so I want to get it right. 

           Thanks in advance for any help/advice offered.

        • 1. Re: Sending Letter to Multiple Records

               By "letters" do you mean email or do you want to print a copy of the letter for snail mail, but with salutations and address specific to that recipient?

               Depending on what you intend here (email or snail mail), the details will differ slightly, but a relationship from the table that stores the letters can match to all "ticked" recipient records.

               But how you "tick" a record could also be a problem if it is even remotely possible that you might have two or more users doing this at the same time. (You don't want their selections to become part of your list of recipients and vice versa.) There are ways to avoid that possible complication if such is needed for your system.

          • 2. Re: Sending Letter to Multiple Records


                 I would agree with Phil that the "tick" into a regular field is problematic. It is a regular record field, for all users, but you're really trying to use it for only one user, doing one report. 


                 Why not create a table for "reports sent"? That way you can create a record for what is going to happen,* with the id of the person to get it, the id of the report to send, and the date created. That way all the data needed is available, without having to even change the records of the main table (so also does not change its "modified" field(s)). 


                 Such a "reports sent" can be tested to see if the person has already got that report, when, etc., so any user running the script can be warned (if desired), and such people would not get another report (or new record of it; unless you want to).


                 As far as choosing "who sent to" it seems that it would be done into a global field. You'd just need a layout method to click on "who", to add someone to the "list of people ids/names" in the global field. And show them, to see who's already chosen. I think the easiest method would be 2 portals, one with the people (sorted by name) and a second with the people chosen (also sorted by name). The real guts behind the scene could be their ids. The result could be they're IDs (as that is what you'd want for the "report sent" table records).


                 Likely Phil can explain this better. There are options on what to do. But I think another "simple" table to store the ids and date is the way I'd do (have done) it.


                 * If you're really going to "print" this report, then you could create all the records of the "report sent" AFTER the "print" (in case something goes wrong with it). Personally, I'd use email, with a method to send the report (there are several method; any of which is more reliable than "print", in my opinion).

            • 3. Re: Sending Letter to Multiple Records



                   Thanks for the info.  I do not want to create a table for reports sent as there are loads of users of the database who would generate many, many various reports throughout the day.  The information may vary throughout the day/daily and they could reference / re-run it multiple times in a day, to create a record every time they run the report would just create a massive table.  Unless I'm misunderstanding something.  John Doe may be on a list I need for a group waiting list, but then he also may be on the list for another person as actively getting another service, etc.  So they can be in more than one place.  Also, as I explained, this is not to print the report itself, its to run a report, get client names that fit the particular find criteria of that report, then send various letters to a selected group those clients.  Emailing is not an option for this.


              • 4. Re: Sending Letter to Multiple Records

                As I see (guess), you have a "report," then you want to choose multiple "recipients" who to send a "letter" to. You seem to have covered those (and likely have IDs for each). The problem is more how to handle choosing the "recipients", one at a time, after running the "report." [ It would be easy if the "recipients" were already known (saved in a Variable); but we humans prefer to do the report first, then choose those to send to.]

                So, the "report id" was be saved somehow somewhere, and the list of "recipients" built up. [ I am not entirely sure what the "report" has to do with the "letter", but may not need to know.].

                Sense the entire operation is for that user only, and you do not want another table, then something global is needed, either a $$Variable or a global field (text). Either would work, but only a field could show chooses in a portal, and support a relationship (which would make it easier).

                You could them choose the "recipients", and each choose ID would be added to a list in that global field. When you are done, the list could be used to go to the chosen "recipients" records. A relationship from the global list of "recipient" IDs could go easily to the records.

                I'm sure there's a few other tweaks needed, like clearing the globals first, before starting; and I'm not sure how exactly you'd "choose" the "recipients". They would need a [+] button to add each to global list to "send the letter", and possibly a view of "chosen ones" (this could be shown in a portal on the Header, as it's seen via a global field).

                [ The reasons I do not like "tick" fields in tables: 1. All users see it,* 2. Must be set then cleared (takes time), 3. Changes cause any "date/time modified" field to get triggered, messing with you're ability to see which records has actually been changed.

                * I have used "mark" fields in a table, as it is more of a real change. It is possible to make such work for each user, via their log-in. But even "mark" fields can be done via a global (until quit), or another "mini" table; in order to not mess with the "modified" fields (which can be important if needed).]



                • 5. Re: Sending Letter to Multiple Records

                       A more complete description of your tables and relationships would help Fenton and others be more specific in their suggestions.

                       The "tick" selection method that I use in a mult-user database is to place buttons on the layout to click to select that record (such as the record for a particular recipient). The script then either creates a related record in another table linked to both the "letter" record and the "recipient" record or it builds a return separated list of ID's for the selected recipients in a global field or variable. I set up the script so that clicking the button once adds a record or adds an ID to the list, clicking it a second time removes it from the list or related table--the same action you get from a check box but in a way that makes it easy to keep each users' list of selected ID's separate from each other. Conditional formatting can then be used to make the button look and act just like the check box field that it is simulating as such conditional formatting can make a character--such as the letter x, appear and disappear to show whether a particular recipient record has or has not been selected by you.

                  • 6. Re: Sending Letter to Multiple Records

                         Thank you both very much for all your advice and tips, I really appreciate it.  And I'm sorry if I haven't fully explained the setup or need of the database.  It's just so detailed at this point I thought explaining every relationship / how it all works / why reports are run and what they return would only get totally confusing without looking at it. I will look more into your suggestions of additional tables and see how to get it to work.  I would have never have thought about some of the issues you both brought to my attention. 

                         Again, thanks for taking the time to help!! 

                    • 7. Re: Sending Letter to Multiple Records


                           One thing I forgot to mention, if you used a new table to store the "letter to send to" is that it would also require the staff person who is doing it, which means each would need a unike log-in account name; so each staff could see their stuff. I meant to add this to the IDs needed, but didn't.
                           After all you said about what they actually were doing, i.e., the large number needed, and the little history needed, I then wrote about doing all this without a new table. That is, in some ways easier. It seems the problem is, as I said, the "choosing multiple recipients to get the letter". Phil wrote about a method, with "builds a return separated list of ID's for the selected recipients in a global field or variable."
                           So all data needed can be held in global fields (or variables, but a global is needed if a portal is used to show the "recipients to send letter"). The only real problem left (I think) is how exactly to do two things: 1. See/or/Find the recipients, and 2. See the ones chosen.
                           Two portals, side by side, sounds nice. However, the problem with that is "how many recipients"? A lot makes the portal to pick them a bit awkward; especially if you want a sort of "free form" Find, using more than just going thru a list of names. The most freedom is a regular list. The "recipients chosen" could be shown on the Header. Though the "recipients chosen" would just be global text field, with each chosen being just the ID, it can be used as the "from" field or a relationship, to show the "recipients names", sorted by name.
                           Since it is based on a global field, it could be set from any layout of the "recipients" data; from any window also (though you'd likely need to commit to see changes). It is also possible to show Phil's idea of a checkbox, on the (special) "recipients" layout (even though it would be based on the relationship "from" on the global field to "recipients" table occurrence). [ However that's not really critical, as you'd see them in the "recipients chosen" already, so you'd know which they were. Another tip I got a while ago; you can use this calculation: Char(10003), to show a "checkbox" (no image needed).]
                           So, it's all doable, on one table, on the fly. I'm sorry I even mentioned "another table". But it will need a special layout for the "choose recipients" (for the button to add them, which you do not want on the regular list), and/or one to show the "chosen recipients" (which would also tell them any other data needed (which could also be in a global field, if best).
                           P.S. I'm not always real clear on what to do, partly as I'm kind of old, am retired, and had a "brain injury", which makes it harder to write long lists of information clearly; though I still can (more or less) figure them out.