Project – Order Tracking System

Order Tracking System Project

Business Task

Create a web-based order tracking system for a photography company. The system will be comprised of the following components:
  • Online order form with dynamic fields
  • Order database with tabs that track all stages of the order from in process to completed and paid
  • Invoicing capabilities
  • Payroll capabilities
  • Sales metrics and visualizations
  • Outstanding orders linked to contractor spreadsheets with select data
The system will be cloud-based and will utilize as many free services as possible to minimize costs.

Technologies Incorporated

Order Form: Jotform
Order Database: Google Sheets
Web Connector: Make (formerly Integromat)
 

Task 1: Order Form Creation

The order form will be made up of the following fillable fields:

  • Name
  • Email Address
  • Property Address
  • Image Type
  • Image Count
  • Photo Editing Notes
  • Terms & Conditions Agreement
There will also be some non-fillable fields that contain dynamic responses based on data entered in the fillable fields:
  • Price Per Image – The price per image varies based on text entered in name field and options selection in the image type field.
  • Estimated Total – This field is calculated by taking the dynamic number given in price per image multiplied by the image count.
  • Rushed Delivery Notice – This field appears if the rushed deliver option is selected in the image type field.
  • Free Revisions Notice – This field appears if the free revisions option is selected in the image type field.
  • Image Upload Notice – This field appears based on text entered in the name field.
The order form will also contain many hidden fields that are used for calculations and other back-end functions. They will be discussed in further detail as we go along.
 
We chose to use Jotform as the order form solution since it allows us to create dynamic fields and because of it’s low cost. As of this writing, there are four main packages offered: a free package, a $24/month package, a $29/month package, and a $79/month package (all billed annually). We’ll be using the free package for this demo but the final form the photography company is using required the $24/month package since they needed more than the 100 submissions provided by the free plan.
 
We wanted the following the order form to give us the following information upon submission: timestamp, client name, client email, property address, image type, image count, billable image count, service cost, contractor needed for the service, contractor’s pay rate, office staff pay rate, photo editing notes. 
 
The final form contained the following fields, in this order:
 
Timestamp – Date Picker Field. This is a hidden field since we just wanted a timestamp of the order passed back upon submission.
*Jotform did not have a native way of passing a timestamp over upon submission when this form was originally created. It seems they have rectified this since then but we haven’t tested it since our workaround solution works.
Name – Short Text Field.
Email – Email Field.
Property Address – Short Text Field.
Type of Images – Single Choice Field. This field has three options for the three services offered.
 
Rushed Delivery Notice – Paragraph Field. This field is hidden by default. If the rush service is selected in the type of image field, this message will appear.
 
Free Revisions Notice – Paragraph Field. This field is hidden by default. If the free revisions service is selected in the type of image field, this message will appear.
 
Image Enhancement – Short Text Field. This is a non-fillable field that displays the price per image based on the name field and the type of images field. If a name is not in the system, the price defaults to the retail price. If a name is in the system, the price defaults to that client’s specific pricing. The rushed delivery service is always a certain amount more than the standard service. The free revisions service is always free so that option will always cause this field to output $0.00.
Image Count – Number Field.
Billable Images – Number Field. This is a hidden field that is filled based on the type of images field. If “Free Revisions” is selected then a zero is entered into this field. Otherwise, the image count field number is passed on to this field.
Estimated Total – Short Text Field. This field is hidden by default but will display if a value is passed on to it. The value in this field is calculated by taking the billable image field and multiplying it by the image enhancement field.
Contractor – Short Text Field. This field is hidden and already has placeholder text entered into it since the same contractor handles all of these orders.
Z_Edit Pay – Short Text Field. This field is a non-fillable, hidden field. Like the image enhancement field, it’s value is based on what’s entered in the name field and type of image field.
Staff Pay – Short Text Field. This field is a non-fillable, hidden field. Like the image enhancement field, it’s value is based on what’s entered in the name field and type of image field.
Editing Notes – Long Text Field.
Terms & Conditions Checkbox – Terms & Conditions Widget.
 
 

Task 2: Order Form Settings

Now that the form is built, we can setup the proper conditions for the dynamic fields. This is done by going into the Settings tab and clicking on Conditions from the left navigation bar.

We’ll need to create conditions for our price per image field (labeled image enhancement), contractor pay field, staff pay field, rushed delivery notice, free revisions notice, billable images field, and estimated total field. Let’s go through each of these fields.

Image Enhancement

Condition 1: Each client has their own unique price based on criteria the photography has set. We can pass these custom values over to the form by creating an Update/Calculate Field Condition. We will need several variations of this condition in order to capture all of the current clients. For this example, we’ll look at two clients that both have a rate of $3.75 per image.

We’ll tell this condition to enter an amount of $3.75 into the image enhancement field if two rules are met. (1) The type of images must be equal to image enhancement. Free revisions and rushed delivery image types will have their own unique conditions. (2) The name must contain the name “Kelsey Stewart” or “Tony Noble”.

Once we hit save, the condition will be set.

Now that this condition is set, we can clone it and edit the name values and amount for each additional client.

Condition 2: Once we have all of our clients entered we can clone all of those conditions and this time change the type of images to the rushed delivery option and update the amounts for each of the clients.

Condition 3: Since our free revisions image type always carries a value of $0.00, we do not need to use the name field for this condition.
Condition 4: We need one final condition that handles situations where a name is entered into the name field that isn’t recognized. This will allow people that aren’t set up as clients to purchase the service. We have already told the form to default the image enhancement value to $8.00 an image. That value is overwritten if the rules in conditions 1-3 are met. This default value will effectively cover unrecognized names that want to purchase the image enhancement service which is exactly what we want. However, we do want the field to update to $9.50 if an unrecognized name orders the rushed delivery service. We can do that by cloning a rushed delivery condition and changed the name condition’s state to “Does not Contain” and entering every client name into the value field. Then we’ll change the amount to $9.50 and click save.

Contractor Pay

Contractor pay has similar pricing rules as the image enhancement field so we can repeat the steps above and insert the custom rates into the Z_Edit Pay field which is our contractor pay field. If an unrecognized name is typed into the name field, the order form will pass on the default field value of $2.50 so we don’t need a condition for unrecognized names that are ordering the image enhancement service. However, we do need a condition for unrecognized names that order the rushed delivery service.

Staff Pay

The staff pay field needs to be $1.20 for one set of clients and $2.10 for another set of clients. We can set these rules following the same steps we’ve already taken for our other fields.

Rushed Delivery Notice & Free Revisions Notice 

Our rushed delivery and free revisions notices were set to hidden by default when we created our form. We can make them appear by creating two Show/Hide Field conditions.
Billable Images

Condition 1: We want the billable images field to change to “0” if the image type is equal to “Free Revisions”. We can accomplish this by creating an Update/Calculate Field condition where the number 0 is entered into the billable images field if the type of images field is equal to “Free Revisions”.
Conditions 2 & 3: We want to pass the image count field value over to billable images if the type of images field is equal to “Image Enhancement” or “Image Enhancement – RUSH”. We can create this by using another Update/Calculate Field condition. We’ll tell that condition to copy the value of the image count field over to the billable images field if the type of images field is equal to “Image Enhancement”. Then we can clone this condition and change “Image Enhancement” to “Image Enhancement – RUSH”.
Estimated Total

We want our order form to calculate an estimated total for us by taking the image count and multiplying it by the price per image (image enhancement field). We can do this dynamically by creating another Update/Calculate Field condition. An if statement is required so we can just look to make sure the image enhancement field and the image count field are filled and if those rules are matched we’ll calculate the estimated total field by multiplying the image count field and the image enhancement field.

Task 3: Spreadsheet Connection