Halo Q&A Series – Custom Tables

hi everyone and welcome to another Halo video with me again today I have my all-time favorite person at Halo Mr Halo I know he's missed me saying this quite a lot because we we haven't done these videos for a little while um and uh I I heard by the Great Vine he was really upset and sad over Christmas time that no one called him Mr Halo but we're back we're calling him Mr Halo he's got a smile on his face and that's what I like to see so hey Morgan um so yeah it what I wanted to talk about is carrying on with our kind of Q&A series and one of the things I hear a lot about is you know custom stuff within um within Halo and I know we covered it off on the on the last call you know about kind of setting up custom feels with with sqil but I get a lot of queries around you know what are custom tables how do they work when do you use them so it' be good to maybe just go through that on on this video so um welcome again Morgan thank you for your time and uh um yeah if if we can kind of go through that and um let people know what that's all about yeah good to be back um yeah probably a a short one we'll just keep it to this there's a few bits around custom tables uh so yeah well let's let's go through all of that so custom Fields hopefully you're all familiar with the concept of custom Fields now um a bit more of a technical explanation as to where they are being added is they're B basically an extra column that's added to the database table for the entity in question right so what do I mean by that uh well a custom field against the customer entity let's do it customer custom field becomes a column that's added to the area table in the database where the area table is the table containing customer information so blah blah blah blah blah blah blah blah blah there it is CF customer custom field right so custom fields at database level are just columns that are added to the respective tables for the entities yeah a custom table is is as you would imagine its own table in the database own custom table now if we're thinking about tables right tables are composed of columns and when we've bring that back to Halo a custom table is just a series of custom Fields added together so let's have a look at how to create a custom table so we go config custom objects custom tables and a custom table uh let's call this one sales info see I've done this before uh when we're setting up our custom tables we have this option to link to another entity generally you will want to link it to another entity you know do we want our custom table to be added to tickets H request do we want it to be added to area a custom table against the customer entity and so on I'll do this one against tickets you'll notice you can set none um there's been a couple of Niche requirements that people have had in the past where uh they need a table of information available in Halo but not linked to anything um some something like a a sales sheet that says if the quote is over this value then uh if the quote is over this value and is going to be charged over a 36-month period uh then apply an escalation factor to each monthly payment um that doesn't necessarily link back to anything that's just a sheet in Halo that we can then query to get the information we need out again kind of Niche generally will be linking to an entity and when we um link our table to an entity after saving we have this field list Tab and this field list tab is just a bunch of custom Fields so we're just creating custom fields in here where each custom field becomes a column of that custom table so let's do Eder and we'll leave that as text let's do details and we'll make that uh Rich Text so we might want to include images and then let's make a checkbox and we'll see why in a moment and we'll call this one um display checkol the options in here same options as what we see for our standard custom Fields y all right so we have created the custom Table after saving we've got the field list tab we have created custom fields that are composing the table and so now we have the custom table but we're not quite finished yet the final step to be able to use this sales info table in the UI is to create a custom field within the same entity in this case ticket of type table and then when our type is table we've got an option below where we can pick from the custom tables that we have created for the respective entity cool um so now when I'm include this custom field on my ticket type there we go so we've added our custom field it is in fact a table it's got three columns a header column and our display check box simple as that now there are a couple of uh less commonly known features around custom tables that it'll be sort of good opportunity to discuss firstly um the options that are at custom field configuration level so if we go back to the custom field that we've just created we'll see that there's actually two tabs that have appeared that we don't usually see so these are two tabs specific to our custom fields of type table the first one is default default value so we can provide default as it says on the tin we can provide default values in here we can prepopulate this table and such that when a new when a new opportunity or whatever ticket type you're logging with this table uh is logged the information will already be there in the table and then secondly this customized variable Tab and this was sort of you know touch touching on the fact that we're going more down this FAQ Series this was something that uh a partner recently asked about um custom tables are great but how do I actually change the format uh of the table itself how how can I uh amend the styling and how how it looks and it's from the customize variable tab there's three options in here first selection field the selection field will will provide a list of all of the custom fields that have composed the table and are of type checkbox so that's why I made that display field of type checkbox and we can see that's in there now um and what this does is this will only display rows in that custom table where this checkbox is checked and the reason that we did this initially to provide a bit of context here is uh when you're building your proposals out you might sell security you might sell VoIP you might want to include your slas you might want to include an about a section um basically you can have predefined blocks of information from the default value Tab and only include the bits of information that are Rel relevant to the proposal that you're building based on the check box being checked and I will probably set up a quick example to provide a bit more context there um but yeah so below the selection field we have the option in here to amend the HTML for um for for the the table itself right so the in the table variable uh we can basically set the the headers I might not actually want to set the headers I might just want to take the content from what's inside the table uh so for that I'll just do dollar all rows and then below that we have our table row HTML and then what we might want to include in here is the the header as a header t tag uh and then below that the details so to do something like that the first thing that I need to do is I need to know what the custom field value or the custom field names are for our table so we go back config custom objects custom tables go to the field list and we've got header and header is CF header so in here I'll do like H1 dollar CF header something like this and then maybe break and then and then below that we might want to include the details which is dollar or CF details so we need to do dollar CF details so what this means is if we go back to our opportunity I think it was this one yeah if I take the custom field value or custom field name which is dollar CF sales INF save that let's see what happens so now we can see dollar CF sales info the variable for this custom field has been replaced with the contents of the table furthermore the way the that the table has been presented namely header break details has been determined based on the HTML that we've put into the customized variable tab it's a little bit techy but hopefully you understand the concept there yeah that makes that makes perfect sense and that's actually good if you had like a like a price list or something that you maybe wanted to send to a customer right you could put that into a table send that over to the customer and go here's a list of prices or whatever and and what's also important to bear in mind is I mean I I just threw that variable onto the action um what you can also do is you can put that same variable onto your quote templates so when you generate the quote it will take the information from the opportunity kind of like it's what it's done here but it would do it on the quote template um maybe we maybe we can look at that as well let's do that uh as one final piece so we can go to our quotes go to our templates we'll just use this one we'll make a new page at the bottom and we'll just call it extra details get rid of all of this and just put dollar CF sales info save that save that go back here let's do this one um change that opportunity and what I'll do here is I will add um two rows in so we'll call this one uh header header to show details to show and we'll check display and then we'll add another and we'll say headed to hide details to hide and we won't check this and then we'll go through the motions blah blah blah raise the quote use the default template add something on there Bang Bang generate our PDF let's see what's happened so a crude example but hopefully it demonstrates the point um we made a separate page and we use that variable uh what is rendered on the quote is based on the h ml that we've set for the custom field and then the rows that are displayed on the quote are the rows where the checkbox has been checked based on my selection field that I set in here yeah and yeah like I say if you're if you're an MSP that sells well the chances are you you sell different Services you provide different services and you want your proposals to provide information about the services that that you manage or the uh the services that you offer or anything like that and um this is a great way to achieve that by building out a table with predefined blocks of information for the different things that you you offer uh and then instead of having to type that out or instead of having to pick the template you just check the box or check the boxes that represent the pieces of information that you want to return on the proposal and yeah there's custom tables in a Nut Shop yeah I mean there's some really powerful stuff in here um in terms of um you know how custom tables work and I think um what what we probably could do in these other videos is to explore this kind of stuff in a lot more detail because I think a lot of people um you know VAR interested in in custom Fields custom SQL you know that kind of stuff um so yeah and this was one that comes up all the time around how to use custom tables and I think this is It's been it's been really useful it's been a really good way of being able to um you know demonstrate the the power of of what custom tables are and how they work yeah yeah maybe we can come back to it and um I mean one of another thing that we can do with custom tables is um is it in here custom tables uh yeah we can import data into custom tables on a scheduled basis uh again going back to the example um that I provided earlier if we have a a sheet a factor sheet of you know quotes worth this much over this period have this Factor applied that might change each year instead of manually updating that you can use uh this mechanism to import uh Records into your custom table and then records are updated in that custom table based on our um import keys so uh we pick the column in here that is the unique identifier and then when importing that spreadsheet we match existing records based on the value being the same yeah makes sense cool no I think that's that's been really good I think that's uh that that'll address a lot of people's um questions and you know this kind of stuff comes up all the time and I think you know we we get to a certain point as consultants where you you know you set up Halo and then you get to a certain point where people say well actually I need more I need to be able to understand this does um and and then you know then they see all these custom tables and wonder how they work so um you know and at somebody if anyone wants to know anything more maybe some use cases on how these these would work um you you know happy to go through those um custom buttons and custom tabs we can we can cover off at some point as well that goes through a kind of a very similar Pro process um so yeah no I really appreciate it Morgan as always appreciate your time appreciate you and uh really appreciate Halo allowing us to do these videos um if anyone has any any comments or any questions or you know likes or dislikes put them in the comments below um and I'll make sure Morgan sees them and you know any things that you want to to know about any kind of you know questions you have let us know and we we'll cover those off in the next um on the next few videos yeah yeah we're we're doing these for you guys so the more information you can provide us with as to what you want to know about the more value we can provide back to you um by talking about it so yeah thanks for watching thanks for inviting me Chris and yeah see you on the next one as always thank you very much and we'll see you on the next one thanks have a good one just