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




        • Recent Topics

        • Custom Function for Estimates

          Hey everyone, I was wondering if there was a way to automate the Subject of an estimate whenever one is created or edited: * the green box using following infos: * Customer Name and Estimate Date. My Goal is to change the Subject to have this format "<MyFirm>-Estimate

        • Search in module lists has detiorated

          Every module has a problem with the search function :-/

        • YouTube Live #1: AI-powered agreement management with Zia and Zoho Sign

          Hi there! We're excited to announce Zoho Sign’s first YouTube live series, where you can catch the latest updates and interact with our Zoho Sign experts, pose questions, and discover lesser-known features. We're starting off by riding the AI wave in

        • Search in module lists has detiorated

          Every module has a problem with the search function :-/

        • Add Zoho Forms to Zoho CRM Plus bundle

          Great Zoho apps like CRM and Desk have very limited form builders when it comes to form and field rules, design, integration and deployment options. Many of my clients who use Zoho CRM Plus often hit limitations with the built in forms in CRM or Desk and are then disappointed to hear that they have to additionally pay for Zoho Forms to get all these great forms functionalities. Please consider adding Zoho Forms in the Zoho CRM Plus bundle. Best regards, Mladen Svraka Zoho Certified Consultant and

        • Bigin: filter Contacts by Company fields

          Hello, I was wondering if there's a way to filter the contacts based on a field belonging to their company. I.e.: - filter contacts by Company Annual Revenue field - filter contacts by Company Employee No. field In case this is not possibile, what workaround

        • Has Zoho changed the way it searches Items?

          Right now all of our searches have broken and we can no longer search using the SKU or alias. It was fine last night and we came in this morning to broken.....this is impacting our operations now.

        • Refunds do not export from Shopify, Amazon and Esty to Zoho. And then do not go from Zoho inventory to Quickbooks.

          I have a huge hole in my accounts from refunds and the lack of synchronisation between shopify , Amazon and Etsy to Zoho ( i.e when I process a refund on shopify/ Amazon or Etsy it does not come through to Zoho) and then if I process a manual credit note/

        • CRM->INVENTORY, sync products as composite items

          We have a product team working in the CRM, as it’s more convenient than using Books or Inventory—especially with features like Blueprints being available. Once a product reaches a certain stage, it needs to become visible in Inventory. To achieve this,

        • Zoho Calendar not working since a few days

          Hey there, first off a minor thing, since I just tried to enable the Calendar after reading this in another topic (there was no setting for this though) : For some reason my current session is showing me based in New York - I'm in Germany, not using a

        • Monthly timesheet, consolidation of time by project

          I have time logs for various jobs for project. Is it possible to consolidate the time spent for each job, when I am generating a timesheet for a month? I am getting the entries of jobs done on each day when I generate a timesheet for a month For example

        • Sending emails from an outlook account

          Hi, I need to know if it's possible to send automatic emails from an Outlook account configured in Zoho CRM and, if so, how I can accomplish that. To give you some context, I set up a domain and created a function that generates PDF files to be sent later

        • Struggling with stock management in Zoho CRM – is Zoho Inventory the solution?

          My biggest pain point today with Zoho is inventory management. I run a retail business and reliable stock management is absolutely critical. Obviously, I need this inventory to be visible inside the CRM. At first, I tried handling it through custom modules

        • can't login Kiosk URGENT

          already try, can't login pls help to support. thanks.

        • 【Zoho CRM】CRM for Everyoneに関するアップデート：関連データ機能

          ユーザーの皆さま、こんにちは。コミュニティチームの中野です。 今回は「Zoho CRM アップデート情報」の中から、CRM for Everyoneの新機能「関連データ機能」をご紹介します。 関連データ機能は、あるタブのデータを別のタブに柔軟に関連付け、異なるタブで管理されている情報を1か所にまとめて表示できます。 たとえば、組織タブとチームタブのデータを関連付けることで、必要な情報に効率よくアクセスでき、顧客理解を深めながら他チームとの連携もスムーズに行えます。 目次 1. 関連データの設定方法

        • Profit / margins on Sales orders / Invoices / Estimates

          When we select an SKU or item name in any of these documents, much info such as invoice.line_items.rate is pulled from the item & filled into the document being worked on. If we had another lineItem DB field (hidden) auto filled at the same time: invoice.line_items.purchase_rate

        • Inventory to Xero Invocie Sync Issues

          Has anyone had an issue with Invoices not syncing to Xero. It seems to be an issue when there is VAT on a shipping cost, but I cannot be 100% as the error is vague: "Unable to export Invoice 'INV-000053' as the account mapped with some items does not

        • How to activate RFQ? What if a price list has ladder price for items?

          Where can I find the option to activate request for quotation? How does it work? If the item has ladder price, does it gets calculated depending on how many items are in the cart?

        • Can't join canal Developers Zoho User

          Hello, I received an invitation to join this channel, but I get an error when I try to join it, and I get the same error when I go to the Zoho Cliq interface > Search for a channel. Is this because I don't have a license linked to this email address?

        • Desk Email reply - set default font / use custom font

          Hello, in our e-mails, which we send to our customers, a certain font must be used (Corporate Design): Segoe UI https://en.wikipedia.org/wiki/Segoe#Segoe_UI How can this be included? How can this be set as the default font to ensure that this font is

        • PDF Templates - Checkbox Borders

          Is there a way to remove the border of a radio/checkbox on a PDF? I'd like to use the function of checkbox but if there's no easy way to remove the border (the PDF form already has a rectangle so it gets cluttered), then I'm forced to create a single

        • Settings Icon No Longer in ZOHO Desk?

          In ZOHO desk, there has been a gear icon for settings. as of yesterday, it is no longer there. I showed up briefly this morning but is gone again. Anybody else experiecing this?

        • Tip #43 - Track, Review, and Analyze Your Assist Sessions with Reports-'Insider Insights'

          Did you know you can generate detailed reports for both remote support sessions and unattended access sessions in Zoho Assist? This makes it easy to monitor technician activity, measure efficiency, and review customer interactions. Let us now take a closer

        • Function #20: Custom calculation in item table of invoices

          When you create an invoice in Zoho Books, the 'Amount' of a line item is typically calculated as the product of the "Quantity" and the "Rate" of the item. For instance, if an item has a sales rate of $50 and a quantity of 5 is sold, then the amount would

        • Simplified Call Logging

          Our organization would like to start logging calls in our CRM; however, with 13 fields that can't be removed, our team is finding it extremely cumbersome. For our use case, we only need to record that a call happened theirfor would only need the following

        • Sub form doesn't as formula field

          Is it possible to get formula field in sub form in futures?

        • Week date range in pivot table

          Hello, I need to create a report that breakouts the data by week.  I am using the pivot table report, and breaking out the date by week, however the date is displayed as 'Week 1 2014' format.  Is there anyway to get the actual dates in there? ex. 1/6/2014-1/12/2014 Thanks,

        • How do I get Status History data of my Projects?

          I want to build a table in Zoho Analytics that Groups by Date, when Projects entered a certain status. I cannot find Status History or any such useful data available in the Setup of my Data Source sync. Please advise how I can achieve this?

        • Is it possible to hide fields in a Subform?

          Since layout rules cannot be used with Subforms, is there another way, or is it even possible, to hide fields in a subform based on a picklist fields within said subform? For example, if the Service Provided is Internet, then I do not want to see the

        • Weekly Tips :Instantly find what you need with Attachment Viewer

          Your inbox must be packed with project emails, shared notes, and scattered attachments. You are looking for one specific file—a presentation slide or maybe a media clip from a team update—but don’t want to dig through endless email threads or switch between

        • Missing Zoho Desk integration option for form workflows

          According to the help page "Configure Zoho Desk integration in form workflows" we should be able to select Zoho Desk as an integration target but when I open the integrations list then Zoho Desk is not being listed in it. We are on the Premium plan which should already support Zoho Desk integrations.

        • Gantt for 2 or more projects

          Hello, I'm trying the free version of your produtc. It is veryyy good!!!! I don't know if in the Standard plan, I can overview a Gantt Graph for 2 or more Projects Milestone. This would be very helpfull for managing teams and taking decisions about who I will assign a task to. In the paid plan Do I have this possibility? Thank you.

        • Integrating a Zoho Project Gantt Chart into Reports

          Is is possible to integrate a Zoho Project Gantt Chart into a Zoho Report Dashboard. I am in the process of creating Project Status Dashboards for the projects that we track in Zoho Projects and I would like to incorporate the gantt chart within Reports.  Please let me know! Thanks

        • ZOHO BOOKS - EXCESSIVELY SLOW TODAY

          Dear Zoho Books This is not the first time but it seems to be 3 times per week now that the system is extremely slow. I work on Zoho Books 95% of my day so this is very frustrating. Zoho you need to do something about this. I have had my IT guy check

        • Displaying related quotes in sales order and back

          Hi, My colleague liked to see to which sales orders, the quote has been converted. Quote shows Invoices, but not SO. Same, they would like to see the quotes in the sales order, as they can see invoices, packages, shipment, How can we achieve this ? Thank

        • Tip of the Week #71–Auto-move incoming messages to the right inboxes with keywords

          We all know that customer-facing teams, especially your sales and support teams, can’t afford to miss even a single customer conversation. But sometimes, sales queries or support requests can easily get lost in a crowded inbox or even end up in the wrong

        • Clearing Fields using MACROS?

          How would I go about clearing a follow-up field date from my deals? Currently I cannot set the new value as an empty box.

        • Migrating a Zoho Forms form into Zoho Creator

          Hi, How can I migrate my Zoho Forms form into Zoho Creator? Thanks. Truly, Emad

        • Is there any way to recall an email sent using Zoho CRM?

          If an email is sent using Zoho Mail, there is a recall option/functionality that is available to the sender. Is there any way to recall an email if it was sent using Zoho CRM? I can't seem to find that option. Any help would be appreciated.

        • Problem with reports due to "Connected" items change - Yes this IS a problem

          Now that the change has been made to use "connected" items I can no longer run the reporting I need in CRM. I should be able to start with Deals as the parent, connect down to the Account (Account_Name) on the deal as the child, then to any child items

        • Next Page