Function 58: Custom calculation in item table of invoices (2 fields)

Function 58: Custom calculation in item table of invoices (2 fields)



Hello everyone, and welcome back to our series!

In Zoho Books, the Item Amount in invoices is calculated by multiplying the Quantity and Rate fields. Previously, we shared a function to include a custom field in this calculation.  Today, we are taking it a step further to make it even more versatile for various business needs. Instead of just one custom field, you can include two custom fields to determine the Item Amount.

Item Amount = Custom Field 1 × Custom Field 2 × Rate × Quantity

Consider Mr. John Hector, who runs a steel production plant. He processes orders for steel sheets according to their dimensions. For accurate billing, he can utilize today's function. When creating the invoice, John can enter the length and width of each steel sheet into the custom fields and specify the number of sheets ordered, and the rate per square foot in the default fields. The function will automatically calculate the Item Amount using this formula:

Item Amount = Length × Width × Quantity × Rate



Prerequisites: 

1. Create a Connection named "zbooks" to successfully execute the function. You can watch the GIF attached below to know how to create the connection. 



2. Create the third (Length) and fourth (Width) variables as item-level custom fields (Decimal type) to be displayed in the sales transactions.



3. Similarly, create another custom field called " Unit Rate" to be displayed in the item table. The purpose of this custom field is to store the original sales rate of the line item. 



Custom Function:

Navigate to Settings -> Automation -> Workflow Actions -> Custom Functions -> +New Custom Function -> Add the function code from this GitHub link -> Save.

Workflow Rule:  

Go to Settings -> Automation -> Workflow Rules -> +New Workflow Rule and set up the workflow rule as shown below:-





In the final step of this workflow rule, associate the custom function you created and then hit Save.

Here's how the function will work:

The custom function will be executed once the invoice is saved. Initially, it copies the original sales rate to the Unit Rate (Custom) field. Then, it multiplies Length × Width × Unit Rate and populates the result in the Rate (Default) field. Following this, Zoho Books calculates Rate (Default field) × Quantity and fills the result in the Amount field.

Note: If you wish to hide the "Rate" - default field at the template level, go to Settings > Customisation > PDF Templates > Invoices > Edit > Table > Disable the checkbox for Rate field > Save. Now, the Custom Field 1 (Length), Custom Field 2 (Width), Unit Rate (Custom Field), Quantity and the Amount field will be displayed on the invoices.



If you have to incorporate two extra values into your Item Amount calculation, this function is perfect for you. For further customization or assistance, please reach out to our Support team at support[at]zohobooks[dot]com. We're here to help!

Regards,
Shireen Farhana
Zoho Cares

    Nederlandse Hulpbronnen


      • Recent Topics

      • Holding Shift to keep selected tickets

        It is annoying trying to change the category of tickets and then closing them. You have to select them one by one, no way to 'hold down left click and drag your mouse down to select multiple'. Once you have selected them and you change the category, you
      • Formula Module how to convert to percentage

        Hello There, I have create a formula field and i want the outcome to be in percentage how do i do that This is my formula ${Deals.Forecast Revenue Per Year}/${Deals.Annual Processing Volume} I have try ${Deals.Forecast Revenue Per Year}/${Deals.Annual
      • Existing subform data is being changed when new subform entries are added

        I'm having trouble with existing subform data being changed when new subform entries are created. I have the following setup to track registrations for a girl scout troop: Main Form: Child Subform: Registrations The data are a one-to-many relationship where each Child record has many Registrations (new Registration will be created for each year the child is in the troop.) Per the instructions, I have created the subfom, added it to the main form, gone back to the subform and created the bi-directional
      • How to add a contact ID to contacts I'm importing with migration

        New user, business owner here, not a data specialist. I'm attempting to migrate import data from my old CRM, Daylite, via a .csv file. I feel like I've mapped the columns in the .csv file, however when I click save and next, I get a message saying the
      • FSM Job Sheets data to CRM

        Hello, is there anyway of getting the data collected in the job sheets into the CRM. We are collecting inspection data which we were using zoho forms for but the job sheets in fsm seem like a lot better way to collect it but we need to be able to get
      • Footer in PDF template doesn't stay at the bottom of the page

        When setting up a PDF template there is an option for a header / footer. The header stays at the top of the page however the footer does not. It appears the footer actually serves no purpose as it seems to rise up to underneath the header (see image). Is there a way to lock the footer to the bottom of the page? Otherwise what is the point of it? Image demonstrating what I mean. https://ibb.co/cJY1xZ4
      • Hiring Staff Accountant in San Diego with ZohoBooks Experience

        Hello, We are a San Diego, CA company looking for an experienced Staff accountant or controller that has ZohoBooks experience for a full-time position. Please reach out if interested. Best, Ben
      • Third party apps for my mail

        Hello im new here and i have a very important issue. A third party company uses one of my emails to send invoices to our customers. My problem is that cannot connect to zoho mail server. my imap settings are corrent imappro.zoho.eu 993 smtppto.zoho.eu
      • Outage?

        Is there an outage? Everytime I try to access Zoho FSM (web, as admin) I get a blank screen.
      • Zoho chat module for prestashop doesn't work

        Zoho chat module for prestashop doesn't work
      • IMPORTRANGE Spreadsheets

        I am trying to importrange a spreadsheet into another, but an error keep appearing saying NOT LINKED. Noting that I've linked it. I've tried to publish both sheets and re-apply the formula many times but nothing is working. I don't know what can I do
      • How to create Comparison across Period chart in a dashboard?

        Hi all How can I create this chart in a custom dashboard? The issue for me is that this chart is very small. The CRM module (unlike Projects module) has no ability to expand a chart. I want to make it larger, but also want to include it in a custom Forecast
      • Trouble fetching custom fields from Zoho Booking

        I'm following the instructions from here : https://help.zoho.com/portal/en/kb/flow/user-guide/app-specific-documentation/articles/zoho-bookings#1_Problem_with_accessing_custom_fields But I can't get this to work. Here is the result of a test booking :
      • How to change an employee mail id

        Hi, Does the administrator have the rights to edit an  employees mail id. 
      • Tip of the Week #56 – Analyze performance with analytics reports.

        Wondering how well your team is managing communications in shared inboxes? It’s time to stop guessing and start knowing — with Analytics Reports! As an admin, you get a broader view of your team's performance by tracking important metrics such as response
      • Zoho Sprints is now AI empowered

        Enhance the quality of your work with the power of generative AI tools in Zoho Sprints. These tools help you write precise descriptions, summarize content, analyze sentiment and tone, and translate content into your preferred language, and write and explain
      • Zoho CRM's V8 APIs are here!

        Hello everyone!!! We hope you are all doing well. Announcing Zoho CRM's V8 APIs! Packed with powerful new features to supercharge your developer experience. Let us take a look at what's new in V8 APIs: Get Related Records Count of a Record API: Ever wondered
      • Threads view for email sent from Cases module in CRM

        Hello all, Following the previous announcement regarding the support for the Send Email functionality from within the Cases module, the Email thread view is now available for emails sent from the Cases module. This enhancement is for better readability
      • Custom view placeholders

        Hi all, On some occasions it would be great to have placeholders setting up a custom view. Example in our case we have a field for a year. We would like to have a placeholder like $.{CurrentYear} that will insert the current year 2025 e.g. Now we have
      • Languages in Zobot

        Hello, I have found a list of supported languages for the Zobot. The information specifies the languages are supported in the following 3 features: SalesIQ supports 34 different languages in the following features. Resources (Articles, FAQs, Small Talks)
      • USA Military addresses

        When we have a client with a US military address adding them to the CRM, or having them fill in a form is a problem. Zoho Forms and CRM doesn't seem accommodate them correctly. It doesn't make sense for me to have to create a secondary data model for
      • Parentheses in System Path

        Zoho WorkDrive includes a mandatory parenthesis with the organization name in the desktop sync client. This adds parens to the system path. Many command-line applications do not allow for the use of parenthesis, so if you want to use a file saved on WorkDrive in a command line you cannot. Most major document syncing platforms do not allow parenthesis for this reason.
      • Match Transaction > Filter | Allow wildcards in search

        The Filter function is useful for narrowing down to a specific set of transactions. It would be useful to have the ability to use wildcards in search. e.g. customer name "starts with" or " * " as a placeholder. e.g. "National * " instead of "National
      • Can your customer support speak every customer’s language?

        In today’s world, talking to your customers in their language is not a nice-to-have. It’s a must. It's one of the reasons that most businesses have trouble going global: Language barriers get in the way. Say, a customer from another part of the world
      • Sub accounts in Balance Sheet - specifically Assets

        I've added in a new account, IP Developed, under Intangible Assets. When I run the balance sheet report, it shows on its own without a header, just tacked on the bottom of all the other assets. I'd like it to show under Long Term Assets or Other Assets.
      • Assign default Location + warehouse to Customer / Vendor

        Hello there. With the introduction of Locations I'm having to enter the warehouse for every single transaction which is getting really tiring and causes errors (easy to fix, but still). Does anybody know if there's a way to assign a default Location and
      • Check Sales Order for Duplicate Line Items by SKU

        It would be really nice if Zoho Books offered at least some of the features that the 30 year old software we migrated from did. When entering a Sales Order having some kind of warning pop-up if you try to enter the same item again would be very helpful.
      • Fifth Insight - Maximize the role of Departments

        The Wheels of Ticketing - Desk Stories Maximize the role of Departments What are Departments? Departments represent the various business divisions within your organization, each serving distinct functions that align with your products, geographical locations,
      • hey

        help me in zoho payroll
      • Kanban Deals not showing everything

        I have a deals (I call it Opportunities) module in CRM that shows various components of the opp. I'm trying to have a checkbox show up for the end user to be able to toggle it if they want an email to be sent when the change stages (in this example, it's
      • Assign Vendors and customers specific branches ( location )

        Hi Team, Is it possible to assign specific customers/vendors to specific branches.
      • Currency Data Type Issue

        Hi, I'm running into an issue with the currency data type conversion with the pipeline tool inside Zoho Analytics. Basically, when the table is added to the pipeline, the columns with currency data type will be converted to text type. I assume it's because
      • VAT in Retainer Invoice -UAE

        It's currently not possible to add VAT to Retainer Invoices. UAE TAX LAW however makes VAT mandatory on each retainer invoice in UAE So basically, what i'm saying is that if you don't allow us to add VAT to Retainer Invoices, than the whole Retainer Invoices
      • Add SalesPerson PlaceHolder in Notification

        We have the ability to include only Created By But for my use case I need the sales Person in the Email as well And the subtotal as well Please give us access to Sales Person and Subtotal Placeholders
      • Using Equity Contributions To Fund Investments/Expenses?

        Hello! My partner and I just transferred over to Zoho Books and are trying to figure out how to adapt it to our business model.  We currently fund the company as individuals and utilize those funds to:  1) Cover OPEX 2) Invest in companies.  - I recorded
      • Showing ALL missing data

        Hi, When I create a chart, I noticed that even when enabling the "show missing values" option, if, let's say, the current time period will not show as "0" on the chart if there's no data (rows) in the table. For example, I have this data: Week 1: 0 rows
      • Using tickets to train Zia

        Hi Team, I would like to know if there is any way that Zia can also learn from previous tickets in addition to the articles from the knowledge base. Since we have most of our knowledge curerently in the tickets and that this is hard to combine into a
      • How to automatically go to the bottom of a Zoho spreadsheet when opening the file

        Is there a way to open a Zoho spreadsheet and have it automatically go to the either the bottom row that contains any sort of text or formula?   It would work fine if it simply went to the "lowest" row for Column A that contains information.  I do see you can use the URL to go to a specific cell (and probably a named cell as well) but I haven't found a way to do what I'm asking.  The spreadsheets are fairly dynamic and information is added to them frequently.   Another thing that would work would
      • Add more than 7 sender addresses in campaigns

        I need to add at least 15 sender email addresses but am currently limited to 7. Please can you increase. Thank you
      • How to avoid outgoing emails from Zoho CRM from being treated as SPAM/Junk when arriving in an Outlook Inbox?

        I just upgraded to Zoho CRM paid version so I could use the Mass EMail feature using a simple email template I prepared. When testing this feature out, I sent emails to my Yahoo, GMail and to an Outlook EMail Inboxes. Yahoo & GMail received the messages
      • Next Page