Problem Solving | Salesforce Project Implementation

'I am happy to help you', I replied to a post on a Salesforce group. A group member posted a roadblock in his recent Salesforce project implementation, requesting for help. All I saw was that there is a problem to be solved. I replied even though I did not have a clear idea about its solution. After a few email exchanges, we scheduled our first zoom meeting to understand the project and requirements.

It was an e-commerce business fresh Salesforce implementation. The business model looked simple, however, implementing the Sales cloud in Salesforce turned out to be tricky. The products available for order on the client's website were different from actual products to be shipped. Each product has a corresponding item or bundle of items associated.

'Crate 1' will be shipped when 'Single Crate' is ordered or 'Crate 1' to 'Crate 6' will be sent together when '6 Month Crate' is ordered or each of these Crates will be shipped monthly in a sequence when 'Subscription: 6 Month' is ordered. The client asked for a forecast of Crates to be shipped for each confirmed order by dates. The forecast report will help in maintaining a healthy inventory of Crates, manage orders, and also provide shipping dates. To ensure that I understood the requirements clearly, I created report formats on an excel sheet. The formats were agreed upon during our call.

Next step was to configure them on the Salesforce platform. Getting back to Salesforce, it all seems to be an easy task to accomplish. The first thought was to create a custom child 'Crates' object with Products being the master, thereafter, create custom reports to fulfill the requirements. Would there be a post asking for help, had it been so simple a solution? I soon found out that custom objects cannot have a master-details relationship with 'Products' object.

I became curious to find a way to solve the problem. Began my search on Google and SFDC communities for answers; however, I did not come across anything that could provide a complete solution. Meanwhile, I remembered 'product quantity schedule' and 'revenue schedule' concepts from my admin certification study course. Part of the problem was solved by enabling quantity scheduling and revenue scheduling in my dev org.

I could now set the monthly quantity schedules for subscription products, which automatically creates monthly schedules when added to an 'Opportunity' record.

I created a new custom object 'Crate' for reporting crates for each product sold. 'Crate' object cannot have a direct lookup relationship with the 'Product' object because each product has one or more crates associated with it. However, crates and products have one thing in common, its their Product Category. I created a 'Product Category' custom object linked to the 'Product' object by a lookup relationship and the 'Crate' object as its child. I now have quite a number of objects which need to be in the report and since custom reports cannot have junction objects (eg: Opportunity Product), it has to be done differently. HOW?

Don't forget to check out: Standard and Custom Percentage Calculator in Salesforce Lightning Component

I realized I now have to use process automation components but they cannot produce reports. Therefore, I created another custom object 'Crates Schedule' which has all the required fields needed on the report. It has lookup relations to Accounts, Opportunity, Product, Crate, and Product Category objects. The data in 'Crates Schedule' gets populated via Process builder and Flows from all the related objects. I created an auto launched flow which is be triggered by a Process Builder.

Step 1: Loop eachOppProduct

The flow loops through all opportunity product records passed on by the Process builder in a record collection variable.

Step 2: Assignment assignScheduleDate

Assigns Opportunity product date to a new date variable. This date will be used later for calculating shipping dates for each crate item

Step 3: getAllCrates

It gets all the corresponding crates based on two conditions. First: product and crate should match on Product Category. Second: the product's number of crates should be greater than the Sort order number in Crate. This gives us all the Crates for that product stored in a crate record collection variable.

Step 4: Loop eachCrate

The flow now enters another loop which is on all the crates (crateCollection_flow variable) found in the previous step.

Step 5: Decision quantityScheduleCheck

Checks if the product has quantity schedule enabled. We do need two separate assignments, therefore, two decision outcomes are created - ScheduleEnabled and ScheduleDisabled

Step 6: Assignment AssignCrateScheduleRecord

All the fields of the Crate Schedule Record variable are assigned when a product has quantity schedule enabled.

Assignment incScheduleDate

Increments Opportunity product date variable (step2) by one month. I used a formula variable to achieve the date increment. This is mainly for the crate's monthly shipment frequency.

Assigment AssignCreateNoScheduleRecord

If the opportunity product is not a subscription product, it assigns all the fields of the Crate Schedule Record variable. The difference is the shipment date, which will be the same for a crate in this case.

Assignment AddtoCrateScheduleCollection_flow

The Crate Schedule Record from the previous step is now added to a collection variable within the loop. The is to preserve the data for each loop since the Crates Schedule record variable will be overridden during each iteration of the loop.

This whole process keeps repeating for all the Crates for each Opportunity Product and finally Create Records creates all the rows using the Crate Schedule Record collection variable.

Process Builder

Process builder triggers the flow, passing the Opportunity Products records for a 'Closed Won' Opportunities.

Check out an amazing Salesforce Video tutorial here: Create Custom Auto Number Field With Your Criteria Using Flows | Salesforce

The data populated in Crate Schedules are used for forecasting reporting.

The problem was solved and the client was happy with the results. I finally had a sense of achievement by solving this problem. With the 'Can do' attitude and 'Never Give Up' approach helped me win.