Salary Calculation help please

[Nth20]

Hello,
I want to show the salary monthly for my team to see.
The salary = 14% x Total profit of that month devides number of employees (can be put manually or in a field). All the fields are located in the custom entity called Bookings (changed from Leads originally).
For example, I have 5 employees and the profit is each booking, booking A has profit $50, booking B has profit 60$, booking C has profit 70$. The total profit for these bookings is $180 and the number of employees is 5 so the salary is 14%x$180/5 = $ 5.04. Each booking (lead) has profit field and date fields so I can specify the month of those bookings.
How can I do this and show it in the report please?

[Ashif Malayil]

To create a monthly salary report in EspoCRM for your team (14% of total profit divided by number of employees), follow these steps:

1. Ensure Fields: Confirm the "Bookings" entity has a "Profit" (Currency) and "Date" field.
2. Create Grid Report:
   • Go to Reports > Create Grid Report.
   • Entity: Bookings.
   • Group By: MONTH: Date. (after booking completed you can update the date using workflow)
   • Columns: Add SUM: Profit (label: Total Profit).
   • Add a calculated column for salary: (SUM: Profit * 0.14) / 5 (for 5 employees, adjust as needed).
   • Add runtime filter: Date for month selection.
3. Display: Add the report to a dashboard for team visibility.

Note: You can create Custom module eg;- "Staffs", Create relationship with Booking, by using formula get the count. Profit for the month calculate through report filter and do the same salary calculation there.

[Nth20]

Hello Ashif, Thank you for your quick reply.
Can you please be more specific about these?

• Group By: MONTH: Date. (after booking completed you can update the date using workflow) - I don't need to update the completed date but actually I have 2 date fields in the Booking. One departure date and one return date (Passengers choose one date or both dates at their choice wether they want to book one-way or round-trip tickets). How can I include these both dates for the month?
• Columns: Add SUM: Profit (label: Total Profit) - Can you please show me more specific steps about this?
• Add a calculated column for salary: (SUM: Profit * 0.14) / 5 (for 5 employees, adjust as needed) - I don't know how I can do this too.
• Add runtime filter: Date for month selection.

I am sorry and look forward to hearing from you soon!

[Nth20]

Can you please help?

[dreginald]

I suggest, you need create a new entity (Base/Base Plus) - Salary Calculations and relate one to many with Bookings. Write required formula to calculate as per your logic with suitable report filters to get the Salary for each month and each employee, based on the profit in the bookings entity.

[Nth20]

Thank you very much Dreginald. My current entity named Booking and the field in this custome entity is Profilt so now I need to create another custom entity called Salary Calculations (photo attached) then creat link one-to-many. In the 2nd photo attached when I creat the link, what should I put in Name and Lable on the right please?
Then do I need to create a field named Salary in the Salary Calculations entity and put the required formula? What will be the formula please? I am sorry that I'm not good at this so hope you will help with detailed instructions please. Thank you very much!

[dreginald]

Thats Right. Please tick the Link Multiple Field Button.

In Salary Calculations, Add Currency Field Salary, Add Salary Date (primarily to capture the month), Total Profit, Total Employees

So the data in Salary Calculations can be Tabulated as below:-

Employee(Assigned User) - Salary Date - Salary - Total Profit - No of Employees

Employee A - 31-08-2028 - $?? (Record 1) - $?? - 5
Employee B - 31-08-2028 - $?? (Record 2) - $?? - 5
.
.

Every month entry has to be made to create salary for the employee for the month or you can write a scheduled workflow to create automatically on the first day of the month.

Now Record One is Salary Calculations needs to be linked to all the bookings of Employee A for the month of August, Record 2 with Employee B for August, and so on..


This can be achieved by writing the following formula in Bookings (workflow with any condition is most ideal)

1. record/findone to find the ID of the record in Salary Calculations that matches the Assigned User, Month of the Salary Date. - please check the syntax in sandbox
2. record/relate the ID at 1 above, with the booking (Lead) please check the syntax in sandbox

In Salary Calculations write formula to work out the salary as below:-

Total Profit = entity/sumrelated('Lead', 'profit'); please check the syntax in sandbox
Salary = Total Profit / 5 X 14%


More Simpler way will be to create fields - Salary (Currency) and No of Users (Integer) in Booking itself. Write formula - Salary=15% of Profit / No of Employees.

Create a Grid Report in Booking Entity, Group By: Month:Created At/Booking Date AND Assigned User and Column Sum:Salary

I would prefer the first method - create a separate entity Salary and get the filtered data from Booking to Salay

[Nth20]

Hi Dreginald, I took the 2nd option. I created a field called Salary in the Booking and write the formular for that field (Salary=profit * 0.14/5) so it will show the salary for each booking. The problem now is how to Group By in the report when the month here can be the month of the departure date OR the month of the return date please? I have 2 date fields, not one. The departure date and the return date and I want the report to take the month of either one of these dates please. For example, passenger A books the one-way tickets in August from New York to London, he will choose the date called Departure date. Passengers B books the tickets also in August but from London back to New York so he will choose the date called Return date. Passenger C books round-trip tickets so he will choose both departure date and return date. So when creating the report, the Month should be taken from these dates, not only one date. Do you have any suggestion please?

[dreginald]

You need to fix any one of these dates, as per your business logic, to consider the salary for the employees

[Nth20]

Can I create another date field called "Salary Date" then it will take the month of either Departure date or return date? What will be the formula for this please?

[dreginald]

Suggest you use Month of either departure or return date to work out the salary for the month.

[Nth20]

I cannot do that because if I use the departure date, those who book the return only will not be counted and if I use the return date, those who book the departure tickets only will not be counted. How about creating report for each month (using the Filters) then put all these reports into one report please? For example, I will create a report for August salary, a report for September, a report for October then is there anyway to put all of these reports in one report?

[Nth20]

How about creating a Report Date and use the following fomular and create report on that report date please? Can you check if the following fomular is correct please?

reportDate = ifThen(
departureDate != null,
departureDate,
returnDate
);