• Add to Collection
  • About

    About

    This is a adaptive database using the QuickBase platform and API, this is one of many configurations of QuickBase, including project management, … Read More
    This is a adaptive database using the QuickBase platform and API, this is one of many configurations of QuickBase, including project management, inventory, customer support CRM, and many more. Read Less
    Published:
Automated database using QuickBase platform and API
EVALUATION FORM
This is a evaluation form, there are two versions of this form, one has less information and controls which is used for our service centers so it is not included.
This is the intial screen, when a product like controls are selected only fields pertaining to controls show up, you may also notice that the products are listed, this is pulling from another database, this allows us to control who can create returns and who can perform evaluations.
We have selected a soft starter, this shows the fields for a soft starter, then we selected to repair the drive so the database shows a part list, the summary is a email that will be sent to the customer, this summary is adaptive and based off of the RA number, customer name, what you are doing and if there are any cost(see partial formula below).

If([Different Summary Text]="",
If([Type of Evaluation]="Warranty",
   If([Warranty status]="Approved",
      If([Disposition]="Repair",
      "Your "&[Model Number]&" is repairable and we will begin the repairs listed below as soon as you approve.</br></br>"&"<b>Parts List</b></br>"&[Parts List]&"</br></br><b>Please Choice One:</b> I would like Weg to</br>"&
      "<a href=mailto:pdiaz@weg.net?subject=Please&nbsp;Repair&nbsp;"&[RA number - RA#]&"&body=Please&nbsp;repair&nbsp;my&nbsp;"&[Model Number]&"&nbsp;and&nbsp;provide&nbsp;a&nbsp;status&nbsp;update.%0D%0A%0D%0AThank&nbsp;you,%0D%0A"&Left([RA number - Contact Name], " ")&"&nbsp;"&Right([RA number - Contact Name], " ")&">REPAIR</a>"&
      "   --   "&
      "<a href=mailto:pdiaz@weg.net?subject=Please&nbsp;Credit&nbsp;"&[RA number - RA#]&"&body=Please&nbsp;issue&nbsp;credit&nbsp;on&nbsp;my&nbsp;"&[Model Number]&"&nbsp;.%0D%0A%0D%0AThank&nbsp;you,%0D%0A"&Left([RA number - Contact Name], " ")&"&nbsp;"&Right([RA number - Contact Name], " ")&">CREDIT</a>"&
      "  my "&[Model Number],
      "Your product has been evaluated,  a credit has been issued to your account for the "&[Model Number]&" in  the amount of $"&"<b>"&[Price of unit]&"</b>. It can take up two weeks to get a credit memo mailed to you, contact your salesman for details. Please contact me if you have any further questions or concerns.")
   ,"Your "&[Model Number]&" has been denied for the following reason:</br>"&[Reason for warranty denial]&"</br></br>"&
     If([Repair status]="Beyond Economical Repair",
 
This formula is about 5 pages in a word document, and uses a advance IF-THEN statement along with html to format and email code for the responses, making it easy for the customer, all they have to click is wether they want to do the repair or not.
RA Log and Letter
This is the form to create a return request, this allows the tech to input the information given by the customer, we also have another version of this form for the customer which allows them to request for a return, then a tech can validate the information and ether make it into a RMA or deny it.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>RA Letter V1.12</title>
<meta name="description" content="This document is a automatic emailer, that provides dynamic information using HTML and QuickBase API.">
<meta name="author" content="Nate Thompson">
<style type="text/css">
body
{
   background-color: #FFFFFF;
   color: #000000;
}
</style>
<style type="text/css">
a
{
   color: #F0F0F0;
   text-decoration: underline;
}
a:visited
{
   color: #F0F0F0;
}
a:active
{
   color: #F0F0F0;
}
a:hover
{
   color: #715D5D;
   text-decoration: underline;
}
</style>
<style type="text/css">
#Table2
{
   border: 0px #C0C0C0 none;
   background-color: transparent;
   border-spacing: 1px;
}
#Table2 td
{
   padding: 0px 0px 0px 0px;
}
#Table2 td div
{
   white-space: nowrap;
}
#Line1
{
   color: #9EA0A0;
   background-color: #9EA0A0;
   border-width: 0px;
}
</style>
</head>
<body>
<table style="position:absolute;left:0px;top:0px;width:754px;height:646px;z-index:0;" cellpadding="0" cellspacing="1" id="Table2">
<tr>
<td colspan="2" style="background-color:transparent;text-align:left;vertical-align:top;height:54px;">
<div><span style="color:#000000;font-family:Arial;font-size:13px;"> </span><span style="color:#000000;font-family:Arial;font-size:16px;"><strong>[Company Name]<br></strong></span></div>
<div><span style="color:#000000;font-family:Arial;font-size:13px;"><br></span></div>
<div><span style="color:#000000;font-family:Arial;font-size:13px;">Dear [Contact Name],</span></div>
</td>
<td style="background-color:transparent;text-align:right;vertical-align:top;height:54px;">
<div><img src="https://wegelectric.quickbase.com/up/bhgp2xygs/g/rb/eg/va/logo.gif" id="Image1" alt="" border="0" style="width:59px;height:44px;"></div>
</td>
</tr>
<tr>
<td colspan="3" style="background-color:transparent;text-align:left;vertical-align:top;height:50px;">
<div><span style="color:#000000;font-family:Arial;font-size:13px;"><br></span></div>
<div><span style="color:#000000;font-family:Arial;font-size:13px;">This is a automated email providing you with information and instructions on your return authorization. Please follow the </span></div>
<div><span style="color:#000000;font-family:Arial;font-size:13px;">instructions listed below in order to return your product.&nbsp; Failure to comply with these instructions may delay the credit process.</span></div>
</td>
</tr>
<tr>
<td colspan="3" style="background-color:transparent;text-align:left;vertical-align:top;height:18px;">
&nbsp;</td>
</tr>
<tr>
<td colspan="3" style="background-color:transparent;text-align:left;vertical-align:top;height:18px;">
&nbsp;</td>
</tr>
<tr>
<td colspan="3" style="background-color:transparent;text-align:left;vertical-align:top;height:22px;">
<div><span style="color:#FF0000;font-family:Arial;font-size:16px;"><strong>[Warranty Type]</strong></span><span style="color:#000000;font-family:Arial;font-size:13px;"><strong> - [Date RA Issued]</strong></span></div>
</td>
</tr>
<tr>
<td colspan="3" style="background-color:transparent;text-align:left;vertical-align:top;height:48px;">
<div><span style="color:#000000;font-family:Arial;font-size:24px;"><strong>Return Authorization Number: </strong></span><span style="color:#FF0000;font-family:Arial;font-size:24px;"><strong>[RA#]</strong></span><strong></strong></div>
</td>
</tr>
<tr>
<td colspan="3" style="background-color:transparent;text-align:left;vertical-align:top;height:18px;">
<div><span style="color:#000000;font-family:Arial;font-size:13px;"><strong>Authorized products to be returned(additional items will be scraped):</strong></span></div>
</td>
</tr>
<tr>
<td colspan="3" style="background-color:transparent;text-align:left;vertical-align:top;height:18px;">
<div><span style="color:#000000;font-family:Arial;font-size:13px;">[RA Products]</span></div>
</td>
</tr>
<tr>
<td colspan="3" style="background-color:#C0C0C0;text-align:center;vertical-align:top;height:34px;">
<div><span style="color:#FF0000;font-family:Arial;font-size:13px;"><strong>&nbsp;&nbsp; ALL CARTONS MUST BE IDENTIFIED WITH THE AUTHORIZATION NUMBER STATED ABOVE AND THIS LETTER </strong></span></div>
<div><span style="color:#FF0000;font-family:Arial;font-size:13px;"><strong>ATTACHED AS A PACKING LIST TO INSURE PROMPTED PROCESSING.</strong></span></div>
</td>
</tr>
<tr>
<td colspan="3" style="background-color:transparent;text-align:left;vertical-align:top;height:18px;">
&nbsp;</td>
</tr>
<tr>
<td colspan="3" style="background-color:transparent;text-align:left;vertical-align:top;height:18px;">
<div><span style="color:#000000;font-family:Arial;font-size:11px;">[RA Procedure]</span></div>
</td>
</tr>
<tr>
<td colspan="3" style="background-color:transparent;text-align:left;vertical-align:top;height:114px;">
<div><span style="color:#000000;font-family:Arial;font-size:13px;">Thank you,</span></div>
<div><span style="color:#000000;font-family:Arial;font-size:13px;">XXXXXXXX</span></div>
<div><span style="color:#000000;font-family:Arial;font-size:13px;">Automation Technical </span></div>
<div><span style="color:#000000;font-family:Arial;font-size:13px;">Services Coordinator</span></div>
<div><span style="color:#000000;font-family:Arial;font-size:13px;">XXXXXXXXX</span></div>
<div><span style="color:#000000;font-family:Arial;font-size:13px;">XXXXXXXXX</span></div>
<div><span style="color:#0000FF;font-family:Arial;font-size:13px;">www.weg.net</span></div>
</td>
</tr>
<tr>
<td colspan="3" style="background-color:transparent;text-align:center;vertical-align:top;height:18px;">
&nbsp;</td>
</tr>
<tr>
<td colspan="3" style="background-color:transparent;text-align:center;vertical-align:top;height:18px;">
<div><span style="color:#000000;font-family:Arial;font-size:13px;">----------------------------To insure fast delivery please cutout and attach below portion to the outside of your crate----------------------------</span></div>
</td>
</tr>
<tr>
<td colspan="3" style="background-color:transparent;text-align:center;vertical-align:top;height:18px;">
&nbsp;</td>
</tr>
<tr>
<td colspan="3" style="background-color:transparent;text-align:center;vertical-align:top;height:104px;">
<div><span style="color:#000000;font-family:Arial;font-size:11px;"><u> PLEASE RETURN TO:</u></span></div>
<div><span style="color:#000000;font-family:Arial;font-size:19px;"><strong>XXXXXXXXX</strong></span></div>
<div><span style="color:#000000;font-family:Arial;font-size:19px;"><strong>XXXXXXXXX</strong></span></div>
<div><span style="color:#000000;font-family:Arial;font-size:19px;"><strong>XXXXXXXXX</strong></span></div>
<div><span style="color:#000000;font-family:Arial;font-size:19px;"><strong>Attn: </strong></span><span style="color:#FF0000;font-family:Arial;font-size:19px;"><strong>Automation Test Lab</strong></span></div>
</td>
</tr>
<tr>
<td colspan="3" style="background-color:transparent;text-align:left;vertical-align:top;height:40px;">
<div><span style="color:#FF0000;font-family:Arial;font-size:19px;"><strong>[RA#]</strong></span></div>
<div><span style="color:#000000;font-family:Arial;font-size:13px;">[RA Products]</span></div>
</td>
</tr>
</table>
<hr id="Line1" style="margin:0;padding:0;position:absolute;left:0px;top:143px;width:750px;height:3px;z-index:1;">
</body>
</html>
 
 
This code is written in standard html with a couple API calls, this allows it to be more flexable with the different methods in which a customer may view the email.
SERVICE CASE
This is our service case form used to talk to customers and provide technical support.
Like many of the forms this is a adaptive form, the tech support will decide what they need to fill out, this allows for a trackable system.
After the service case is save a generous amount of items appear to further the case along, any further action can be tracked using the activities button, allowing you to create meeting, take notes, or track emails. There is a note function which only appears for the Warranty personnel who need to capture information on a possible warranty along with a ability to create a return request using the RA numbers section.
REPORTS
QuickBase allows you to mine multiple data sources however you can only view one report at a time, so I added some html code to bring all the reports together using the API system, code below.

<!-------------------------------------------Tabel 1------------------------------------------->
<!--Contains Two Pie Charts-->
<table><tbody><tr>
<!--Percentage of Warranty Types-->
     <td width="50%"> <iframe src="https://wegelectric.quickbase.com/MSChart/?realmhost=wegelectric.quickbase.com&amp;dbid=bhiew3mf6&amp;qid=10&amp;generate=map&amp;width=500&amp;height=334" marginwidth="0px" marginheight="0px" id="iframeDundas" name="iframeDundas" width="500px" frameborder="0" height="334px" scrolling="no"> </iframe>
    <br></td>
<!--Percentage of Monthly Status-->
    <td width="50%"> <iframe src="https://wegelectric.quickbase.com/MSChart/?realmhost=wegelectric.quickbase.com&amp;dbid=bhiew3mf6&amp;qid=6&amp;generate=map&amp;width=500&amp;height=334" marginwidth="0px" marginheight="0px" id="iframeDundas" name="iframeDundas" width="500px" frameborder="0" height="334px" scrolling="no"> </iframe>
    <br></td>
</tr><tr>
<!--RA's Created-->
     <td width="50%"> <iframe src="https://wegelectric.quickbase.com/MSChart/?realmhost=wegelectric.quickbase.com&amp;dbid=bhiew3mf6&amp;qid=23&amp;generate=map&amp;width=500&amp;height=334" marginwidth="0px" marginheight="0px" id="iframeDundas" name="iframeDundas" width="500px" frameborder="0" height="334px" scrolling="no"> </iframe>
    <br></td>
<!--RA's Received-->
    <td width="50%"> <iframe src="https://wegelectric.quickbase.com/MSChart/?realmhost=wegelectric.quickbase.com&amp;dbid=bhiew3mf6&amp;qid=24&amp;generate=map&amp;width=500&amp;height=334" marginwidth="0px" marginheight="0px" id="iframeDundas" name="iframeDundas" width="500px" frameborder="0" height="334px" scrolling="no"> </iframe>
    <br></td>
</tr><tr>
<!--RA's Evaluated-->
     <td width="50%"> <iframe src="https://wegelectric.quickbase.com/MSChart/?realmhost=wegelectric.quickbase.com&amp;dbid=bhiew3mf6&amp;qid=25&amp;generate=map&amp;width=500&amp;height=334" marginwidth="0px" marginheight="0px" id="iframeDundas" name="iframeDundas" width="500px" frameborder="0" height="334px" scrolling="no"> </iframe>
    <br></td>
<!--RA's Closed-->
    <td width="50%"> <iframe src="https://wegelectric.quickbase.com/MSChart/?realmhost=wegelectric.quickbase.com&amp;dbid=bhiew3mf6&amp;qid=26&amp;generate=map&amp;width=500&amp;height=334" marginwidth="0px" marginheight="0px" id="iframeDundas" name="iframeDundas" width="500px" frameborder="0" height="334px" scrolling="no"> </iframe>
    <br></td>
</tr></tbody></table>
 
I prefer to mark the text in this case, so that if I ever leave WEG someone else can understand the code I have written much quicker.
This is but a small portion of the system I have created, this has shaved off countless hours and allows automated tracking and report generation.