Building minimum stock alerts in HaloPSA with a custom field and SQL tweak
A practical workaround for MSPs who need to know when stock runs low, built entirely from custom fields and a tweaked native report.
The short version
HaloPSA does not natively support minimum stock levels or low stock alerts. This tutorial shows how to build a workaround using a custom field on the product entity plus a small SQL edit to a stock levels report, so you can schedule an email whenever stock drops below where it should be.
What you'll take away
-
No native low stock alert exists
HaloPSA has no built-in way to flag when stock runs out or falls below a threshold.
-
Custom field lives on the product entity
Build it in Configuration, Custom Objects, Custom Fields, and pick Product (called Item in the USA language pack).
-
Save before details will show
The new custom field will not appear in the tab list until you save the field once and go back into it.
-
The SQL is just CF plus AS
Pulling the custom field into a report is as simple as writing CF, the field name, then AS and a label.
-
Group by clause will reject it first time
HaloPSA throws an aggregate error until you add the field into the group by clause with a comma.
-
Steal the stock levels report and schedule it
Copy the existing Stock Levels report from the online repository, add the custom field as a column, then schedule it by email to whoever handles purchasing.
Key insights from the episode
-
Create the custom field under Configuration, Custom Objects, Custom Fields against the Product entity (Item in the USA language pack).
-
Field names cannot contain spaces or special characters, so use the field label for anything human readable.
-
Set the input type to integer so only numbers can be entered for a minimum stock value.
-
Save the custom field once before trying to add it to the Details tab, otherwise the tab option will not appear.
-
In the report SQL, reference the field as CF, the exact field name, then AS and your chosen column label.
-
If HaloPSA errors about an aggregate or group by clause, add the same field into the group by clause with a comma and retest.
-
Copy the existing Stock Levels report from the online repository rather than building one from scratch.
-
Once the field is on the report, use Edit Schedule Email to send it monthly to whoever handles purchasing.
Questions people actually ask
Does HaloPSA have a native minimum stock level feature?
No, HaloPSA does not have a built-in minimum stock level or low stock alert function. You can track stock received, transferred and issued across locations, but there is no native way to flag when a product falls below a threshold.
How do I add a minimum stock level field in HaloPSA?
Go to Configuration, Custom Objects, Custom Fields and create a new field against the Product entity (Item in the USA language pack). Set it as a text field with the input type limited to integer, save it, then go back in and add it to the Details tab.
Why does my new custom field not show up on the Details tab in HaloPSA?
You need to save the custom field once before the Details tab option becomes available. This looks like a bug or a design quirk, but saving first and reopening the field resolves it.
How do I pull a custom field into a HaloPSA report?
Edit the report SQL and reference the field using CF followed by the exact field name, then AS and your chosen column label. If HaloPSA throws an aggregate error, add the field to the group by clause with a comma and test again.
Can I get email alerts for low stock in HaloPSA?
Not natively, but once you have a minimum stock level custom field on a report, you can use Edit Schedule Email on that report to send it out regularly, for example monthly, to whoever handles purchasing.
Which HaloPSA report should I start from for stock levels?
Search the online repository under Reporting for Stock Levels and add that existing report to your library rather than building one from scratch. It already includes quantity in stock, and you just add the custom field as an extra column.
Full transcript
1,649 words
Read full transcript
Collapse
Full transcript
1,649 words
Hello and good evening. I'm not very good at these one take videos. I kind of sit here and think I'm going to just nail these out for you and really help you but then I listen to it back and I'm like ah the audio quality just isn't how I desire it anyway. Enough about me, how you all doing? I hope you're well.
Um, after title insinuates I'm going to show you how to set up a minimum stock level function in HaloPSA. Now this is using custom fields. This isn't natively built into Halo but I'm speaking to a customer just the other day who was saying Connor, how do I track my stock? I don't know if I've got enough in or not. And I was like I don't really know and then I thought about it and came up with this little solution for them which they love that this works them really well. And I asked if I could share this on YouTube with everyone else. I said sure. Um, if it helps them go for it.
So I want to show you what I mean by this. So just go to products and I'm just going to go to serialised items and I'm just going to pick on ethernet cable five meter. So if you're not familiar with stock in Halo you can basically have multiple stock locations such as a warehouse in the office in a van and you can basically transfer stock receive stock and then also issue stock when you're on site or you know you're selling an item.
Now that's fantastic in Halo. I can click receive stock. I can say I've got 10 of these in the post. They come from Amazon and I'm going to put all these in the warehouse like so. We have 10 and you'll see that on the 22nd of September I added five and but they all went so I had zero remaining and on the 18th which is today I added 10 and I still have 10 remaining. The problem is as far as I'm aware you can't get any notifications when your stock runs out and you also can't have a minimum stock level so you don't again it's not easy to understand how many you should or shouldn't have in there.
So I made a little solution for this. I'm going to share with you. So what I did is went to configuration. I went to custom objects, custom fields and then I selected product. If you are in the USA language pack this will say item. Bear that in mind. And I made a new custom field. The field name is almost irrelevant but I recommend you rename them to something that makes sense so minimum stock. Just know you can't have any spaces or special characters in that field name. The field label is going to be minimum stock levels. That is what displays to me. This wants to be a text field. The input type wants to be limited to an integer and you can only put a number in there. Having number of minimum stock and that's all I'm going to do. You could make this mandatory so every time you add a new item you put in there what your minimum stock level is but for now I'm just going to click save and that's not too relevant.
Then I'm going to just click edit and go all the way back down and then click details. I don't know if there's a bug or it's by design but the first time you add a new custom field details does not show in the tab box. It demonstrates very quickly here. You can't do detail doesn't appear. You have to save it first. Yes, I'll be logging up to Halo after this video. I figured that out myself five minutes ago on my first recording. Um, but essentially what we've done now is we've said add the custom field see if minimum stock to the tab details on products.
So if I go to products I go to ethernet cable five meters I'm in the details tab I scroll to the bottom and you'll see here we now have a minimum stock levels custom field. I'm just going to type in the value 30. Now this is a custom field. This is plain text. This isn't going to do anything particularly crazy for us but we can leverage it.
So now I want you to make a report with me. Once you click reporting in the bottom left of Halo, my little face is covering that up, and then click the online repository button for me at the bottom. In the top left I'm going to type in stock levels and basically go and steal this report from HaloPSA and add this report to your library. Then click back on reports and then basically open the report.
What we're going to do is edit this and this is a nice report out of the bag anyway but we can say I want to edit this and I want to basically edit this SQL and this is quite scary. This is very daunting to me when I first did this but once you start getting around it a little bit it actually makes kind of a lot of sense. I'm just going to place this in here and I'm going to do the most simple SQL I've ever done in my life here. I'm going to do CF which stands for custom field because we just made a custom field. I'm going to type in minimum stock and I'm going to type as and do minimum stock levels and we're going to click test.
Okay cool. I'll show that in a minute but just to reiterate I basically give in my custom field here. This doesn't work everywhere in the system by the way um but it certainly does for this one and I've gave the the custom field name in here. I then said as so I want to display this custom field as and then give it a name. You can type anything between these two brackets whatever your little heart desires.
Um, and basically that is almost you done. You notice when I click test we got an error and it's basically saying that um you can't use minimum stock because it's not contained in an aggregate of a group by clause. What that means in English um I think is it basically doesn't exist in this group by clause at the bottom here so you need to add a comma. You need to press Ctrl V to paste it in and then click test and then it should say successful.
Just going to go ahead and save that. Then you've got to go to your fields because this report does have um all its fields added manually and we need to go and add the field we just made called minimum stock levels. Click save and I'm just going to put this after quantity in stock and then click save again.
We're then going to click view report. As you can see now we have ethernet cable five meters we have 10 in stock and we now have a minimum stock level of 30. What you can then do, if I just go back into this, is you can then click edit schedule email and email this to yourself for purchasing or whoever does it and you can send this report each month and say you know if the stock level is you know lower than what it should be then please order it in.
You can go slightly further with this report. I'm not going to this evening because I've had a long day but you can add another column if you want and then put you know needs ordering um if quantity in stock is less than minimum stock level then fill the field with needs ordering and again if you want to see that let me know I will put the report in the comments but for now that is pretty much it.
So just to recap what we did. I made a new custom field in custom objects, custom fields and I made this in the entity product. I saved it. Very important. Went back into it and then said I want to show this custom field on the details tab. That then gave me a plain text custom field on the detail of the asset and this will be on every asset. There's no need you have to fill it in. It's just there if you want it. And then we made a report together and that is a little solution I found the other day to a problem that I had. I thought I would share it all with you.
Any questions find me in the comments below and have a lovely day. I've been Connor. Goodbye.
Author
Related tutorials
We recently engaged Renada Solutions to help our MSP migrate from AutoTask PSA to HaloPSA, and the experience was excellent. Conner was exceptionally friendly and ensured we were comfortable with each step of the process. He was supported by Robbie, who excelled in handling the technical aspects of the implementation.Ilkley IT
Our Core Services
Offering support to enable sustainable success for your organisation.
