Create a Purchasing workflow in Lansweeper

[randallader]

Lansweeper is great at inventorying assets, and pretty good at helpdesk ticketing, but it lacks a complete purchasing setup. I’ve configured the helpdesk ticketing system’s single purchasing ticket into a spiceworks-like purchasing work-alike.

Full purchasing fields from Spiceworks were added for tracking. Vendors are supported. Categories and departments are multi-level ala Spiceworks. All the benefits with customization to boot.

Report on all purchases on single rows per purchase. Sortable and searchable.

Record multiple assets per purchase with hyperlinking. Assets with purchases are linked back to the purchase ticket too.

• Total time: < 1 day
• Estimated cost: Lansweeper license for your environment
• Tools used: None

Step 1: Part 1: Adding custom fields to the Helpdesk Ticketing System.

In Lansweeper go to Configuration, Helpdesk Settings, Ticket Custom Fields:

Add these custom fields as Name | Info | Type | Values

• Purchase Status | Pending, Approved, Purchased, Received or Denied. | Color Combobox | Pending
  Approved
  Purchased
  Received
  Denied

• PO # | Purchase Order # | Textbox |

• Description | Item or part description | Textbox |

• Qty | Quantity ordered | Numeric |

• Price | Price | Currency |

• Purchased for | If intended for asset, make sure to click Asset concerning button otherwise just enter info. | Textbox |

• Vendors | Vendor list. Add new vendor in Ticket Content, Vendor custom combobox values. | Combobox | Fill in your vendors

• Part # | Part number | Textbox |

• Category | Purchase classification | Combobox | Fill in your requirements here

• Vendor Order # | Invoice/Sales number | Textbox |

• Memo | Additional notes. Need more room, use the ticket notes below. | TextArea |

• Charged to | Department charged to | Combobox | Fill in your departments

• Ticket assigned | Copy hyperlink of ticket URL to link ticket here | Hyperlink |

• Ordered Date | Date ordered | Date |

• Paid Date | Date paid | Date |

• Received Date | Date received | Date |

• Tracking # | Shipper tracking number | Textbox |

Additionally, you have to add sub-fields if you want to track extra info in Categories, Departments or Facilities. The additional sub-categories are up to your imagination. Listed here are some of my examples based on Spiceworks categories. These examples will become clear in the next section. I prefixed them with the Cat: to indicate they belong in the Category collection. You can do the same with any other sub-fields you see fit.

Step 2: Part 2: Setup the Purchasing ticket workflow.

Here we setup the Ticket entry workflow. Some fields won’t appear until a prerequisite field is satisfied.

• Configuration, Helpdesk Settings, Ticket Content.
• Make sure Ticket types have IT Purchase listed.
• Select IT Purchase name and change for your needs.
• Set default agent if necessary.
• Set Icon to ‘shopping-basket.png’ or what you like.
• Set Agent team for your needs or leave as Purchase.
• Default note type: Internal.
• Ticket Description: Required or Optional to have ticket note box available. Set to invisible if you what to hide it.
• Input Worktime: Unchecked for not tracking time.

Alter Custom Fields to your liking. Mine are as follows to closely resemble Spiceworks Purchasing data entry fields, but I’ve made changes the order of entered fields to better fit my needs.

• Purchase Status
  - Required for closing
• PO #
  • PO Approved
• Description
  - Required
• Qty
  - Required
• Price
  - Required for closing
• Purchased for
• Vendor
  - Add your vendor list here
• Part #
• Category
  • Cat: Hardware
  • Cat: Software
  • Cat: Services
  • Cat: Consumables
• Vendor Order #
  • Tracking #
• Memo
• Charged to
  - Add your department list here
• Ticket assigned
• Ordered Date
  • Paid Date
    • Received Date

Optional:
If you have SLA enabled and want to turn it off for purchasing, navigate to Configuration, General Settings, Service-level agreement and click Show Targets. Change the Ticket Types from All to your needs, excluding IT Purchase.
SLA Screenshot:

Step 3: Part 3: Create your first purchase ticket.

Create your first purchase.

Click Create Ticket and change the Type to IT Purchase to see the custom form. Fill in the fields and save.

See attachments.

Step 4: Part 4: Setting up your Purchasing Helpdesk Ticket Panel.

This will give you a pseudo-purchasing panel that emulates the basics of Spiceworks purchasing screens, modified to suit your needs. There are two parts to this. First one is the Purchasing panel for current open tickets and the Second is the Past Purchases for closed purchase historical lookups. You can forego the past purchases screen if you want to rely on the report feature in Part 6.

Click on the New Tab link shown under the Helpdesk tab. Set your filters to Status: all but Closed, and Type to IT Purchase.

Click Apply filter button above. Click the Select columns button now. This will allow you to add columns to your display tab. Customize as needed.

Click Ok and view your handywork. Adjust as necessary for your organization. To save the tab for future use, click the floppy disk icon next to the X button. Give it a name and Save.

If you change or adjust the columns later and want to save the new layout, click the floppy disk icon to save the changes, but use the overwrite existing filter choice.

If you want to see past purchases, you’ll want to setup another screen like this one, but only pick the Closed status. You have to create a new tab like before, change the Status boxes as shown to only Closed, apply the filter then click the floppy disk icon to save the changed tab with a new name such as Past Purchases.

Editing the purchase workflow.
Now to edit the ticket/purchase status simply open the ticket/purchase like normal.

1. To change the Purchase Status, click on the Edit button on the top. From here you can change almost all custom fields.
2. Click the user icon with the red pencil on the right side to edit any notes that were put into the standard yellow note area when creating the purchase.
   Sample edit screen of purchase:

Bonus: when you add any image attachments to the purchase, they show up as little buttons at the top of the form. Hovering over them produces a thumbnail image of the attachment.

When you’ve completed the purchase and received the product(s), edit the Purchase Status to Received, fill in the Vendor Order #, Received Date and any other fields as needed. Click OK to save the changes.

For the Ticket assigned: field, use the hyperlink URL of the work ticket to link the purchase to the ticket. Using the Asset’s during the creation of the purchase will also link the two together, but this links the work to the purchase as well.
Note: you can link multiple assets to a single purchase if you have one order for multiple assets. This allows you to link a single work order, like upgrade all keyboards and mice on all workstations, to a single purchase ticket, and have that linked to multiple assets.

Once the purchase is completed, click the Close Ticket button and that will remove it from the Purchasing tab and it will now be in the Past Purchases tab.
Example of the Past Purchases tab:

Step 5: Part 5: Setting up Vendors as assets (Optional)

Setting up vendors is a 4-step process requiring using the Asset screens. Adding any asset increases the license count usage, but there is a way around that using the Monitor Asset Type. Following this process, you can any number of Vendors without an increase in license count.

1. Setup a new Asset Type – Configuration, Asset Mapping, Add Asset Type.
2. Setup a new Asset State – Configuration, Asset Pages, Add Asset State.
3. Setup Dynamic Asset Grouping – Configuration, Asset Groups.
   The key to this is the filtering on the State not the asset.
4. Setup your Vendors – Assets, New Asset.

Only three items are needed.

• State is changed to Vendor
• Asset Type is changed to Monitor (for no license count)
• Assetname.

All other fields are at your descrection.
 
Now when you want to see the Vendors list, you can simply click on the Dynamic Group “Vendors” and get the complete list.

From this point you can configure the reporting as you see fit. Just remember to look for Assets = Monitors and Status = Vendor.

If your curious to see if the license count is affected or not, verify the current license count in Configuration, Licensed assets, change the Vendor’s Asset Type from monitor to anything else and save asset, then head back to the license count and see the number change.

I suspect this bit of trickery can be used to deactivate, sold or otherwise disposed other equipment not in use and recover the license count while still maintaining the historical data on the retired asset.

Step 6: Part 6: Reporting:

First build is a report that lists on one line the relevant summary of the purchases made. This includes:

• Ticket #
• Ticket/Purchase Name
• Qty
• Price
• Part #
• Vendor
• Vendor Order #
• Order Date
• Paid Date
• Received Date
• Shipper Tracking #
• State of Ticket (Open, Closed, In Process, …)
• Priority of “Ticket”
• PO #
• PO Approved (Y/N)
• AssetName (purchase was assigned to)
• Intended use (Purchased For field)
• Charged To
• Short Description
• Purchase Status fields.

I’ve added the hyperlink capability to the AssetName to click through to the asset the purchase was for. If there are multiple assets the purchase was assigned to, it will only show one asset on the line.
As with all the reports in Lansweeper, you can sort and/or filter on the columns to find your data.

Overview of the sections for those wanting to understand it.

The first section is to setup the tickets custom fields mapping to variables that is used for the Left Join groups.

The second section is for colorizing the output. First is the foreground (text) coloring starting with the CASE statement and ending with the entire END line. Second is for coloring the background of the rows. I’ve tried to use pastel colors so the text is visible. Same conditions apply to the code, CASE to END lines. Don’t use both color codes in the same report. Third option is to remove all refences to the color code to have the report in basic report colors. Your choice.

In the third section, pay attention to the grouping of the Left Join statements. These pull the data out of the custom fields in the Tickets. Last line it to filter all the “Tickets” by Purchases (#3) only. Without it, you would get all the tickets ever created.

Here is the report formatted in a more readable format.

Select Distinct Top 1000000 htblticket.ticketid,
  '../helpdesk/icons/' + htbltickettypes.icon As icon,
  '#' + Cast(htblticket.ticketid As nvarchar) As Ticket,
  htblticket.subject As [Purchase Name],
  Qty.data As Qty,
  Price.data As [Price ($)],
  [Part #].data As [Part #],
  Vendor.data As Vendor,
  [Vendor Order #].data As [Vendor Order #],
  Convert(varchar(10),Cast([Ordered Date].data As date),110) As [Ordered Date],
  Convert(varchar(10),Cast([Paid Date].data As date),110) As [Paid Date],
  Convert(varchar(10),Cast([Received Date].data As date),110) As
  [Received Date],
  [Tracking #].data As [Shipper Tracking #],
  htblticketstates.statename As State,
  htblpriorities.name As Priority,
  [PO #].data As [PO #],
  Approved.data As [PO Approved],
  tblassets2.AssetName,
  tblassets2.AssetID,
  [Purchased For].data As [Intended Use],
  [Charged To].data As [Charged To],
  Description.data As [Short Description],
  [Purchase Status].data As [Purchase Status],
 
 /* Remove color section for normal foreground and background. You only need one section for coloring. Remove all highlighted code except the color code you want. You only need the section from Case to End. Lansweeper may not like the ‘/* to the */’ comment sections that SQL uses. */

 /* Color Foreground
 Case [Purchase Status].data
    When 'Recieved' Then '#33691e'
    When 'Pending' Then '#9c27b0'
    When 'Approved' Then '#f57f17'
    When 'Purchased' Then '#0d47a1'
    When 'Denied' Then '#b71c1c'
    Else '#ffadad'
  End As foregroundcolor
*/

/* Color Background
  Case [Purchase Status].data
    When 'Recieved' Then '#77dd77'
    When 'Pending' Then '#ffd6eb'
    When 'Approved' Then '#a4dded'
    When 'Purchased' Then '#dcdcdc'
    When 'Denied' Then '#ff6961'
    Else '#f8f8ff'
  End As backgroundcolor
*/

From htblticket

  Inner Join htblpriorities On htblpriorities.priority = htblticket.priority

  Inner Join htblticketstates On htblticketstates.ticketstateid = htblticket.ticketstateid

  Inner Join htblusers On htblusers.userid = htblticket.fromuserid

  Left Join htblagents On htblagents.agentid = htblticket.agentid

  Left Join htblusers htblusers1 On htblusers1.userid = htblagents.userid

  Inner Join htbltickettypes On htblticket.tickettypeid = htbltickettypes.tickettypeid

  Inner Join htblusers htblusers2 On htblticket.userid_lastnote = htblusers2.userid

  Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
      htblticketcustomfield.data
    From htblticketcustomfield
      Inner Join htblcustomfields On htblcustomfields.fieldid = htblticketcustomfield.fieldid
    Where htblcustomfields.name Like 'PO #') As [PO #] On [PO #].ticketid = htblticket.ticketid

  Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
      htblticketcustomfield.data
    From htblticketcustomfield
      Inner Join htblcustomfields On htblcustomfields.fieldid = htblticketcustomfield.fieldid
    Where htblcustomfields.name Like '%Qty%') As Qty On Qty.ticketid = htblticket.ticketid

  Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
      htblticketcustomfield.data
    From htblticketcustomfield
      Inner Join htblcustomfields On htblcustomfields.fieldid = htblticketcustomfield.fieldid
    Where htblcustomfields.name Like '%Price%') As Price On Price.ticketid = htblticket.ticketid

  Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
      htblticketcustomfield.data
    From htblticketcustomfield
      Inner Join htblcustomfields On htblcustomfields.fieldid = htblticketcustomfield.fieldid
    Where htblcustomfields.name Like '%Part%') As [Part #] On [Part #].ticketid = htblticket.ticketid

  Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
      htblticketcustomfield.data
    From htblticketcustomfield
      Inner Join htblcustomfields On htblcustomfields.fieldid = htblticketcustomfield.fieldid
    Where htblcustomfields.name Like 'Ordered Date') As [Ordered Date] On [Ordered Date].ticketid = htblticket.ticketid

  Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
      htblticketcustomfield.data
    From htblticketcustomfield
      Inner Join htblcustomfields On htblcustomfields.fieldid = htblticketcustomfield.fieldid
    Where htblcustomfields.name Like '%Received%') As [Received Date] On [Received Date].ticketid = htblticket.ticketid

  Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
      htblticketcustomfield.data
    From htblticketcustomfield
      Inner Join htblcustomfields On htblcustomfields.fieldid = htblticketcustomfield.fieldid
    Where htblcustomfields.name Like 'Vendor') As Vendor On Vendor.ticketid = htblticket.ticketid

  Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
      htblticketcustomfield.data
    From htblticketcustomfield
      Inner Join htblcustomfields On htblcustomfields.fieldid = htblticketcustomfield.fieldid
    Where htblcustomfields.name Like '%Purchased For%') As [Purchased For] On [Purchased For].ticketid = htblticket.ticketid

  Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
      htblticketcustomfield.data
    From htblticketcustomfield
      Inner Join htblcustomfields On htblcustomfields.fieldid = htblticketcustomfield.fieldid
    Where htblcustomfields.name Like '%Purchase Status%') As [Purchase Status] On [Purchase Status].ticketid = htblticket.ticketid

  Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
      htblticketcustomfield.data
    From htblticketcustomfield
      Inner Join htblcustomfields On htblcustomfields.fieldid = htblticketcustomfield.fieldid
    Where htblcustomfields.name Like '%Approved%') As Approved On Approved.ticketid = htblticket.ticketid

  Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
      htblticketcustomfield.data
    From htblticketcustomfield
      Inner Join htblcustomfields On htblcustomfields.fieldid = htblticketcustomfield.fieldid
    Where htblcustomfields.name Like '%Charged To%') As [Charged To] On [Charged To].ticketid = htblticket.ticketid

  Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
      htblticketcustomfield.data
    From htblticketcustomfield
      Inner Join htblcustomfields On htblcustomfields.fieldid = htblticketcustomfield.fieldid
    Where htblcustomfields.name Like 'Vendor Order #') As [Vendor Order #] On [Vendor Order #].ticketid = htblticket.ticketid

  Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
      htblticketcustomfield.data
    From htblticketcustomfield
      Inner Join htblcustomfields On htblcustomfields.fieldid = htblticketcustomfield.fieldid
    Where htblcustomfields.name Like 'Paid Date') As [Paid Date] On [Paid Date].ticketid = htblticket.ticketid

  Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
      htblticketcustomfield.data
    From htblticketcustomfield
      Inner Join htblcustomfields On htblcustomfields.fieldid = htblticketcustomfield.fieldid
    Where htblcustomfields.name Like 'Tracking #') As [Tracking #] On [Tracking #].ticketid = htblticket.ticketid

  Left Join (Select Top 1000000 htblticketcustomfield.ticketid,
      htblticketcustomfield.data
    From htblticketcustomfield
      Inner Join htblcustomfields On htblcustomfields.fieldid = htblticketcustomfield.fieldid
    Where htblcustomfields.name Like 'Description') As Description On Description.ticketid = htblticket.ticketid

  Left Join (Select htblticketasset.ticketid,
      Min(htblticketasset.assetid) As AssetID
    From htblticketasset
    Group By htblticketasset.ticketid) FirstAsset On FirstAsset.ticketid = htblticket.ticketid

  Left Join tblAssets tblassets2 On tblassets2.AssetID = FirstAsset.AssetID

Where htblticket.tickettypeid = 3

So with the notice that Spiceworks is EOL’ing their inhouse Spiceworks Desktop application at the end of this year, I was determined to make Lansweeper handle my purchasing needs. I’ve only been using LS now for 2 years and have gotten better at understanding it.

This project took me two weeks to fully implement and it gives me some added benefits over SW.

1. I can add a single purchase and assign multiple assets to it.
2. I can see purchases in progress on one screen, and switch to another to see past purchases.
3. In the report I can see a total list, color coded if I want, of all the purchases made, filter and sort on the same report.
4. In the asset screens, I can see the purchase ticket assigned to that asset without having to dig thru other reports to track down purchases.
5. Vendors can be maintained as assets without using a license count. (not really a benefit over SW, but an Easter Egg find)

A big thank you goes out to Lansweeper’s technical support group. First runs of the report ran multiple rows per asset of the same purchase ticket. So, if you had multiple assets per purchase, you’d get multiple rows.
It wasn’t pretty.
The Distinct clause along with their help sub-grouping tblassets2 cleaned up that mess.

If you want the full document write-up I’ve done on it, PM me and I’ll be happy to send it on.

[randallader]

Finally got Lansweeper to post the documents here in their forum.

https://www.lansweeper.com/forum/yaf_postsm65613_How-I-managed-to-create-a-purchasing-workflow-in-LW.aspx#post65613

[randallader]

Additional note: I figure out how to migrate the purchases from Spiceworks to Lansweeper. Follow the documents I posted in the above link for the additional guide.

[spiceuser-yiyfm]

Thank-you for this!

Bookmarked and starting my migration next month.

[randallader]

Your welcome. Good luck with your migration. PM me if you need any clarification.

[billingsley (Chris8573)]

THANK YOU!!! I am reluctantly looking for a Spiecworks replacement since they abandoned their desktop version. I really like Lansweeper except for the lack of purchasing and vendor tracking. You have made my decision SOOOOO much easier by posting this how-to. Thanks again!!!

[randallader]

No problem. Being in the same boat we have to help our shipmates out. DM me if you need. Good luck.