How can I create a gratuity calculation workflow in HubSpot without coding?

[LThom]

Hey everyone

I’m trying to build a simple HR automation for UAE businesses inside HubSpot. Basically, when HR updates salary and joining date, I want HubSpot to auto-calculate the employee’s end-of-service gratuity.

Is it possible to do this purely through workflow formulas or custom properties? Or do I need to use external logic for this kind of computation?

I checked calculategratuity.ae for formula reference wondering if I can apply similar logic in HubSpot fields. Has anyone tried something like this?

[MichaelMa · Accepted solution]

You can definitely do it through workflows custom coded actions but as far as I'm aware, you there's really not much mathing that you can do without custom coded actions (just increment, I believe).

You can do it via calcuation properties IF you really wanted to but it can get complicated.

Presuming:

1. Join Date property (date field)

2. Leave Date property (date field)

3. Salary field (number)

Calcuation Property Method:

You can do it using a calcuation - custom equation property (gratuity).

We start the formula using time_between the first and second fields. Date fields are unix timestamps in milliseconds so we divide by 86400000 to get days and then +1 to add current day.

time_between([properties.date_start], [properties.date_end]) / 86400000 + 1

Now that you have the days between two points, you can do the rest of the formula which should just be mathing around these two fields and maybe using IF statements if you wanted to be more concise.

Eg,

if ( 
(
(time_between([properties.date_start], [properties.date_end]) / 86400000) + 1 >= 365 AND 
(time_between([properties.date_start], [properties.date_end]) / 86400000) + 1 <= 1825
),
[properties.salary]/365 * 21,
if ( 
time_between([properties.date_start], [properties.date_end]) / 86400000 + 1 > 1825,
[properties.salary]/365 * 30,
0 
))

I don't know what formula you're using or what other "special" secenarios you want to consider (eg, this counts all days between two dates so it includes weekends and holidays not just "working days").

If a long complicatedm multi-if statement is too unweidly, you could perhaps create multi grauitity fields that sum up into one field.

Workflow Method using Custom Coded Actions:

1. Create a new workflow

2. Create a Custom Coded Action Node

2a. Pass in the fields you need

2b. Do the code for the formulas (you can probably chatgpt it). Just sample code here:

exports.main = async (event, callback) => {
  /*****
    Use inputs to get data from any action in your workflow and use it in your code instead of having to use the HubSpot API.
  *****/
  
  const millisecondsInADay = 1000 * 60 * 60 * 24;
  const dateDiffInDays = event.inputFields['date_end'] - event.inputFields['date_start'] * millisecondsInADay + 1 //Date fields are in unix_timestamp milliseconds format so we convert to days to be useful

  var gratuity = 0 //default to 0
  
  if (dateDiffInDays >= 365 && dateDiffInDays < 1825) { //If greater than 1 year but less than 5 years
    gratuity =  event.inputFields['salary'] / 365 * 21
  } else if (dateDiffInDays >= 1825) { // Greater than 5 years
    gratuity =  event.inputFields['salary'] / 365 * 30
  }
  
  /*****
    Use the callback function to output data that can be used in later actions in your workflow.
  *****/
  callback({
    outputFields: {
      gratuity: gratuity
    }
  });
}

Which would output a gratuity value.

3. Add a new action to update a property and push the gratuity value into that property.

[d-lupo · Accepted solution]

Hi @LThom, 

What you're trying to achieve is definitely possible within HubSpot. Whether or not it can be done without any code at all really depends on how accurate and detailed you want the gratuity calculation to be.

If you're simply looking for a rough estimate based on a few general rules, then this can very probably be done using custom properties and simple workflows, without the need for external logic or code.

However, if you want to account for more complex scenarios, for example, employees whose salary has changed over time, and you want to calculate gratuity pro rata for each salary period, then it gets more technical. Handling these cases accurately would likely require some more data and coding, possibly with the help of an integration or custom code within HubSpot.

Also, for a fully accurate calculation, you'd need more inputs than just salary and joining date:

• Employee’s contract type (limited/unlimited),

• Reason for termination (resignation, dismissal, etc.),

• Total length of service,

• History of salary changes (if applicable).

So maybe you could clarify: are you aiming for a quick estimate, or a legally precise calculation? That would help determine the best approach.

Let me know if you'd like help mapping out a solution!

[MichaelMa · Accepted solution]

You can definitely do it through workflows custom coded actions but as far as I'm aware, you there's really not much mathing that you can do without custom coded actions (just increment, I believe).

You can do it via calcuation properties IF you really wanted to but it can get complicated.

Presuming:

1. Join Date property (date field)

2. Leave Date property (date field)

3. Salary field (number)

Calcuation Property Method:

You can do it using a calcuation - custom equation property (gratuity).

We start the formula using time_between the first and second fields. Date fields are unix timestamps in milliseconds so we divide by 86400000 to get days and then +1 to add current day.

time_between([properties.date_start], [properties.date_end]) / 86400000 + 1

Now that you have the days between two points, you can do the rest of the formula which should just be mathing around these two fields and maybe using IF statements if you wanted to be more concise.

Eg,

if ( 
(
(time_between([properties.date_start], [properties.date_end]) / 86400000) + 1 >= 365 AND 
(time_between([properties.date_start], [properties.date_end]) / 86400000) + 1 <= 1825
),
[properties.salary]/365 * 21,
if ( 
time_between([properties.date_start], [properties.date_end]) / 86400000 + 1 > 1825,
[properties.salary]/365 * 30,
0 
))

I don't know what formula you're using or what other "special" secenarios you want to consider (eg, this counts all days between two dates so it includes weekends and holidays not just "working days").

If a long complicatedm multi-if statement is too unweidly, you could perhaps create multi grauitity fields that sum up into one field.

Workflow Method using Custom Coded Actions:

1. Create a new workflow

2. Create a Custom Coded Action Node

2a. Pass in the fields you need

2b. Do the code for the formulas (you can probably chatgpt it). Just sample code here:

exports.main = async (event, callback) => {
  /*****
    Use inputs to get data from any action in your workflow and use it in your code instead of having to use the HubSpot API.
  *****/
  
  const millisecondsInADay = 1000 * 60 * 60 * 24;
  const dateDiffInDays = event.inputFields['date_end'] - event.inputFields['date_start'] * millisecondsInADay + 1 //Date fields are in unix_timestamp milliseconds format so we convert to days to be useful

  var gratuity = 0 //default to 0
  
  if (dateDiffInDays >= 365 && dateDiffInDays < 1825) { //If greater than 1 year but less than 5 years
    gratuity =  event.inputFields['salary'] / 365 * 21
  } else if (dateDiffInDays >= 1825) { // Greater than 5 years
    gratuity =  event.inputFields['salary'] / 365 * 30
  }
  
  /*****
    Use the callback function to output data that can be used in later actions in your workflow.
  *****/
  callback({
    outputFields: {
      gratuity: gratuity
    }
  });
}

Which would output a gratuity value.

3. Add a new action to update a property and push the gratuity value into that property.

[MichaelMa]

Just a note that if statements in calcuation fields are similar to Excel if statements. I would avoid long complicated multi-step if statements but that's possible if you want.