Product level tax rates

Here in Brazil tax rates are quite a nightmare, several variables and specific conditions. That said, we implement for our local customers these approaches, according to complexity of business taxes:

• 1:M relationship between Tax Rates and QLI so Sales Person can select the Tax which better fits the scenario (easier approach)
• Custom Accounting modules, their relationships with Product Catalog and some custom fields in the Product Catalog as well so CRM can calculate the appropriate Tax Rates based on nature of select QLI and Customer Accounting configuration (the powerful approach)

Some other customers like to send the Quotes to ERP and let it to calculate the Tax Rates as it has all the data to process it and then bring back to CRM the calculated/updated Quote with Taxes.

Its possible to add a custom dropdown field to the Product Catalogue (e.g. producttaxrate_c) and then
1) specify text and/or numeric field for the QLI record (e.g. productTaxRate_string_c and productTaxRate_value_c)
2) set them up as calculated fields for QLI , e.g.
 
3) setup Worksheet Columns in the Quote Configuration (in Admin section)

That will make the tax rate  per QLI product possible




The next question is how to include different QLI taxes into the Quote/Invoice printout, specifically in the Quotes footer
Technically the simple solution might be just to add the custom field - aka Grand Tax - for using it in the Quote footer (that might be Quote custom filed) and then to calculate its value from the QLI taxes rates
If you are keen to make it no-coding then, perhaps, that could be done via configuring a logic hook on QLI is saved

André Lopes said:

1:M relationship between Tax Rates and QLI so Sales Person can select the Tax which better fits the scenario (easier approach)

Not sure if I'm understanding correctly, but that is already standard, i.e. when you select a product (QLI) in a Quote, you have the option to select a Tax rate that is held in the Tax rates module.

Dmytro Chupylka said:

Its possible to add a custom dropdown field to the Product Catalogue

That would not be an acceptable solution, because

a) dropdown values would ned to be maintained seperatley instead of only once using the Tax rates module

b) you'd need to change the core functionality and calculations that already use the standard relate field for each QLI that looks up from the Tax rates module.

I believe the 'ideal' solution for us would be be able to select from the Tax rate module within the Product catalogue record.

In order to do this, it seems we need to expose the Tax rate module so it's viewable in Studio, and then create either a Relate field or a full relationship (1:M relationship between Tax Rates and Product Catalogue?)

Install zip with Module Loader then run QuickRepair  enable_TaxRates_in_Studio_v1.zip

Thank you Dmytro Chupylka very kind of you.

So we are now able to ad a custom 'Relate' field and select the Tax rates module within Product Catalogue.

Would it be too much to ask for the logic hook that would select the associated Tax rate when a QLI is added to a Quote? 

Happy to send you some beer money.

Have a great weekend in the meantime!

Hi Tony,

I like the vision that it takes just some beer for Sugar Admin to fuel the drawing of a Sugar logic hook with the Logic Builder no-code tool and in case of no tool in place, trying to set up algorithms via logic hooks may turn into a big beer party for non-developers :)

I'd be happy to help with automation, but let's align expectations first.

AFAIK from documentation, logic hooks in the Sugar platform could be configured to system events such as when creating, editing, and deleting records.
support.sugarcrm.com/.../ 

The Logic Builder allows to leverage solely Sugar platform capabilities to deliver that the code generated requires nothing else but Sugar core to operate - and that means for our case is that configuring doesn't change the nature of the logic hooks in the Sugar platform -
we can configure a logic hook to select the Tax rate for a QLI on the basis of the Product Catalog's Tax rate - on e.g. QLI or other record is saved, not earlier.

In case of a user performs actions via UI - populates QLI's Product field by selecting the record from the Product Catalog, - that is purely UI behavior and has nothing to do with logic hooks since neither saving, editing, nor deleting of the record happened yet -
To adjust client-sided UI behavior, it would need development efforts to set the Tax rate for the QLI immediately on the user selects Product in the interface and e.g. to recalculate the Tax in the Quote's grand total on the fly - prior to the data provided in the form is saved, so that the user could make adjustments

However, if the requirement is not about adjusting UI, and we are looking for the Product's Tax Rate to populate the QLI and the Tax in grand total is recalculated on the user saves the record - to perform that on the server background, - we should be in a position to employ logic hooks and therefore configure the algorithm with Logic Builder.

please let me know what behavior is actually expected (describe the use case)

Nevertheless, I've been able to mix no-code logic hooks with calculated fields to deliver both - to enable tax percentage on the Quote form on Product is selected and at the same time to set Relate field Tax Rate for QLI's on QLI is saved 

check it on the video: https://youtu.be/Q1Oti_UYeoI

to check on the instance. you may login https://sg-example07.demo.sugarcrm.eu/ (admin, asdf) 
logic hooks involved are also deployed in that instance - the logic hooks drawings are the following:

If that works for your case, I will share logic hooks ZIPs

At the same time, the open question is whether it is required to recalculate Tax in grand total along with grand total

hi Dmytro Chupylka

Thanks for your efforts.

I would suggest keeping it simp0le and also not use calculated field in the QLI but instead add a Relate field to the Tax rates module same as we did for the Catalogue module.

I have logged into your demo instance and added this Relate field to the QLI and the Quotes layout (can't seem to change it's position to be after Line item total).

So the logic hook only needs to lookup the Tax rate when a product is added into Quote QLI from the matching product catalogue entry.

Then all that remains is to adjust the calculated fields so as they take the Tax rate Relate field into consideration, which we can do.

Hope that makes sense?

Hi Tony Romano

Sorry for the delay with the reply

Below is the logic hook that sets up the Tax rate for the QLI based on a Product specified for the QLI 

Please note that Sugar logic hooks run on server-side actions, inc data entered is being saved to the database, so the configured logic hook will populate Tax Rate Related with product catalogue entry's Tax rate on QLI is submitted for saving - e.g. when the checkbox for the QLI grid line is clicked by the user:


If using logic hooks works for the case, here is the logic hook implementation drawing :




and ZIP file - to deploy the logic hook for the Sugar instance with Module Loader:
z20220705_flowchart_lb62c42db03e09e6_13040435.zip

Please note that as the drawing shows, the logic hook custom configuration sticks to the custom fields' names 

Therefore, the following conditions should be met for that logic hook proper functioning:
1) the custom Relate field for Tax Rate in the Product Catalogue should be named as "tax_rate_c" (label not important)
2) the custom Relate field for Tax Rate in the QLI  should be named as "tax_rate_related_c" (label not important)

I've deployed the logic hook implementation for  https://sg-example07.demo.sugarcrm.eu/ (admin, asdf) so that we could try it out -  also I could make adjustments to the logic hook configuration if necessary

hi Dmytro Chupylka
apologies for late reply, I had a little break

It seems the demo instance no longer works.

In the meantime, on reading I notice your logic hook is 'on save' and wondered if better to use 'before save'?