locked
Send Email With Data From Two Entities With a One To Many Relationship When Records Inserted RRS feed

  • Question

  • I can send email with data from a single entity when record inserted no problem, but I have two entities with a one to many relationship. Entities are, Plans (one) PlanDetails (many).

    I can email Plans and PlanDetails separately, except I get a separate email for every record inserted in the PlanDetails (many) entity.

    Does anybody have any idea how I can go about this? Send all the records inserted into both entities in one email.

    My existing email code is something like that below.   

     partial void Plans_Inserted(Plan entity) {  string strMsgBody = "Block: " + entity.Block1.ToString() + Environment.NewLine + Environment.NewLine;            strMsgBody = strMsgBody + "Group: " + entity.Group1.ToString() + Environment.NewLine + Environment.NewLine;            strMsgBody = strMsgBody + "Place: " + entity.Place1.ToString() + Environment.NewLine + Environment.NewLine;            MailMessage msg = new MailMessage("Plan@email.com", "diddlydo@thisplace.org");            msg.Subject = "New Plan";            msg.Body = strMsgBody;            SmtpClient SmtpServer = new SmtpClient("localhost");            SmtpServer.Send(msg);        }

    Wednesday, March 13, 2013 2:55 PM

Answers

  • Beth Massi's post has some tips on using query results in emails.  You should only need to query against the PlanDetails table since each PlanDetail has a Plan property.  Alternatively, you could get all the PlanDetails for a given Plan by using the navigation property, but that wont help if the associated details have not been inserted yet.  There are a lot of ways to do this depending on your workflow.  If many Plans and PlanDetails could be entered in one screen (unit of work), you could get the EntityChangeSet and do your email commanding based on that.

    • Proposed as answer by Yann DuranModerator Thursday, March 14, 2013 5:34 AM
    • Marked as answer by Angie Xu Friday, March 29, 2013 7:11 AM
    Thursday, March 14, 2013 12:59 AM

All replies

  • Beth Massi's post has some tips on using query results in emails.  You should only need to query against the PlanDetails table since each PlanDetail has a Plan property.  Alternatively, you could get all the PlanDetails for a given Plan by using the navigation property, but that wont help if the associated details have not been inserted yet.  There are a lot of ways to do this depending on your workflow.  If many Plans and PlanDetails could be entered in one screen (unit of work), you could get the EntityChangeSet and do your email commanding based on that.

    • Proposed as answer by Yann DuranModerator Thursday, March 14, 2013 5:34 AM
    • Marked as answer by Angie Xu Friday, March 29, 2013 7:11 AM
    Thursday, March 14, 2013 12:59 AM
  • Hi Yann, thank you for your response, as you suggest I can indeed get the Plan records from the PlanDetails query, thank you very much for that.

    My problem now is getting all the PlanDetails records into one email and not a separate email for every record ... not good!

    I do intend to have one screen for entering records for both Plans and PlanDetails. You mentioned using the EntityChangeSet to collect all the records, do you know of any examples of using it in this manner?

    I have seen the Beth Massi's post, she is using VB XML Literals which are not available to me as I'm working in C#

    Friday, March 15, 2013 1:59 PM
  • When I received the email notification for this thread, I thought to myself "I don't remember sending that reply". Now I see why, lol. I proposed the reply as the answer to your question, but I didn't write it, Hessc wrote it.

    If you need to combine a series of record's details into the one email, you'll need to do that in the screen's Saving method, not in the entity's Inserting method.

    So in the Saving method, you loop around the datagrid's collection, composing the email however you want, then fire off an email using the collected information.


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    Friday, March 15, 2013 2:11 PM
    Moderator
  • LOL .... guess I have to thank Hessc :-)

    I'm a bit of a LightSwitch newbie don't be surprised if I ask some really dumb questions lol.

    Ok, am I right in saying if the records are collected via the screen's saving method, that is client side and I'd need to pass the details to the server project to send the email as System.Net.Mail isn't available on the client?

    I now have it sending all the details in one email, it's just it sends an identical email for every PlanDetail record inserted lol. I'll just stick in my code so others having this issue can see the wrong way to do it!

                   partial void PlanDetails_Inserted(PlanDetail entity)
                  {
                      string strMsgBody = "Block: " + entity.Plan1.Block1.ToString() + Environment.NewLine + Environment.NewLine;
                      strMsgBody = strMsgBody + "group: " + entity.Plan1.Group1.ToString() + Environment.NewLine + Environment.NewLine;
                      strMsgBody = strMsgBody + "Place: " + entity.Plan1.Place1.ToString() + Environment.NewLine + Environment.NewLine;

                      string strDetails = "";

                      foreach (PlanDetail p in entity.Plan1.PlanDetails)
                      {
                          strDetails = strDetails + "Shift " + p.Shift1.ToString() + " - " + p.TType1.ToString() + " - " + p.Notes.ToString() + Environment.NewLine + Environment.NewLine;
                      }

                      strMsgBody = strMsgBody + strDetails;

                      MailHelper mailHelper = new MailHelper(
                              "blah@blah.com",
                              "blah@blah.com",
                              "Name",
                              "New Entry",
                             strMsgBody);

                      mailHelper.SendMail();
                   }

    Friday, March 15, 2013 2:50 PM
  • "I'm a bit of a LightSwitch newbie don't be surprised if I ask some really dumb questions lol"

    Don't worry, the only dumb question is one that doesn't get asked IMO. Many of us are here to answer questions, simple or more complicated. And we all started at the beginning, with regard to LightSwitch, so we were where you find yourself now. :-)

    I feel like I've answered this particular question in another thread???

    You can't accomplish what you're trying to do (send a single email) by using the Inserting method. You need to use the screen's Saving method to put the email text together, then something like the command table pattern to trigger off the firing of the single email.

    If you need more info on this, just ask for clarification. We'll try to help.


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    Saturday, March 16, 2013 3:00 PM
    Moderator
  • Hiya Yann, thanks for taking the trouble to help me out here, much appreciated ... Where do I find my self with LightSwitch now? I think it's pretty cool, but could probably have written this whole application another way in less time than it's taken to figure out this email thing ... LOL.

    Yup I totally totally absolutely 100% get that this cannot be done using the inserting method.

    Ok .....  I've spent most of today so far investigating using the screen's Saving method, but cannot seem to reference any of the properties I need to grab the value of to build the email text, and can't find any example code of this being done anywhere.

            partial void PlansListDetail_Saving(ref bool handled)
            {
    ?????????????????????????????????????????????
            }

    So, my next question is, using the screen's Saving method, how can I reference the controls on the screen to get the values from them to put the email text together?

    Monday, March 18, 2013 4:36 PM
  • For this sort of job you wouldn't reference the controls on the screen. You would reference the collections, entities and queries of the screen - the things in the bar on the left in the screen designer.

    this is the screen and this.Plan (or something like it) will be the Plan that is currently on screen. this.Plan.Details (or something like it) will be the collections of Details for the plan that is currently on screen. The actual names of objects and collections will depend on what you have called them.


    Simon Jones
    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

    Wednesday, March 20, 2013 10:58 AM
  • Thanks for that Simon, got it now.

    I can reference the collections and grab what I need using a foreach in the _Saving method

                foreach (Plan p in Plans)
                {
                    strBlock = p.Block1.ToString();
    ......etc. etc.
                }

    And for the related record thus

    foreach (PlanDetail pd in PlanDetails)
    {
    strDetails = strDetails + "Shift: " + pd.Shift1.ToString();
    }

    So far so good, I can now build and send the email with the required records using the command table pattern as suggested by Yann (much thanks).... but there is a snag or two.

    When records are deleted from the screen it also sends an email via the Saving method which is not required, and when more than one record is inserted in one save, only one email is sent containing the the last record.

    Bit more to do methinks

    Wednesday, March 20, 2013 12:23 PM
  • p.Details.EntityState or pd.Details.EntityState will tell you if the record is Added, Deleted, Unchanged, etc. You can then ignore the Deleted ones as they will be removed from the database when the data is saved.

    Simon Jones
    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

    Wednesday, March 20, 2013 12:29 PM
  • As Simon says (no pun intended), you don't access the screen controls, you access the properties that the controls are bound to.

    So if you have a string property called say EmailText, that's bound to a TextBox on the screen, your code would look something like this:

    partial void PlansListDetail_Saving(ref bool handled)
    {
        string details = "";
        foreach (PlanDetail p in this.PlanDetails)
        {
            strDetails += "Shift " 
                + p.Shift.ToString() 
                + " - " 
                + p.Type.ToString() 
                + " - " 
                + p.Notes.ToString() 
                + Environment.NewLine 
                + Environment.NewLine;
        }
        this.EmailText = details;
        handled = true;
    }
    Does that help?


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    Wednesday, March 20, 2013 1:31 PM
    Moderator
  • Thanks Simon, just the job ... getting there :-)
    Wednesday, March 20, 2013 3:52 PM
  • Thanks guys, pretty much there now and emails being sent with all required info, your help is much appreciated, thank you so much for taking the time and trouble  to help others out, very cool :-)

    I can get all the records from the PlanDetails collection so only one foreach required to collect all records.

    Basic working code at the moment something like this.

    For List / Details screen Saving method ...... 

     partial void PlansListDetail_Saving(ref bool handled)
            {
                string strDetails = "";
                string strMsgBody = "";
                string strBlock = "";
                string strGroup = "";
                string strPlace = "";
                string strPDEState = "";
    
                foreach (PlanDetail pd in PlanDetails)
                {
                    // Get the entity state Added Deleted Modified etc.
                    strPDEState = pd.Details.EntityState.ToString();
    
                    //Set variables from Plan collection
                    strBlock = pd.Plan1.Block1.ToString().Trim();
                    strGroup = pd.Plan1.Group1.ToString().Trim();
                    strPlace = pd.Plan1.Place1.ToString().Trim();
    
                    //Set variables from PlanDetails collection and build email text
                    // Notes are not required but can't pass null value
                    if (pd.Notes != null)
                    {
                        strDetails += "Shift: " + pd.Shift1.ToString().Trim() + Environment.NewLine + "Training Type: " + pd.TrainingType1.ToString().Trim() + Environment.NewLine + "Notes: " + pd.Notes.ToString().Trim() + Environment.NewLine + Environment.NewLine;
                    }
                    else
                    {
                        strDetails += "Shift: " + pd.Shift1.ToString().Trim() + Environment.NewLine + "Training Type: " + pd.TrainingType1.ToString().Trim() + Environment.NewLine + Environment.NewLine;
                    }
                }
    
                //Build email text from Plan collection
                strMsgBody = "Block: " + strBlock + Environment.NewLine + Environment.NewLine;
                strMsgBody += "Group: " + strGroup + Environment.NewLine + Environment.NewLine;
                strMsgBody += "Place: " + strPlace + Environment.NewLine + Environment.NewLine;
    
                //Join email text for both collections
                strMsgBody += strDetails;
    
                if (strPDEState == "Added")
                {
                    var rs = this.Application.CreateDataWorkspace();
                    Email command = rs.PlansData.Emails.AddNew();
                    command.Verb = "CreateEmail";
                    command.EmailMsg = strMsgBody;
                    rs.PlansData.SaveChanges();
                }
            }

    I'm using a table called Email for inserted method with columns Verb and EmailMsg.

    Emails_Inserted code something like this .... 

                   partial void Emails_Inserted(Email entity)
                   {
                       switch (entity.Verb)
                       {
                           case "CreateEmail":
                               MailMessage msg = new MailMessage("blah@blah.org", "blah@blah.org");
                               msg.Subject = "New Plan";
                               msg.Body = entity.EmailMsg;
                               SmtpClient SmtpServer = new SmtpClient("localhost");
                               SmtpServer.Send(msg);
                               break;
    
                           default:
                               break;
                       }
                   }

    Hope that helps somebody else with similar requirements.

    Thursday, March 21, 2013 10:15 AM
  • I'm glad you're getting on but I've noticed something about your code that may not be quite right.

    You define strPDEState outside the foreach loop, set it inside the loop and test it after the end of the loop. This means that your email will be sent if the last plandetails row in the collection was Added and it will contain all PlanDetails, whether or not their records were Added, Deleted or Unchanged.

    This may or may not be what you want but it doesn't look right to me. I'd expect you to be testing the EntityState of the objects inside the loop and deciding whether or not to include them in the email there.


    Simon Jones
    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

    Thursday, March 21, 2013 10:26 AM
  • You're welcome! And well done coming up with your code, & sharing it here for others.

    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    Thursday, March 21, 2013 2:36 PM
    Moderator
  • You are absolutely spot on Simon thank you for pointing it out, the EntityState of objects would indeed be better tested within the loop and decision made there to include the row or not.

    I'm not yet sure what the exact requirements are, e.g. if an email is required on modification, or if an email is required if a record is entered with no related records etc. It may be they'd rather have a button to send email so they can decide for themselves whether on not to send one. For this particular app I can't think they would want anything other than all rows ... but hey who knows .... been wrong before and no doubt will be again!

    Have a few options to discuss re the email requirement before I get a definite specification sorted out, likely next week at some point, what I have just now should be ok just to demo but probably not final .. oh definitely not final .... lol

    Thursday, March 21, 2013 2:58 PM
  • No problems coming up with my code Yann if it helps other that's great, where would we all be if nobody was willing to share their stuff?
    Friday, March 22, 2013 10:05 AM
  • I've just been finishing this part of my app off as I now know the conditions under which email should and should not be sent and found an interesting thing.

    I'm catching the entity state using Details.EntityState but it never returns the entity state as being "Deleted", only ever Added, Modified or Unchanged. Yes I am deleting when checking the state!

    I would have liked to catch the "Deleted" state for my logic although probably not essential, just wondering if anyone has come across a similar problem?

    Friday, April 12, 2013 1:27 PM
  • EntityState.Deleted only shows when you delete a record that was previously saved.

    If you add a record and then delete it without having saved it first, the record is just removed from memory. There's no point trying to delete it from the database as it never existed there.

    LightSwitch's grid control shows a cross in the row selector when a row's EntityState is Deleted. This shows it will be deleted when you save the data. If you delete an unsaved row, it just disappears.

    If this doesn't explain what you're seeing, you probably need to post your current code so we can have a look at it.


    Simon Jones
    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

    Friday, April 12, 2013 4:05 PM
  • Hi Simon, thanks for yor comments. It isn't the behaviour I'm seeing, I am deleteing saved records.

    My code for testing the entity state is below, very simple and sets a boolean depending on state. If I include a "ShowMessageBox" within the loop so I can see the entity state returned for each record it never shows "Deleted" only Added, Modified, or Unchanged. 

    foreach (Plan p in Plans)
    {
    // Test the entity state of the Plans collection records
    // Set send email boolean to true if entity state of any record added or modified
    
    if (p.Details.EntityState == EntityState.Added || p.Details.EntityState == EntityState.Modified)
        {
          blnPSendEmail = true;
        }
     }

    Monday, April 15, 2013 2:22 PM
  • In that case, you might have fallen into this bear trap.

    The EntityStates Enumeration has the FlagsAttribute which means it allows a bitwise combination of its member values. The values are just combined using OR (addition).

    Detached = 1
    Unchanged = 2
    Added = 4
    Deleted = 8
    Modified =16

    This means an Entity could be both Modified and Deleted (8+16= 24) so comparison with a simple test for equality can fail. If this is the case, you have to AND with the value you are testing for and see if you get the same value back.

    If p.Details.EntityState And EntityState.Deleted = EntityState.Deleted Then

    24 (Modified OR Deleted) AND 8 (Deleted) = 8 (Deleted)

    If the EntityState did not flag Deleted you would get zero, not 8.

    16 (Modified) AND 8 (Deleted) = 0

    I've not seen this effect in practice in LightSwitch but it is theoretically possible and so should be coded for.


    Simon Jones
    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

    Monday, April 15, 2013 2:51 PM
  • Thank you kindly Simon,

    That indeed would explain the behaviour yes, not tried it out yet as working on something else today, but will try to do so soon and point out if results differ from expected.

    Monday, April 15, 2013 3:37 PM