Home
Zoho Book

How to Update Estimate Custom Field in Zoho Books using custom function

Scenario :- In Zoho books,you want to add a  Custom field called "Gross Profit %" to the Estimate Module.The Gross Profit % will be calculated using the formula:

Steps to Calculate:-

1.       Add up the Sum of each item cost x the quantity

2.       Add up the sum of each item sell x the quantity

Gross Profit % = (Total Sales - Total Costs) / Total Sales * 100;

 

Example:  If the total costs of all items (multiplied by their quantities) is $100, and the total sales (multiplied by their quantities is $250, the GP is 60%

Steps to Add a Custom Field:

  1. Go to Settings.
  2. Under the Preferences tab, select General.
  3. Choose the module (Estimates) where you want to add the new field.
  4. Click on "+ New Custom Field", name it "Gross Profit %", and choose Percentage as the data type.
  5. Save the changes.


Please find the below deluge Code:-

estimateID = estimate.get("estimate_id");

estimatedate = estimate.get("date").toDate();

organizationID = organization.get("organization_id");

get_quote = zoho.books.getRecordsByID("Estimates",organizationID,estimateID,"zbooks");

estimate_v = get_quote.getJSON("estimate");

lis = List();

total_sales = 0;

total_cost = 0;

for each  it in estimate_v.getJSON("line_items")

{

mp = Map();

getItems = zoho.books.getRecordsByID("items",organizationID,it.getJSON("item_id"),"zbooks");

mp.put("item_id",ifnull(it.getJSON("item_id"),""));

mp.put("name",ifnull(it.getJSON("name"),""));

mp.put("description",ifnull(it.getJSON("description"),""));

mp.put("rate",ifnull(it.getJSON("rate"),""));

mp.put("unit",ifnull(it.getJSON("unit"),""));

mp.put("quantity",ifnull(it.getJSON("quantity"),""));

mp.put("pricebook_id",ifnull(it.getJSON("pricebook_id"),""));

mp.put("discount_amount",ifnull(it.getJSON("discount_amount"),""));

mp.put("discount",ifnull(it.getJSON("discount"),""));

mp.put("tax_id",ifnull(it.getJSON("tax_id"),""));

//calculate GP% value

total_cost = total_cost + ifnull(getItems.getJSON("item").get("purchase_rate"),0) * ifnull(it.getJSON("quantity"),0);

total_sales = total_sales + ifnull(getItems.getJSON("item").get("sales_rate"),0) * ifnull(it.getJSON("quantity"),0);

lis.add(mp);

}

mape = Map();

info "total_cost :" + total_cost;

info "total_sales:" + total_sales;

gp = (total_sales - total_cost) / total_sales * 100;

info "Calculate GP%" + gp;

mape.put("custom_fields",{{"api_name":"cf_gross_profit","value":ifnull(gp,"")}});

mape.put("line_items",lis);

resp = zoho.books.updateRecord("estimates",organizationID,estimateID,mape,"zbooks");

info resp;


#Alternative Way to Update Custom Fields:

Book = Map();

record = List();

update = Map();

update.put("api_name","cf_gross_profit");

update.put("value",ifnull(gp,""));

record.add(update);

Book.put("custom_fields",record);

resp = zoho.books.updateRecord("estimates",organizationID,estimateID,Book,"zbooks");

info resp;





Post a Comment