Sorting a filtered look up

Forms
1

I have a lookup filter as below. I want to sort the lookup in descending order by date so the last one is on top.

fd.spRendered(function() {

    function filterLookup(v){
        // getting the selected Choice (0 if nothing is selected).
        //today’s date:
var today = new Date();
//number of ms in a day:
var day = 86400000;
//amount of days:
var amountOfDays = 14;
var endamount= 24;
var date = new Date();
//get date to filter with:
var filterValue =  date.setDate(date.getDate() - 365 /*days*/);
var filterValueend = date.setDate(date.getDate() + 365 /*days*/);
var filterDate = new Date(filterValue);
var filterEnd= new Date(filterValueend);

        // setting filtration
         console.log(filterDate);
        fd.field('TargetEvent0').filter =" EventDate lt datetime'" + filterEnd.toISOString() +  "'"+" and EventDate gt datetime'" + filterDate.toISOString() +"'";
  
   
        fd.field('TargetEvent0').ready().then(function (field){
            var whatIsTheFilter = field.filter;
            var isThisNull = field.widget;
            console.log(whatIsTheFilter);
            console.log(isThisNull === null);
            field.widget.dataSource.read();
        });
        

    }

    console.log('form open filter lookup');
    //filter when form opens
    filterLookup(fd.field('TargetEvent0').value);
    
   
});
2

Hello @jktodd007,

For the lookup to use descending order, please, try:

fd.field('Lookup').orderBy = { field: 'Column Internal Name', desc: true };
3

That makes sense but since I am filtering the results where do I put that orderBy?

4

Hello @jktodd007,

You need to use it after filtering the results. I've updated your code slightly, please see below.

Please replace Column Internal Name in the code with teh actual column name.

fd.spRendered(function() {

    function filterLookup(v){
        // getting the selected Choice (0 if nothing is selected).
        //today’s date:
		var today = new Date();
		//number of ms in a day:
		var day = 86400000;
		//amount of days:
		var amountOfDays = 14;
		var endamount= 24;
		var date = new Date();
		//get date to filter with:
		var filterValue =  date.setDate(date.getDate() - 365 /*days*/);
		var filterValueend = date.setDate(date.getDate() + 365 /*days*/);
		var filterDate = new Date(filterValue);
		var filterEnd= new Date(filterValueend);

		// setting filtration
		console.log(filterDate);
		fd.field('TargetEvent0').filter =" EventDate lt datetime'" + filterEnd.toISOString() +  "'"+" and EventDate gt datetime'" + filterDate.toISOString() +"'";
        //order records
        fd.field('TargetEvent0').orderBy = { field: 'Column Internal Name', desc: true };  
        fd.field('TargetEvent0').widget.dataSource.read();  
    }
    
         console.log('form open filter lookup');
        //filter when form opens
        fd.field('TargetEvent0').ready().then(function (field){
            filterLookup(field.value);
        });

});
5

That worked great for one form I have another form that uses same information the code is below

fd.spRendered(function() {

    function filterLookup(v){
        // getting the selected Choice (0 if nothing is selected).
        //today’s date:
		var today = new Date();
		//number of ms in a day:
		var day = 86400000;
		//amount of days:
		var amountOfDays = 14;
		var endamount= 24;
		var date = new Date();
		//get date to filter with:
		var filterValue =  date.setDate(date.getDate() - 180 /*days*/);
		var filterValueend = date.setDate(date.getDate() + 365 /*days*/);
		var filterDate = new Date(filterValue);
		var filterEnd= new Date(filterValueend);

		// setting filtration
		console.log(filterDate);
		fd.field('TargetEvent').filter =" EventDate lt datetime'" + filterEnd.toISOString() +  "'"+" and EventDate gt datetime'" + filterDate.toISOString() +"'";
        //order records
        fd.field('TargetEvent').orderBy = { field: 'ID', desc: true };  
        fd.field('TargetEvent').widget.dataSource.read();  
    }
    
         console.log('form open filter lookup');
        //filter when form opens
        fd.field('TargetEvent').ready().then(function (field){
            filterLookup(field.value);
        });

});

However I am getting a TypeError: "fd.field(...).widget is null" for this one and it will not sort and I don't know why.

6

@jktodd007,

Please make sure that you are using the correct internal name of the lookup field in the code.

If the internal name is correct, is that a complete code that you are using?
Do you see any other errors in the console? Could you please share the screenshot.

7

That is the complete code and there are no other errors

8

@jktodd007,

Could you please share the screenshot of the error with the extended details.

Also, please share the screenshot of the lookup field settings in the designer:
image

9

Console
Console.JPG1892×336 104 KB
Target1 target2

10

@jktodd007,

Could you please export the form and share the file with me.

image