How to Create and Use Custom User Functions - (2024)

See more videos and subscribe to the Proposal Kit YouTube channel

In this video we're going to show how to create and use Custom User Functions in the line item database system. These are set up in the Preferences in your line item database definitions. We'll go into Data and Display, click Line Item Quote Database.

We have a custom database setup here and this is just a cloned copy of the Basic Product Sales. We've made a couple modifications to it for this demo. If I go into Edit Definition, I've created a salesperson list for the drop–down list type because what we're going to do is show how to use a custom user function along with a drop–down list.

The reason we're using a drop down list is because it helps control the user input to a set list of values which will be numeric and then we can use those numeric values within a Custom User Function. This list is basically a list of numbers 0, 1, 2, 3 and the data values Ian, Karen, Joel and Unknown for the default. So these are the display values that you would see in a drop–down list list and these are the underlying data values, the unique values, associated with each item.

Our Custom User Function will operate on these numbers. What we're going to show in this example is using a Custom User Function to turn a salesperson selected for the quote into the commission rate that's going to determine the commission for that salesperson. So now that we've added that salesperson list we're going to add a global field, use the drop down list type, and when we create a drop–down list type we have to pick the list.

So that's why we pre–created that list just to shorten the video a little bit. We're going to hide the salesperson from the output so that way it doesn't get output into the Cost Summary. This is just for behind the scenes.

We've already used the label or tag SALESPERSON for the actual list so we will just shorten this up. All right, now that we have our list and field created let's go create a Custom User Function. We'll just add a new function, we'll call it the Sales Commission Function and I'll set our default value to zero.

Now a Custom User Function if you're familiar with Excel it's basically like an Excel switch statement or an if then else statement where we're kind of just basically transforming one value into another value. In this case we're going to transform the salesperson which is going to be a numeric value 0, 1, 2, 3 into an actual commission percentage. By default the commission percent will be zero and we're going to build up this if then else statement using these options.

If our salesperson is zero enter a data value. So we're going to return zero which would be the same thing as the default. Now we're going to add if salesperson is 1 let's just set the result of point 1 for 10%.

If salesperson is 2 let's make that 15%. If salesperson is 3 make that 20%. Now you can see all the entries.

We can select individual ones, go back and make edits if we want. To see kind of what the function is going to actually look like as a statement this is how it would be evaluated. All right, so we've got our function set up.

Now I can use this function within a calculation. This is going to become a global field and we're going to add a new global it'll be salesperson commission and that'll become a currency. And it's going to be a calculation.

So we'll create a CALC statement and we can look at View Calc Fields and we'll actually see Sales Commission Function as a selectable calculation value. We can just type this in but using this form it keeps us from making spelling mistakes. We're doing a calculation on the Sales Commission Function now that will return 0, point 1, point 15 or point 2 but we want to multiply that by the order total.

So our order total will be one of the globals. Now depending on how you want to do the calculations are you going to do this off of the subtotal before taxes, discounts and so on or after everything. It's going to be up to you to determine the actual value that you're going to be doing your salesperson commissions off of.

Maybe you even need to make a a new hidden custom field to be the correct value that the commission will be created on. So it's really going to come down to you, how you run your business practices. Let's just do it off the overall total amount that's after taxes, shipping, discounts are applied, sales tax and so on.

All right, and we're going to make that a hidden field because it's not going to get output to the Invoice or Cost Summary that the client sees. It's just going to be used behind the scenes so you can have record of the salesperson commissions. All right, so other uses for Custom User Functions could be now say you want to create a Custom User Function for calculating shipping costs based on the weight of the entire order.

You can create a custom function where you take the weight of the order and then depending on the weight you can output the shipping amount. So it has a lot of uses. You're basically just using Custom User Functions to convert one value to another value for use within a calculation typically or to convert one value to another for display purposes.

So let's go put this all to use in an actual project. I'm going add a new project and select our custom database. Let's put in some line items.

We got some line items in there. Let's get our globals. Let's say we have a 6% tax rate, shipping might be 20 bucks, $10 discount amount.

Who's the salesperson, let's just pick Karen here. That was the 15% sales commission. We can view the globals here so you can see this is our 15% of the overall total.

Now if we go do a Test and Preview this salesperson won't show up in the output because we hid the field and you'll just use that for your backend accounting to know how much you owe in your salesperson commissions. So you can see the Cost Summary, the Invoice. We can actually go do a build of the whole document.

All right, our final build has been completed. So you can see the final output here. If we wanted to make the salesperson visible, say you wanted to add that somewhere up in the Invoice, that's easy.

You just edit the custom template and put the salesperson tag anywhere you want. You could put it down here, you could put it up in the top under the invoice number and it would tell you the salesperson name. We're not going to want to put their commission in the Invoice but that's a customization you can make easily.

The Cost Summary is a little more simplified version you know because we just want to keep it down to the basics. And that is kind of the basics of creating Custom User Functions to transform one value to another and you can use those in calculations.