+ Start a Discussion
TehNrdTehNrd 

Re-opening the disscussion of SortOrder on OpportunityLineItem

I'd like to re-open the discussion of SortOrder on the OpportunityLineItem object. A thread from 3.5 years ago can be located here:
http://community.salesforce.com/sforce/board/message?board.id=general_development&message.id=3154

Looks like people from SF were going to look into it but obviously nothing has come to fruition. The reason I have put this in the Visualforce board is that we have have a few VF applications that could really take advantage of access to this field. Visualforce has also opened up new possibilities to UI design where the ability to manage SortOrder could be useful.

Also, no offense to salesforce.com, but the tool for sorting OpportunityLineItems is not that useful when there are multiple products with the same name. I have actually built a pretty slick sorting application but this is currently useless.

A lot of the concerns were about error handling. What happens if you have 3 line items but sort order is defined as, 1, 2, 2. Or 1, 2, 4. How about just throwing a sortOrder exception and force the developers to write good code?

Ideas? Thoughts?
http://ideas.salesforce.com/article/show/10092062/OpportunityLineItem_SortOrder_to_be_CreatableUpdatable

-Jason


Message Edited by TehNrd on 09-05-2008 01:22 PM
Best Answer chosen by Admin (Salesforce Developers) 
kpeterskpeters

I know this post is old, but it seems that a lot of people are interested in it.  I needed this too so I came up with a hacky workaround that works for my needs.  As you probably know, SFDC has a Sort button on the product related list where one can manually change the sorting of each individual product (you do not specify, for instance, sort by the product name). In the background, this interface is posting the results of the form to /oppitm/lineitemsort.jsp which results in the SortOrder field of the OLIs to be updated based on the users selection. Unfortunately, the SortOrder field is not writable via apex (things would be much easier if they were).

 

So, the first step is to have an apex web service that does the custom sorting logic and returns a comma separate list of OLI IDs in the desired sort order. Here is an example of my web service that sorts the OLIs for me based on my sorting requirements:

 

 

webservice static String MRsort(Id oppID)
    {
    	//pull back the OLIs in a specific sort order
    	List<OpportunityLineItem> olis = [Select oli.Id
    			From OpportunityLineItem oli
    			Where oli.OpportunityId = :oppId
    			ORDER BY oli.PSFT_REN__c, oli.PricebookEntry.Product2.Technology_Family__c, oli.PricebookEntry.Product2.Name];
    	
    	//build the comma separated 15 character OLI Id string to send back
    	String sortedIds = '';					  
    	for(OpportunityLineItem oli : olis)
    	{
    		sortedIds += String.valueOf(oli.Id).substring(0,15) + ',';		
    	}
    	
    	//remove the last comma
    	sortedIds = sortedIds.substring(0,sortedIds.length() - 1);
    	return sortedIds;
    }

 

 

We are going to use the list of OLI Ids from the above web service in a JavaScript button that sits on the product related list. After reverse engineering how /oppitm/lineitemsort.jsp is called to commit the sort through SFDC's OOTB sort interface, I was able to come up with the correct button JavaScript to recreate a POST request to this JSP with the list of sorted OLI IDs from the web service:

 

 

{!REQUIRESCRIPT("/soap/ajax/20.0/connection.js")}
{!REQUIRESCRIPT("/soap/ajax/20.0/apex.js")}

var oppID = '{!Opportunity.Id}';

//call the apex web service to get the OLIs in the desired sort order for this opp
var result = sforce.apex.execute("MyClass","MRsort",{oppID: oppID});   

//need to post a form to /oppitm/lineitemsort.jsp because this is how SFDC
//does it but there is not direct API to do the sorting (thus the awkward workaround)
var form = document.createElement("form");
form.setAttribute("method", "post");
form.setAttribute("action", "/oppitm/lineitemsort.jsp");

//set the id of the request to the opportunity ID
var hiddenField = document.createElement("input");
hiddenField.setAttribute("type", 'hidden');
hiddenField.setAttribute("name", 'id');
hiddenField.setAttribute("value", '{!Opportunity.Id}');   
form.appendChild(hiddenField);

//the name of the sorted OLI list that the JSP is expecting is "duel0"
var hiddenField = document.createElement("input");
hiddenField.setAttribute("type", 'hidden');
hiddenField.setAttribute("name", 'duel0');
hiddenField.setAttribute("value", String(result));
form.appendChild(hiddenField);

var hiddenField = document.createElement("input");
hiddenField.setAttribute("type", 'hidden');
hiddenField.setAttribute("name", 'retURL');
hiddenField.setAttribute("value", '/{!Opportunity.Id}');
form.appendChild(hiddenField);

//set to save
var hiddenField = document.createElement("input");
hiddenField.setAttribute("type", 'hidden');
hiddenField.setAttribute("name", 'save');
hiddenField.setAttribute("value", ' Save ');
form.appendChild(hiddenField);

//need to do this so it works in IE
document.body.appendChild(form);

//submit!!
form.submit();

 

 

So, after the user clicks the custom JavaScript sort button, the interaction with the sorting web service and /oppitm/lineitemsort.jsp results in the SortOrder field of the OLIs being set to the desired values and thus the OLIs are displayed in the desired sort order in the related product list.

All Answers

TehNrdTehNrd
I guess this issues doesn't affect that many users. If salesforce.com won't open up the sort order field so that I can deploy my sorting app please improve the out of the box sort tool.

-Jason
MigMig
I need this more than ever . any news ?
TehNrdTehNrd

I havent heard anything from salesforce. I just don't think there are enough people asking for it.

 

Here are two ideas but they aren't gaining much traction:

http://ideas.salesforce.com/article/show/10092062/OpportunityLineItem_SortOrder_to_be_CreatableUpdatable#skin=le

http://ideas.salesforce.com/article/show/10092189/Programmatically_change_order_of_Opportunity_Product_Line_Items#skin=le

 

I could make the lives our sales reps really happy with this feature. They waste so much time trying to sort line items.

Message Edited by TehNrd on 01-21-2009 08:52 AM
andresperezandresperez

Hi,

 

Although this is not exactly what you are looking for, it may help.

 

I have designed a Apex class that sorts by any field a list of any object (standard or custom) returned by a SOQL.

 

I put the class in a message called "A gift for you: Sorting any object (custom or standard) by any field" in the community boards.

 

This is the link:
http://community.salesforce.com/sforce/board/message?board.id=apex&message.id=11655#M11655

 

Enjoy,

 

Andres Perez

MigMig

Hi Andres, Are you sure that  you can resort the Opportunity Product Items ? Because normally you can't ... µIt's done automatically by SortOrder and Product Name ! 

 

gonna have a look to you code later 'causer I'm quiet busy in this moment

 

Thanks a lot for your reply ...

 

Mig 

andresperezandresperez

Hi,

 

If you can bring the data with a SOQL statement, and you can display it in a datatable then the data has the structure required to be soreted by the class I have built.

 

Note that displaying the data in a datatable is not required for this sorter to work, what I meant is to be able to access each field from the list of sObjects returned by the SOQL.

 

The sorting class sorts the data that has been brought from the database using SOQL.

 

I'll tested agains the opportunity product items to see if it works and I'll let you know what I find.

andresperezandresperez

Hi,

 

I have tried a SOQL like this:

 

SELECT LastModifiedDate, ListPrice, Opportunity.Name, SortOrder from OpportunityLineItem

and I was able to sort by everyfield. The SortOrder field had all null values, not sure if this is related with the problem you were reporting before.

 

But, anyways... Do you really want to sort by this field if you do not have control as to the values that go into this field?

TehNrdTehNrd

andresperez wrote:

 Do you really want to sort by this field if you do not have control as to the values that go into this field?


This is exactly the issue. We want control of what values go into this field.

andresperezandresperez

Hi,

 

Why no have a custom field where you do have control on the data that goes into the field?

TehNrdTehNrd
In the related List section of an Opportunity the display order is driven by the SortOrder field. You could replace the entire view layout with a VF page to control the displayed order with a custom sort field but this is sort of like killing a fly with a bazooka.
kpeterskpeters

I know this post is old, but it seems that a lot of people are interested in it.  I needed this too so I came up with a hacky workaround that works for my needs.  As you probably know, SFDC has a Sort button on the product related list where one can manually change the sorting of each individual product (you do not specify, for instance, sort by the product name). In the background, this interface is posting the results of the form to /oppitm/lineitemsort.jsp which results in the SortOrder field of the OLIs to be updated based on the users selection. Unfortunately, the SortOrder field is not writable via apex (things would be much easier if they were).

 

So, the first step is to have an apex web service that does the custom sorting logic and returns a comma separate list of OLI IDs in the desired sort order. Here is an example of my web service that sorts the OLIs for me based on my sorting requirements:

 

 

webservice static String MRsort(Id oppID)
    {
    	//pull back the OLIs in a specific sort order
    	List<OpportunityLineItem> olis = [Select oli.Id
    			From OpportunityLineItem oli
    			Where oli.OpportunityId = :oppId
    			ORDER BY oli.PSFT_REN__c, oli.PricebookEntry.Product2.Technology_Family__c, oli.PricebookEntry.Product2.Name];
    	
    	//build the comma separated 15 character OLI Id string to send back
    	String sortedIds = '';					  
    	for(OpportunityLineItem oli : olis)
    	{
    		sortedIds += String.valueOf(oli.Id).substring(0,15) + ',';		
    	}
    	
    	//remove the last comma
    	sortedIds = sortedIds.substring(0,sortedIds.length() - 1);
    	return sortedIds;
    }

 

 

We are going to use the list of OLI Ids from the above web service in a JavaScript button that sits on the product related list. After reverse engineering how /oppitm/lineitemsort.jsp is called to commit the sort through SFDC's OOTB sort interface, I was able to come up with the correct button JavaScript to recreate a POST request to this JSP with the list of sorted OLI IDs from the web service:

 

 

{!REQUIRESCRIPT("/soap/ajax/20.0/connection.js")}
{!REQUIRESCRIPT("/soap/ajax/20.0/apex.js")}

var oppID = '{!Opportunity.Id}';

//call the apex web service to get the OLIs in the desired sort order for this opp
var result = sforce.apex.execute("MyClass","MRsort",{oppID: oppID});   

//need to post a form to /oppitm/lineitemsort.jsp because this is how SFDC
//does it but there is not direct API to do the sorting (thus the awkward workaround)
var form = document.createElement("form");
form.setAttribute("method", "post");
form.setAttribute("action", "/oppitm/lineitemsort.jsp");

//set the id of the request to the opportunity ID
var hiddenField = document.createElement("input");
hiddenField.setAttribute("type", 'hidden');
hiddenField.setAttribute("name", 'id');
hiddenField.setAttribute("value", '{!Opportunity.Id}');   
form.appendChild(hiddenField);

//the name of the sorted OLI list that the JSP is expecting is "duel0"
var hiddenField = document.createElement("input");
hiddenField.setAttribute("type", 'hidden');
hiddenField.setAttribute("name", 'duel0');
hiddenField.setAttribute("value", String(result));
form.appendChild(hiddenField);

var hiddenField = document.createElement("input");
hiddenField.setAttribute("type", 'hidden');
hiddenField.setAttribute("name", 'retURL');
hiddenField.setAttribute("value", '/{!Opportunity.Id}');
form.appendChild(hiddenField);

//set to save
var hiddenField = document.createElement("input");
hiddenField.setAttribute("type", 'hidden');
hiddenField.setAttribute("name", 'save');
hiddenField.setAttribute("value", ' Save ');
form.appendChild(hiddenField);

//need to do this so it works in IE
document.body.appendChild(form);

//submit!!
form.submit();

 

 

So, after the user clicks the custom JavaScript sort button, the interaction with the sorting web service and /oppitm/lineitemsort.jsp results in the SortOrder field of the OLIs being set to the desired values and thus the OLIs are displayed in the desired sort order in the related product list.

This was selected as the best answer
V'NathV'Nath

Hi ,

 

I have tried your Code but its not getting saved Records.

 

can you please provide any suggestions.

 

Regards,

V'nath.

kpeterskpeters

Can you describe your problem in more detail?  I do not understand what you mean.  FYI, this is still working for us today in Winter '12.

SFDC DevloperSFDC Devloper

Hi,

 

i have used Samed Code in Different Sandboxes but its working in all sandboxes except one.

 

i got the Erro "Unable to Access Page" . Please help Regards this.

 

 

 

 

 

Advanec Thanks.

SFDC DevloperSFDC Devloper

Hi,

 

i have used Samed Code in Different Sandboxes but its working in all sandboxes except one.

 

i got the Erro "Unable to Access Page" . Please help Regards this.

 

 

Advanec Thanks.

Mitesh SuraMitesh Sura

Thanks kpeters for posting this. It worked good in my managed package, but SF will not allow webservice call outs in Professional Edition org. I am thinking to call a VF page from a list button. This page will call a controller method that will return the comma seperated ids, which it does, but obiously I am doing something wrong here. Probably to do with my lack of understanding of DOM. 

 

Please see below, I am just copying your solution in a JS in VF page, but I get an error. Any advise is appreciated. 

 

//// In VF page ////

 <script type="text/javascript">
	    customSort();

	    function customSort() {	
	        Visualforce.remoting.Manager.invokeAction(
	            '{!$RemoteAction.MyController.customSort}',
	            '006d0000006xxxx', 
	            function(result, event){
	            	alert(result);
					
					//need to post a form to /oppitm/lineitemsort.jsp because this is how SFDC 
					//does it but there is not direct API to do the sorting (thus the awkward workaround) 
					var form = document.createElement("form"); 
					form.setAttribute("method", "post"); 
					form.setAttribute("action", "/oppitm/lineitemsort.jsp"); 
					
					//set the id of the request to the opportunity ID 
					var hiddenField = document.createElement("input"); 
					hiddenField.setAttribute("type", 'hidden'); 
					hiddenField.setAttribute("name", 'id'); 
					hiddenField.setAttribute("value", '006d0000006xxxx'); 
					form.appendChild(hiddenField); 
					
					//the name of the sorted OLI list that the JSP is expecting is "duel0" 
					var hiddenField = document.createElement("input"); 
					hiddenField.setAttribute("type", 'hidden'); 
					hiddenField.setAttribute("name", 'duel0'); 
					hiddenField.setAttribute("value", String(result)); 
					form.appendChild(hiddenField); 
					
					var hiddenField = document.createElement("input"); 
					hiddenField.setAttribute("type", 'hidden'); 
					hiddenField.setAttribute("name", 'retURL'); 
					hiddenField.setAttribute("value", '/006d0000006xxxx'); 
					form.appendChild(hiddenField); 
					
					//set to save 
					var hiddenField = document.createElement("input"); 
					hiddenField.setAttribute("type", 'hidden'); 
					hiddenField.setAttribute("name", 'save'); 
					hiddenField.setAttribute("value", ' Save '); 
					form.appendChild(hiddenField); 
					
					//need to do this so it works in IE 
					document.body.appendChild(form); 
					
					//submit!! 
					form.submit();
	            	
	            }, 
	            {escape: false}
	        );
		}
	
	</script>

 Error message:

 

Unable to Access Page

You are missing information needed by the page you have attempted to access. If you believe this is an error, please refresh your screen. If the error persists, please report it to our Customer Support team and provide the URL of the page you were requesting as well as any other related information. 

 

URL: https://na14.salesforce.com/oppitm/lineitemsort.jsp

 

Thanks for your time

Kirill_YunussovKirill_Yunussov

kpeters, thanks for the great code.    Any idea on how to call this JavaScript automatically on "after OLI insert"?

craigvb5craigvb5

Any one figure out how to get around the "Unable to Access Page" error for this form post?

KoenVMKoenVM

Does this workaround still work?

If I look at what sfdc is sending in the post of this page, it now also contains a _CONFIRMATIONTOKEN parameter.

If I try this, the page seems to open but it's not automatically submitting.

 

Any ideas?

Kirill_YunussovKirill_Yunussov

Yes, everything still works fine.   I've implemented this in our org, and it's working as usual.

KoenVMKoenVM
Hi, does the page with the sorted items auto submits? At my side, the page does open with the items sorted in the list but it doesn't get submitted. For your background information, I'm at the same time trying to port this functionality to an apex class so it could be called from within a trigger or from different places instead of only from these custom buttons. Thanks for the quick respons!
ministe_2003ministe_2003

Koene_V did you ever find a solution for this?  I'm trying to implement this with apex and visualforce so users can customise the order instead of relying on ordering by queries but like a lot of other people I'm getting sent to the "Unable to access page" error.  I notice the url is /oppitm/lineitemsort.jsp which suggests that I should be able to get past this somehow by stopping that page from having to load, anyone solved this issue?

craigvb5craigvb5
Sorry, wasn't able to solve this using the standard fields. The only option I found was to create my own sorting functionality and print options.
ministe_2003ministe_2003

So did you have to display the products in a custom related list in order to display them in your own order?

craigvb5craigvb5
No, because I just showed a custom sort order field and the user would know that is how it is going to be sorted. If you absolutely must show the items in the related list itself sorted then yes, you'll have to use a custom VF page.
ministe_2003ministe_2003

I think like someone mentioned earlier that there may be an issue with missing the _CONFIRMATIONTOKEN value.  It appears to be dynamically generated and if you open the standard Sort page and change it (inspect it with Chrome and change the value)  then the Save button doesn't work.

 

Without knowing how the key is generated I think we're screwed.

KoenVMKoenVM
I think as well that it has something to do with the _CONFIRMATIONTOKEN...
Paul ScottPaul Scott
VF pages run on a separate domain from standard pages to prevent cross site request forgery.
The Javascript workaround still works, but will only work as button on a standard object detail page/list, not a as script on a VF page.
ventoreroventorero
Hi

Can you guide me how to create the web service in the first piece?
I tried to save it as a class but it wont let me save? is there anything i should add to the code? 

Thanks
Mitesh SuraMitesh Sura
Somebody figured this out. Please see this video [1.20] https://www.youtube.com/watch?v=BcAvvzf0EnM

From VF page it is redirected to "lineitemsort.jsp" and then back to opporutnity with line items sorted. Any ideas on how they have done it?
Eric Smith 9Eric Smith 9
kpeters solution works great.  Would anyone be able to help a newbie out with a sample test class for his controller?
Merri FurlongMerri Furlong
well, it isn't pretty, but here is a test class I made for kpeters controller.  the first part is just to create the account and opportunity (controller is to sort opportunitylines in a custom sort field.  look at starttest/stop test, this is where the testsort is passed to the MRSort in his controller. This is to test the webservice. The assert can probably be improved, but it passes.

  // tests CustomSort
   
   static testMethod void customsort_TestController() {
        List < Account > lstAccount = new List < Account > ();

        Account testAccount = new Account();
        testAccount.Name = 'Test Account';
        lstAccount.add(testAccount);
        Account testAccount1 = new Account();
        testAccount1.Name = 'Test Account1';
        lstAccount.add(testAccount1);

        insert lstAccount;

        Opportunity testOpp = new Opportunity();
        testOpp.Name = 'Test Opp';
        testOpp.StageName = 'To Be Searched';
        testOpp.CloseDate = System.now().date();
        testOpp.AccountId = testAccount.Id;
        testOpp.CurrencyIsoCode = 'USD';
        insert testOpp;

        testOpp = [select Id, Name from Opportunity where Id = : testOpp.Id];

        OpportunityLineItem testSort = new OpportunityLineItem();
        testSort.Description = 'Test oppline 1';
        testSort.PRODUCTNICKNAME__c = 'Test Product 1';
        testSort.UnitPrice = 1.0;
        testSort.PriceBookEntryId = '01u80000004LDj9AAG';
        testSort.OpportunityId = testOpp.id;
        testSort.Quantity = 1.0;
       
        insert testSort;

       testSort  = [Select oli.Id, oli.Line_Number__c,PRODUCTNICKNAME__c
                From OpportunityLineItem oli
                Where oli.OpportunityId = :testOpp.id
                ORDER BY oli.Line_Number__c];

        system.test.startTest();
        CustomSort controller = new CustomSort();
        PageReference pageRef = new PageReference('/'+testOpp.id);
        system.test.setCurrentPage(pageRef);
        ApexPages.CurrentPage().getparameters().put('oppid', testOpp.id);
        pageRef.getParameters().put('oppid', testOpp.id);         
        CustomSort.MRsort(testOpp.id);
        System.assert(testSort != null);
        system.test.stopTest();
    }
Merri FurlongMerri Furlong
I would love to move the javascript button off of the opportunity page layout and have it run from a VF page, but haven't been able to get the code to fire from VF page.  I see others have the same issue, probably due to cross site scripting prevention. will stay tuned to this thread.
Jonathan BoulterJonathan Boulter
Salesforce please open up the SortOrder field on OpportunityLineItem so we can use it in Unit Tests without having to use the SeeAllData=true annotation.
Vikash TiwaryVikash Tiwary
Hello, I tried implementing kpeters solution but like others I am also facing same issue. It works partially not completely.
I am being redirected to lineitemsort.jsp with sorted lineitems but it does not close own its own and the user requires to click Save button. 

Does it work complety for any one? Thank you.
Vikash TiwaryVikash Tiwary
Now, i am able to get this done using related list button by passing ConfirmationToken but when implementing in page still I get error 'Unable to access page'
SarikaPSarikaP
Hi Vikash, Could you please guide me in how did you pass the confirmation token? I am trying to do with the help of other posts in the forum but its still not working. I have a javascript button for this.
Eric Smith 9Eric Smith 9
The kpeters solution works fine for me.  I did have to make a small change for this to continue to work in Winter '18.  Comment out the form.appendChild(hiddenField); line in the //set to save section in the button javascript.
SarikaPSarikaP
Thanks a lot Eric! You made my day. I just commented the line that you mentioned and now its working fine.
Manuela Lorenzi-Kayser 6Manuela Lorenzi-Kayser 6
This is great, I got it to work as well, thanks kpeters and everyone else. Just one note: the custom button does not show up in Lighting and I believe that this solution will not work in LEX (see explanation here https://success.salesforce.com/ideaView?id=08730000000cGX8AAM). Any thoughts on how this could be adapted to work in Lightning?