AnsweredAssumed Answered

Adding Vars to Email Content

Question asked by cynthiablue on Sep 11, 2017
Latest reply on Sep 12, 2017 by cynthiablue

Continuing on with my Dog Show application... now what I'm trying to do is send an email confirmation to the handler that includes all the dogs that the handler has entered in the trial and send them all in one email (I can easily send one email for each dog, but who wants four emails if they have four dogs entered? Not me.)

 

I have a script that will find the dogs that I want (this works fine).

 

I need to loop through my Dog table to find all the dogs, and related fields, that match the criteria I want, then I want to email this information to the handler.  I don't know how many dogs the handler will have entered. So I'm thinking that I need a variable to sick into the Message part of the "Send Mail" Option box.

 

Since the dogs are not in any kind of order in the table, I need to collect the dog information, go read the next field, see if it's a dog I want, if so, collect the dog information, and possibly concatenate the 2nd dog information onto the variable that is already holding the 1st dog information.

 

This is hard to explain. I can provide the text of my script if that would be easier. 

 

I'm not sure how to concatenate the text of the second (and maybe third or fourth etc) variables onto the first variable.

Or, is there a better way to do this?

 

I'm using the Repetition option in my Set Variable dialog to get each dog out of the table, since I don't know how many there are. I opted for this method because I wasn't sure how to use Dynamic Variables even after reading somewhat about them.  Plus, I didn't really think they were needed.

 

Here is my script if anyone would be so kind as to take the time to have a look and let me know what would work.  I'm sure it's not the most efficient script in the world, and I've commented out the parts that retrieve the Runs because I don't need that while I'm trying to just figure out how to do the dogs portion and put it into the email.

 

Thanks!

 

  • Set Variable [ $count; Value:1 ]
  • Set Variable [ $countDogInSet; Value:1 ]
  • Set Variable [ $countRuns; Value:1 ]
  • Set Variable [ $countEmail; Value:1 ]
  • Set Variable [ $PersonName; Value:People::cFullName ]
  • # Get the Result Set that contains all the IDs for the dogs the person owns.
  • Set Variable [ $SetOfAllDogs; Value:ExecuteSQL ( " SELECT \"__pkdog\" FROM \"Dog\" WHERE \"__fkPeople\" = ? "; "" ; "" ; People::__pkPeople ) ]
  • #Use GetValue to parse the Result Set in the SetOfAllDogs Variable.
  • Set Variable [ $GetNextDogFromSet; Value:GetValue ( $SetOfAllDogs ; $count ) ]
  • Go to Layout [ “Dog-INVISIBLE-GetEmailInfo” (Dog) ]
  • Set Variable [ $NumOfDogsInDogTable; Value:Get ( TotalRecordCount ) ]
  • Go to Record/Request/Page [ First ]
  • Pause/Resume Script [ Indefinitely ]
  • Loop
  • If [ Dog::__pkdog=$GetNextDogFromSet ]
  • #Make sure the dog has Runs in the Run Table. If not, don't include in the email.
  • If [ Run::__fkDog=$GetNextDogFromSet ]
  • # Go to the Dogs layout so the Dog information can be gathered.
  • Go to Layout [ “Dog-INVISIBLE-GetEmailInfo” (Dog) ]
  • Set Variable [ $DogForEmail[$count]; Value:"Arm Band #" & Dog::ArmbandNum & " — " & Dog::CallName & " -- " & Dog::PrefixTitles & " " & Dog::RegisteredName & " " & Dog::SuffixTitles & " -- " & "UKC # " & Dog::UKCNum & "; " & Dog::BirthDate & "; " & Dog::Gender & " -- Sire: " & Dog::Sire & "; Dam: " & Dog::Dam & "; " & "Breeder: " & Dog::Breeder ]
  • // Pause/Resume Script [ Indefinitely ]
  • #Go to the Runs layout so the Runs information can be gathered.
  • // Go to Layout [ “Run-INVISIBLE-GetRunInfo” (Run) ]
  • // Go to Record/Request/Page [ First ]
  • #Dog is in the Set and Runs are in the Current Event
  • // If [ Run::__fkDog = $GetNextDogFromSet and Run::__fkEvent = CurrentEvent::__pkCurrentEvent ]
  • // # Loop through all the Runs for the selected dog.
  • // Loop
  • // #If the dog is not entered in a trial, don't put it into the email
  • // If [ Run::Level <> "Not Entered" ]
  • // Set Variable [ $RunsForEmail[$countRuns]; Value:" Trial: " & Run::TrialNumber & " - Element: " & Run::Element & ", Level: " & Run::Level & ", - Class: " & Run::AorBClass ]
  • // Set Variable [ $countRuns; Value:$countRuns + 1 ]
  • // End If
  • // Go to Record/Request/Page [ Next; Exit after last ]
  • // Exit Loop If [ Run::__fkDog <> $GetNextDogFromSet ]
  • // End Loop
  • // # End Loop All Runs for selected dog
  • // End If
  • #End Dog has runs in the Run table.
  • End If
  • // #Dog is in the Set ending.
  • // Set Variable [ $EmailContent; Value:$DogForEmail & ¶ & ¶ & $DogForEmail[$countEmail] & ¶ & $RunsForEmail[$countRuns] & ¶ ]
  • Set Variable [ $EmailContent[$countEmail]; Value:$DogForEmail & ¶ & ¶ & $DogForEmail[$countEmail] & ¶ ]
  • Set Variable [ $countEmail; Value:$countEmail + 1 ]
  • Set Variable [ $countDogInSet; Value:$countDogInSet + 1 ]
  • Set Variable [ $GetNextDogFromSet; Value:GetValue ( $SetOfAllDogs ; $countDogInSet ) ]
  • Pause/Resume Script [ Indefinitely ]
  • End If
  • // Set Variable [ $EmailContent; Value:$DogForEmail & ¶ & ¶ & $DogForEmail[$countEmail] & ¶ & $RunsForEmail[$countRuns] & ¶ ]
  • // Set Variable [ $countEmail; Value:$countEmail + 1 ]
  • Go to Layout [ “Dog-INVISIBLE-GetEmailInfo” (Dog) ]
  • Set Variable [ $count; Value:$count + 1 ]
  • Go to Record/Request/Page [ Next; Exit after last ]
  • Pause/Resume Script [ Indefinitely ]
  • Exit Loop If [ $count > $NumOfDogsInDogTable ]
  • End Loop
  • Go to Layout [ “People Details” (People) ]
  • Send Mail [ Send via SMTP Server; To: People::Email; Subject: "Trial Confirmation"; Message: "Nosework Club" & ¶ & "October Nosework Trial" & ¶ & "Location Place" & ¶ & "October 2017" & ¶ & "Trial Hours are 7:00 am through 6:00 pm" & ¶ & ¶ & "Dear " & $PersonName & ", " & ¶ & ¶ & "Your entry into the Utah Nosework Club’s October 2017 has been received and you have a reserved spot in the trial.  Please read over this email and verify that all the information is correct.  If there are any discrepancies in your entry please reply to this email as soon as possible. " & ¶ & ¶ & "Owner: " & People::cFullName & ", " & People::StreetLineOne & ", " & People::City & ", " & People::State & ", " & People::Zip & ", " & People::CellPhone & "." & ¶ & ¶ & " EmailContent: " & ¶ & ¶ & $EmailContent & ¶ & ¶ & "*If you would like your dog's titles (both prefix and suffix), sire, dam, and breeder information added to your dog's record, please reply to this email. Titles from any club (AKC, UKC, etc) are acceptable."; Name: "Secretary"; Email Address: "email"; Reply-To Address: "email"; SMTP Server: "xxx"; Port: xxx; Use SSL; Authentication Type: Plain Password; User Name: "email"; Password: "Password" ] [ No dialog ]

Outcomes