No transactional integrity when saving multiple modles

  • 2
  • Problem
  • Updated 4 years ago
  • Solved
  • (Edited)
When a page contains multiple models to be saved, if a failure to save a record on a secondary object fails, the page enters an unusable state and a partial save occurs leaving inconsistent data artifacts.

To reproduce the problem, complete the following steps:

1) Using SkuidCRM, add a validation rule to Opportunity Products as follows:
UnitPrice <= 10
2) Go to Opportunities Tab
3) Choose the global action "Create New Opportunity via Redirect"
4) Input valid values for Opportunity Name, Account, Stage & Close Date
5) Choose a Price Book
6) Add a new product row (if one didn't automatically get added specifying a valid value for all fields making sure to set Sales Price to a value less than or equal to 10
7) Click "Save"

Actual Result:
1) Error message indicating Unit Price must be greater than 10 is displayed
2) Change Sales Price to a number greater than 10
3) Click "Save"
4) Error message "Invalid Reference ID: OPportunity ID" is displayed
5) If you check the opportunities, the opportunity you created was saved to the database (in Step #7 above) with no line items

Expected Result:
1) After clicking "Save" the first time, error message about Unit Price validation should appear but Opportunity should not be saved to database
2) After adjusting Sales Price to greater than 10, Opportunity & Line Item should successfully save to database

What seems to be occurring:
1) There is currently no transactional control around model saves when multiple models are involved
2) The opportunity saved and skuid reloaded the model on the client with the SFDC Record ID that was assigned prior to proceeding to attempting the Line Item Saves
3) The Line Item Model failed and now contains a bad reference to the temporary "Record ID" that Skuid gave to the new row in the Opportunity model prior to saving the opportunity record. This reference was not updated when the Opportunity was successfully saved above

We have a fairly complex page with several child models all of which have validations that are likely to be triggered. We can't have a user spend a significant amount of time filing out data only to have a validation fail and the user have to abandon all the data entry, go find the data that did save and then re-key all the child data again.

1) Is there a way to enforce transactional control on model saves? This is the preferred method/solution and something we'd like to see in the future for Skuid if it isn't already possible.
2) Short of that, is there a way to solve for ensuring the child models get updated references when partial saves complete?

Thank you!
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb

Posted 4 years ago

  • 2
Photo of Moshe Karmel

Moshe Karmel, Champion

  • 8,646 Points 5k badge 2x thumb
I had a similar issue with saving 6 different models in JavaScript callbacks. If the user would get it right the first time everything worked great. However if there were any errors and the user tried to rectify the error, we ended up with some models saved and some unsaved and the Invalid Reference ID error. I realized that it was much easier to do a callout to an apex method. The added benefit of an apex callout is the ability to rollback transactions if anything goes wrong. You will need an external snippet with this: "/soap/ajax/29.0/apex.js". And then in your code you can use:

sforce.apex.execute("YOURCLASSNAME", "YOURMETHODNAME",                
{
     YOURFIRSTPARAMETER : value1,
     YOURSECONDPARAMETER : value2

 });.

Hope this helps.
Photo of Zach McElrath

Zach McElrath, Employee

  • 49,056 Points 20k badge 2x thumb
Barry,

We are aware of this limitation in Skuid and are working to resolve it for our Summer 14 release --- it's a very significant issue, and we don't want this to be a reason for users to have to write custom Apex.

There are two parts to our plan to resolve this:

(a) Allow for a "Rollback all saves if any errors occur" option to be specified anywhere that multi-Model Saves can be initiated, e.g. in Page Title Save and Save/Cancel Buttons, or via JavaScript's skuid.model.save(modelsArray) method. This would basically set a Savepoint at the beginning of the Save transaction, and if there is an error in any DML operation Skuid performs as part of the save, then a Database.rollback() will be called to restore the state of the database as of the Savepoint.

(b) Fix the "bad reference" problem with temporary Ids, so that whether or not you use the "Rollback on error" option, your Models will not be come dysfunctional if there is an error somewhere along the multi-Model save chain.

Regards,

Zach
Photo of Zach McElrath

Zach McElrath, Employee

  • 49,056 Points 20k badge 2x thumb
On a lighter note, I was very pleasantly amused when I first read the title of this post: "transnational integrity" made me think there was a problem with our Internationalization framework :)
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
Zach - Glad I could provide a little levity! I realize that I had multiple spelling errors in the original post - if one of the mods is able to feel free to correct my mistakes. Regarding the transactional capability and "bad reference" very encouraged to hear that there might be a resolution in Summer '14. Is there anything that you can suggest as workarounds in the meantime?

Moshe - Appreciate your insight. Would you be willing to share some more details on the approach you took? Did you pass all the data needed down to the apex method and save in there? In your case with 6 models, that seems like a fairly heavy call. Did all of your "save" buttons become "call custom snippet" and then that snippet invoked the apex method?

Thanks guys!
Photo of Moshe Karmel

Moshe Karmel, Champion

  • 8,646 Points 5k badge 2x thumb
Actually I had one save button at the end of a long Create Opportunity page which saves the Opportunity along with a potential Campaign, Account, Management Company and Contact (with an Account). The approach that I took was to use one custom save button for everything, to make our users have less clicks. Then I passed all the information from the page into the apex method. And the apex method went through the parameters and decided what to save and in what order.This is an initial draft that I have here:

// save opportunity
// get the models
var opportunityModel = skuid.model.getModel('Opportunity');
var accountModel = skuid.model.getModel('Account');
var contactModel = skuid.model.getModel('Contact');
var campaignModel = skuid.model.getModel('Campaign');
var managementCompanyModel = skuid.model.getModel('ManagementCompany');
var Electricity = skuid.$M('Electricity');
var Gas = skuid.$M('Gas');
// get the pricing rec type
var recType = skuid.$M('PricingRecordType');
var pricingRecType = recType.getFieldValue(recType.getFirstRow(),'Id');
// get the plymouth house account which will be the agent for every opportunity
var houseAccountModel = skuid.$M('PlymouthHouse');
var houseAccount = houseAccountModel.getFieldValue(houseAccountModel.getFirstRow(),'Id');
// get the model fields
var opportunityRow = opportunityModel.getFirstRow();
var opportunityModelAccount = opportunityModel.getFieldValue(opportunityRow, 'AccountId');
var opportunityModelContact = opportunityModel.getFieldValue(opportunityRow, 'Contact__c');
var opportunityModelCampaign = opportunityModel.getFieldValue(opportunityRow, 'CampaignId');
var opportunityModelManagementCompany = opportunityModel.getFieldValue(opportunityRow, 'Management_Company__c');

opportunityModelAccount = (opportunityModelAccount == null || opportunityModelAccount.length <= 0) ? null : opportunityModelAccount;
opportunityModelCampaign = (opportunityModelCampaign == null || opportunityModelCampaign.length <= 0) ? null : opportunityModelCampaign;
opportunityModelManagementCompany = (opportunityModelManagementCompany == null || opportunityModelManagementCompany.length <= 0) ? null : opportunityModelManagementCompany;

console.log('opportunityModelAccount ' + opportunityModelAccount);
console.log('opportunityModelCampaign ' + opportunityModelCampaign);
console.log('opportunityModelManagementCompany ' + opportunityModelManagementCompany);


var accountRow = accountModel.getFirstRow();
var accountName = accountModel.getFieldValue(accountRow, 'Name', true);
var accountBillingStreet = accountModel.getFieldValue(accountRow, 'BillingStreet', true);
var accountBillingCity = accountModel.getFieldValue(accountRow, 'BillingCity', true);
var accountState = accountModel.getFieldValue(accountRow, 'BillingState', true);
var accountPostal = accountModel.getFieldValue(accountRow, 'BillingPostalCode', true);
var accountPhone = accountModel.getFieldValue(accountRow, 'Phone', true);
var accountTaxNumber = accountModel.getFieldValue(accountRow, 'c2g__CODATaxpayerIdentificationNumber__c', true);

var managementCompanyRow = managementCompanyModel.getFirstRow();
var manCoName = managementCompanyModel.getFieldValue(managementCompanyRow, 'Name', true);
var manCoBillingStreet = accountModel.getFieldValue(managementCompanyRow, 'BillingStreet', true);
var manCoBillingCity = accountModel.getFieldValue(managementCompanyRow, 'BillingCity', true);
var manCoState = accountModel.getFieldValue(managementCompanyRow, 'BillingState', true);
var manCoPostal = accountModel.getFieldValue(managementCompanyRow, 'BillingPostalCode', true);
var manCoPhone = accountModel.getFieldValue(managementCompanyRow, 'Phone', true);

var campaignRow = campaignModel .getFirstRow();
var campaignName = campaignModel.getFieldValue(campaignRow, 'Name', true);
var campaignParent = campaignModel.getFieldValue(campaignRow, 'ParentId', true);
var campaignDescr = campaignModel.getFieldValue(campaignRow, 'Description', true);
var campaignContactId = campaignModel.getFieldValue(campaignRow, 'Contact__c', true);

var contactRow = contactModel.getFirstRow();
var contFirstName = campaignModel.getFieldValue(contactRow, 'FirstName', true);
var contLastName = campaignModel.getFieldValue(contactRow, 'LastName', true);
var contAgentId = campaignModel.getFieldValue(contactRow, 'AccountId', true);
var contactEmail = campaignModel.getFieldValue(contactRow, 'Email', true);
var contactPhone = campaignModel.getFieldValue(contactRow, 'Phone', true);

var gasCommiss = Gas.getFieldValue(Gas.getFirstRow(),'Default_Commission_Rate__c',true);
var elecCommiss = Electricity.getFieldValue(Electricity.getFirstRow(),'Default_Commission_Rate__c',true);
var commodityName = opportunityModel.getFieldValue(opportunityRow,'Commodity__r.Name', true);
var currentName = opportunityModel.getFieldValue(opportunityRow, 'Name', true);      
var commodityId = opportunityModel.getFieldValue(opportunityRow,'Commodity__c', true);
var salesSupport = opportunityModel.getFieldValue(opportunityRow, 'Sales_Support_Representative__c', true);

if(currentName != null && currentName != '' && salesSupport != null && salesSupport != '' && 
    ((opportunityModelCampaign != '' && opportunityModelCampaign != null) || (campaignName != null && campaignName != '' && 
            ((campaignContactId != null && campaignContactId != '') || (contLastName != null && contLastName != ''))))){
    var returnValue;
    var success = true;
    try{
        returnValue = sforce.apex.execute("CreateOpportunityWebservice", "createOpportunity", 
                {oppModelAccountId : opportunityModelAccount,
                    accName : accountName,
                    accBillingStreet : accountBillingStreet,
                    accBillingCity : accountBillingCity,
                    accBillingState : accountState,
                    accZip : accountPostal,
                    accPhone : accountPhone,
                    accTaxNum : accountTaxNumber,
                    oppModelManCoId : opportunityModelManagementCompany,
                    manName : manCoName,
                    manBillingStreet : manCoBillingStreet,
                    manBillingCity : manCoBillingCity,
                    manBillingState : manCoState,
                    manZip : manCoPostal,
                    manPhone : manCoPhone,
                    oppModelCampaign : opportunityModelCampaign,
                    campName : campaignName,
                    campParentCamp : campaignParent,
                    campDescr : campaignDescr,
                    contactId : campaignContactId,
                    agentId : contAgentId,
                    contactFirstName : contFirstName,
                    contactLastName : contLastName,
                    conEmail : contactEmail,
                    conPhone : contactPhone,
                    gasCommission : gasCommiss,
                    electricCommission : elecCommiss,
                    commodName : commodityName,
                    oppName : currentName,
                    houseAccountId : houseAccount,
                    commodId : commodityId,
                    salesSupportId : salesSupport
           });
        } catch (err) {
        alert('An error has occurred. Please try again or contact support with the following message included: ' + err + '; ' + returnValue + ';');
            success = false;
        }
        console.log(returnValue);
        if(success == true)
            window.location = returnValue;
}
else{
    if(currentName == null || currentName == '')
        alert('Opportunity needs a name.');
    else if(salesSupport == null || salesSupport == '')
        alert('Opportunity needs a Sales Support Representative.');
    else if(opportunityModelCampaign == null || opportunityModelCampaign == ''){
        if(campaignName == null || campaignName == '')
            alert('Opportunity needs an existing Campaign or a created Campaign with a Name');
        else if((campaignContactId == null || campaignContactId == '') && (contLastName == null || contLastName == ''))
            alert('Campaign that was attempted to be created needs a Contact or a created Contact with a Last Name');
    }
}
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
Moshe - Thank you again, extremely helpful to see how you approached this! Very much appreciate you taking the time to reply and provide your insight. Seems as though both of us have rather complex data management scenarios we are using Skuid to help solve for :)
Photo of Ben Hubbard

Ben Hubbard, Employee

  • 12,490 Points 10k badge 2x thumb
I believe this issue has been resolved as of the Summer 14 release.