Import Business Rules From Spreadsheet

Hi John,

First of all, I can tell from your question that you are already aware that the product doesn't do this natively. However, the need to manage large lookup tables for usage in process defs is real, and for that reason, I encourage you to post a request for it here: https://sugarclub.sugarcrm.com/explore/sell/i/product-suggestions. I'd love to see large lookup tables managed in a custom module which a business rule could look up - that way we get the normal import/mass update functions to manage it.

There are two ways I've come across of dealing with this based upon your risk tolerance / comfort with Excel:

1. The .pbr file used by the export/import functions in Process Business Rules module are designed to work in a "make a sandbox copy of your whole environment, change+test there, export from sandbox and import to production" style process. There are a range of caveats why this is the only supported process, and the warnings in the system explain why that is so.
   
   However, if you open up the .pbr file (I would suggest putting in 3 rows of ZipCode-to-LeadAssignee into your business rule and then export it), you'll find that it's an ordinary json object, and all the magic happens in project->rst_source_definition. The structure of this value is well defined, and after some header content, you'll see each line of the business rule in it, along with some content around it.
   
   This means that if you choose to maintain the mapping of ZIPCode-to-LeadAssignee in an Excel spreadsheet, you can add another column to that sheet which uses CONCAT to format the first two columns and produce a line which would be used inside the rst_source_definition value.
   
   Then a master CONCAT which joins all of that together with the rest of the content you got from your exported .pbr format.
   
   This approach isn't for the faint-hearted, and requires you to have very carefully formatted CONCAT statements. If you get it wrong, Sugar will not let you import the file. But at the end of the day, all the effort is done once (to begin with), and thereafter, your maintenance process would be to update the mappings in your spreadsheet, and then do a big copy and paste (probably from a second sheet) into a .pbr file for importing.
   
   
2. A potentially simpler option revolves around the same principle, but using a dropdown list as a proxy. I've used this one specifically for lead routing based on postcodes, but as I'm from Australia, I only had several hundred rows to deal with .. not sure how it'll go with thousands
   
   In this case, you maintain your mapping in a spreadsheet, use a simpler CONCAT in Excel to create what each row of the dropdown list looks like, and use your favourite means to get that into a language file in Sugar.
   
   A custom text field uses SugarLogic to lookup the Lead Assignee via the getDropdownValue function (with the zip code as a key), then your business rule just uses the data in this custom field to set the native Assigned To.

Hope this helps as a starting point!

Hi John,

First of all, I can tell from your question that you are already aware that the product doesn't do this natively. However, the need to manage large lookup tables for usage in process defs is real, and for that reason, I encourage you to post a request for it here: https://sugarclub.sugarcrm.com/explore/sell/i/product-suggestions. I'd love to see large lookup tables managed in a custom module which a business rule could look up - that way we get the normal import/mass update functions to manage it.

There are two ways I've come across of dealing with this based upon your risk tolerance / comfort with Excel:

1. The .pbr file used by the export/import functions in Process Business Rules module are designed to work in a "make a sandbox copy of your whole environment, change+test there, export from sandbox and import to production" style process. There are a range of caveats why this is the only supported process, and the warnings in the system explain why that is so.
   
   However, if you open up the .pbr file (I would suggest putting in 3 rows of ZipCode-to-LeadAssignee into your business rule and then export it), you'll find that it's an ordinary json object, and all the magic happens in project->rst_source_definition. The structure of this value is well defined, and after some header content, you'll see each line of the business rule in it, along with some content around it.
   
   This means that if you choose to maintain the mapping of ZIPCode-to-LeadAssignee in an Excel spreadsheet, you can add another column to that sheet which uses CONCAT to format the first two columns and produce a line which would be used inside the rst_source_definition value.
   
   Then a master CONCAT which joins all of that together with the rest of the content you got from your exported .pbr format.
   
   This approach isn't for the faint-hearted, and requires you to have very carefully formatted CONCAT statements. If you get it wrong, Sugar will not let you import the file. But at the end of the day, all the effort is done once (to begin with), and thereafter, your maintenance process would be to update the mappings in your spreadsheet, and then do a big copy and paste (probably from a second sheet) into a .pbr file for importing.
   
   
2. A potentially simpler option revolves around the same principle, but using a dropdown list as a proxy. I've used this one specifically for lead routing based on postcodes, but as I'm from Australia, I only had several hundred rows to deal with .. not sure how it'll go with thousands
   
   In this case, you maintain your mapping in a spreadsheet, use a simpler CONCAT in Excel to create what each row of the dropdown list looks like, and use your favourite means to get that into a language file in Sugar.
   
   A custom text field uses SugarLogic to lookup the Lead Assignee via the getDropdownValue function (with the zip code as a key), then your business rule just uses the data in this custom field to set the native Assigned To.

Hope this helps as a starting point!

I was wondering if this is still the only method or any other ideas like an API import script?