4 Replies Latest reply on May 6, 2015 4:22 PM by Fred_3

    Table In Lieu Of Case Command



      Table In Lieu Of Case Command


      I have the following script that works, but would like to replace the CASE command with a table:
      If [ List Parameters::Mobile Numbers ≠ "-" ]
      Set Error Capture [ On ]
      Set Field [ <Table Missing>; "" ]
      Set Variable [ $$MOBILENUMBER; Value:Case ( List Parameters::Mobile Numbers = "Fred" ; "8884532180" ; List Parameters:: Mobile Numbers = "Mike" ; "8886542323" ; List Parameters::Mobile Numbers = "Tom" ; "8887654323" ) ]
      Set Variable [ $$SCRIPT_TRIGGER; Value:"Off" ] End If
      Send Mail [ Send via SMTP Server; To: $$MOBILENUMBER & "@txt.att.net"; Message: Residential Customers::Last Name & ", " & Residential Customers::First Names & ¶ &
      Residential Customers::Street Address & ¶ &
      Residential Customers::City & " " & Residential Customers::Zip & ¶ &
      Residential Customers::Phone & ¶ &

      Residential Customers::Number of Systems & " Systems"; Name: "Douglas Mechanical, Inc."; Email Address: "douglasmechinc@gmail.com"; Reply-To Address: "douglasmechinc@gmail.com"; SMTP Server: "smtp.gmail.com"; Port: 465; Use SSL; Authentication Type: Plain Password; User Name: "douglasmechinc@gmail.com"; Password: "**********"
      My thinking is that if the names and numbers are in an easily viewable table, the numbers could be easily changed by others without changing the script.  The table would be as follows:
      EMPLOYEES table
      Fred             8884532180
      Mike             8886542323
      Tom              8887654323
      Any thoughts?  And thank you for your time.

        • 1. Re: Table In Lieu Of Case Command

          Use a table just like you do in the body of the email.  I assume you are emailing employees of customer information?  If so, then setup a employee table with a relationship to the customer table using the Cartesian. employee::id x customer::id   This type relationship will match all employees to all customers.   You would then have a dropdown to select the employee you want to text with their phone number.

          If it is the customer you are texting then just add a phone number field to the customer table.

          • 2. Re: Table In Lieu Of Case Command

            I am able to do the drop down list and relate the table to the rest of the database, but I still don't know how to set the variable to the MOBILE NUMBERS by only passing the name to the script.  Essentially, I don't know what to substitute for the CASE command.

            • 3. Re: Table In Lieu Of Case Command

              You don't need the case script step.  Use the Phone Number field in the send mail script step.  The dropdown is used to select the employee record which contains the Phone Number field.   The Phone Number field will work just like all the other fields in the email script step.  Instead of $$mobilenumber you would use employeetable::mobilenumber.

              • 4. Re: Table In Lieu Of Case Command

                I'm sorry.  I'm not able to get it to work.

                I choose a name from the drop down field, which gets passed to the script via List Parameters::Mobile Numbers.  Then Employeetable::mobilenumber will yield the related phone number.

                It seems like I would need something like Go To Related Record where the List Parameters::Mobile Numbers is passed to find the field with Employeetable:employeename.

                Thank you so much for your patient.