Pull live product catalogue prices onto a HaloPSA ticket with database lookups

Connor: Hello, good day, for it is I. I hope we are all doing well today. I want to talk a little bit about database lookups inside of HaloPSA. I did a video a while ago and there was so much going on and I didn't really drill into database lookups, why you would want to use them, the power of using them. And I will show you a little bit how we use them. I suppose so, there you go, loads of use cases, but let's jump straight into it. Let me jump over to my Halo environment and let's talk about them a little bit.

So I am today inside my test environment, so there might be stuff popping up everywhere. Don't panic about that. We're going to focus on database lookups. So first of all, what do I mean by database lookups and why would you ever want to use them? Well, we use them to basically query information inside of Halo that might not be natively in the area we want it to be in.

So what I mean by that, and in this demonstration today, I'm going to start pulling the cost and the price of items from the products catalogue onto a ticket body. Now you can do many things with this. You can have it so you could pull first and last name and append it with something. I know on the trial you might have seen it. I know Jot is something fancy where it will take the company name, append it to the end of a LinkedIn URL to give you a quick way of getting their LinkedIn page. Mileage will vary, of course.

But the way we use it, and there's a specific use case we use now, I obviously can't show you it in full detail, but as you may know, we're distributors for HaloPSA, so we sell licences to a certain amount of our customers. And as a part of that, we need to submit that to HaloPSA the same way you would if you're buying licences from one of your vendors.

So as a part of our process, and I won't go through it all today, but essentially we have a capture form, if you will, and we basically have to select what licence we're selling or buying from Halo. Now you'll see here that I'm selecting item three or item four. It's returning with the results, meaning it's found something. And you'll be noticing here the cost and the price is changing.

Now what's clever about this, and the reason we use them, is because if I just go to item three and call this YouTube item and we change this recurring price to be £8.75 and the cost to be £25 and press save, what we'll notice is on the ticket is that now it will say YouTube item and those price and costings will be reflected.

So what this means for me is if Halo have a price increase on licences or if they change at all, I need to update it in one place in my setup that will update it in all of my systems. But also, mean through my sales engagements, the pricing and the costings will always remain the same.

You can take this one step further as well, and I will show you this as a part of this demonstration. But we can then also run totals. So what we do is we capture how many licences people want, what bandings that falls into. We then calculate the cost and pricing automatically on the ticket so we know what our buy and sell margins are.

But with all that being said, let's get stuck into a little bit to show you exactly what's going on here. Now there is a lot of moving pieces to this one, so please make sure you are strapped in. But first of all, how do we do this? How do we select the items on a ticket from our products catalogue?

Well, that's relatively straightforward and all we're doing there is using a dynamic SQL, basically, and a custom field. So if I go to custom objects, custom fields, and scroll all the way down, you will see here I've made a custom field called monthly licences. Now what is that doing? And I will put this SQL in the description below.

But essentially, it is selecting the item ID as ID. It is selecting the item desk as display from item. And that's basically selecting these two fields from item. We're then joining the generic table on the item table based on item generic equals G generic. I will show this what this means in a minute.

But then we're also saying where the default billing period equals two, and I want to order this ascending. Now what does that mean in English, Connor? Well, essentially what we're doing here is a dynamic list or dynamic SQL lookup. And what we're essentially doing is we're drilling down into our items table.

So if I just pull any of these, I think this is actually item. I think looking at it, I might have hit the jackpot there. There we go. Select from item. So we're basically saying, show me the item desk as the description. So this is the description of the product. So we'll see in here we'll have, you know, item, YouTube item, we'll have item two. And we're basically drilling down into this report or into the database, essentially.

So that's the first part. So the first part is we have a custom object, a custom field, and we are selecting a product. Okay, so we're going into our product catalogue and we're selecting a product from that product catalogue. Now at this stage, nothing else has really happened.

Then what we do is we go down to Integrations and we go down to database lookups. Now I'm pretty sure this is on my default, and I'm pretty sure you can't turn it off. But if it's not on or you can turn it off, I lie already. If you go to Integrations, type in database, you'll see database lookups. Just make sure you press the plus in the top right-hand corner, and that will then appear like this. Then click New in the top right-hand corner, and we're basically going to go through this together now and show you how we build this.

So the first thing is we must give this a name. The name can be anything you want. Doesn't really matter. It's just what we know when we're looking in our database lookups what they're all doing. And I've just called this, um, let's call this item lookup, shall we?

Then we have a trigger field. So when we change or modify or touch this field, something is then going to happen. So what field is it we want? Now I'm going to be honest with you, I've had varying mileage using summary of a ticket, and again, sum doesn't typically change, so I would avoid that one.

I would try and have it so, you know, when you change a field this runs. You can also have it at the very bottom here, run this lookup every time the ticket detail screen is opened or an action is submitted, so something is happening on the ticket or the ticket is opened. But in this use case, I just want this to literally be when I touch this field or change this field, then I want this to happen.

So once we've touched that field or modified it, then we want something to happen. So we then want to use an SQL script or storage procedure. Once again, and I want is select the recurring cost and the recurring price from the items table where something equals something. Okay.

So if I just jump out of here very quickly and I jump back into my reports and I go to this one here, don't know which one it was. I write it again. Let's pull up by item table.

Sorry, best away. And we basically want to say I want to set the recurring cost and price from the item ID of the monthly licence we select. So again, if you remember on here, when we go to service desk, we're selecting an item. What I want to do, and we could use a description, but I, you know, I like to use IDs where possible. I want to select the cost and the price from that item.

Once I've selected the cost and price from the item, I then want to do something with it. And what I want to do with it is I want to post that information or update that information into two custom fields on the ticket.

So what I've done is I've made two custom fields, configuration, custom objects and custom fields. These are about to blow your mind. We have cost and we have price, and these are just text, anything fields. Now you can use integer until you want to start going down a decimal place route, which I ended up last week was getting confused, so anything for the most part is fine.

And that all culminates into the fact that when I go to a ticket and I select or change the field monthly licence, I want to select the cost and price from that custom field monthly licence where the ID matches the monthly licence. And I want to update the cost and price of that item to these custom field cost and price, resulting in the cost and price being mapped here.

But we could go one step further once again. So we could make very, very, very, very, very, very quickly a custom field called licence quantity, like so. And this could again, we'll just make this an integer just for the time being, an integer. And we could add this CF licence quantity to a ticket type. And again, you can add this at an action level or you can add this at a ticket level. It doesn't really matter.

So if I now go and add a CF licence quantity, you will now see on the ticket, if I refresh, we'll now have the monthly licences and how many of them we have.

Now to pull all this together, we've then had to update our SQL, and what we want to do is we want to sum the recurring cost times this new field we've made, so CF licence quantity, like so. Now the good thing about doing these is we have a test button at the top. Okay, so if I click test now and do this, it will say error, invalid quantity, CF licence quantity. Well, that's because we've forgot to put in the dollar sign. Because it's a custom field, it needs to have the dollar in front of it.

So we'll test it again. We're going to select another item, and then it'll basically shout to us. I knew this was going to happen. And basically to say you can't do this because the recurring item cost is not contained within a group or aggregate function.

So all I need to do is amend my SQL script. Do group by, and I'm actually going to save you a little bit of pain here. I'm going to do item recurring cost and also item recurring price. And again, if we test it again now, it'll say we must declare the variable, CF monthly licences. Oh, that's my bad. You have read something and go hang on a minute. This isn't what this should mean. But there we go.

So that SQL is now currently working. Now although we've done this calculation, this sum, we've not told it to do anything with it. So what I'm just going to do here is say show me this as total. And earlier I made a custom field called CF total, which I call total.

So what we're doing here with this lookup field is we're matching the output of our SQL. So we have cost, price, and total. The descriptions here, cost, price, and total. And we're mapping those to custom fields inside of Halo for cost, price, and total. And these already exist in my instance.

The result being, roll please. If we now go to a ticket and we say I want a YouTube item, and the customer wants five of these, if I now cycle through this, it will then run the maths. Five times 25 is 125. Five times 34 is indeed 170.

So again, this is just a slight indication of how you can start building this out. I would actually have the calculation in my environment run on the licence quantity changing. So again, we can go ahead and we can update that. We could go actually, we don't want it to do anything on this. We want it to be on the CF licence quantity, licence quantity. I think it's this one here. Save. We do a refresh. We can then see we're typing in these numbers here.

The problem is the cost didn't update. So again, you can have multiple. So you could actually have in here as well, and this is how we do it. Just to be clear, we have it triggering on multiple things. So I'm not going to say it's required, just going to say if you do it, start running the magic.

But what you end up with, and I'm doing this with you, kind of show you how we build this up, is we can then select item four. And then once we run the quantity, it will also update the total. And it means we're having this quite dynamic experience where we can jump through different parts, and the calculations run.

The final piece of this puzzle, how we do it, is we actually make it so the cost, price, and total cannot be modified. They're just read-only fields. And it means that we can never fat finger a number in, and it also means to me we're having consistent data in how we are procuring and selling licences.

And that is it. That is a slight little look into database lookups and how we use them. So to reiterate, there's a few different things going on here. I'm going a little bit complicated with this. I am selecting something from the products catalogue. I am returning the cost and the price of that. I am then running a slight sum using SQL, and then I'm displaying that on multiple custom fields inside my instance, which makes my life a breeze.

That is it for my demo for today. I hope this helps somebody out. If you've got any more questions, put them in the comments below. There's many ways this can be leveraged. I just thought I'd give you a quick insight into how we use it and how you could use it. And that's it for me. Have a beautiful day. I've been Connor. Speak to you soon. Bye-bye.