Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics NAV forum / Fill date filter autom...
Microsoft Dynamics NAV forum
Answered

Fill date filter automatically with another date

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hello everyone,

I need to add an extra column in Chart of Accounts page with Net Change of the last year. The idea is to fill the Date filter and then in this extra column see the same amount of Net Change but for the last year.

Example.. If i select a period like 01012018..01042018 i want a second date filter to be filled with 01012017..01042017.

In order to do this I create two new columns called Net Change2 (flow filter like Net Change but with a where clause linked to Date Filter 2) and add a column called Date Filter (a flowfield Date) in G/L account table

Then i add this line in GetBalance function

GLAcc.SETFILTER("Date Filter",'%1..%2',v_StartDate,v_EndDate);
GLAcc.SETFILTER("Date Filter2",'%1..%2',CALCDATE('<C-1Y>',v_StartDate));

plano-contas.png



My problem is how to automatically fill date filter 2 with the Date filter less 1 year? I don't want to be me to need to fill this, but be automatically filled after Date Filter 1. I tried the above code but no changes in Net change 2 when I modify the Date Filter like the image can show.
For now is the only thing i need to do to make this work :)
 

                            






Categories:
 

Financial Management 







                        


                        
                        

                            





 





 



    
    
    
    

        
    


                        

                        
                        
                        

                            
                                
                            

                             
                        

                        
                    


                    

                        
 
                                                                                       
                        

                    


                     
                     
                     
                     
                     
                     
                     

                    
                    

                    
                    

                    
                    

                                                              
                                                              
                                                              



















 





 









 





 









 





 









 





 



                    
                    
                    

                        
                        
 
                            











 

















































 

  


 
    
            
            
        
         
        
  • 
          
    
            
    
              
    
                
    
                  
    

                    
    
                    
    
                        

                      
    

                      
    
                     
    
                        






 
    


 
    
    
    
    
    
    
    
    
      
    
        
     

       

        
            Community Member Profile Picture
        

    

  
                        
    
                            
















 





 











 





 

  











 





 











 





 







 
    

  

 
    

    
      

        
        
         
         

        
            
            
            
                
            
            
        
        
        

        
            
              
                Community Member
                
            
             
         

            
                
                     
                        
                             
                                
                                    
                                        
                                        Microsoft Employee
                                        
                                    
                                
                            
                         
                     
                 
            

    

 on  at 
                        
    
                    
    
                        
    
                        
    
                        
                          
                        
                      
    
                    
    
                  
    
                
    
              
    
              
    
                
    
                  
    
                    
    
                      
     RE: Fill date filter automatically with another date
    
                    
    
                    
    
                      
    
                        
    Thanks, it worked but I've added the recalculation of Net Change2 with calcfields..
    

    So the solution was to create a text global var called LastDateFilter and add this code in the OnAfterGetCurrRecord of Page 16.
    

    

    IF LastDateFilter <> Rec.GETFILTER("Date Filter") THEN BEGIN
Rec.SETFILTER("Date Filter2",'%1..%2',CALCDATE('<-CY-1Y>', Rec.GETRANGEMIN("Date Filter")), CALCDATE('<CY-1Y>', Rec.GETRANGEMIN("Date Filter")));
LastDateFilter := Rec.GETFILTER("Date Filter");
CALCFIELDS("Saldo Periodo2");
END;
    

    


     
    
                      
    
                    
    
                      
                          
                       
                        
                        
     
                          
                        
    
                        
                  
    
                
    
              
    
            
    
          
    
        
    • 
        
            
            
        
         
        
  • 
          
    
            
    
              
    
                
    
                  
    

                    
    
                    
    
                        
                            
    Verified answer
    
                        

                      
    

                      
    
                     
    
                        






 
    


 
    
    
    
    
    
    
    
    
      
    
        
     

       

        
            Guillem Padilla Profile Picture
        

    

  
                        
    
                            
















 





 











 





 

  











 





 











 





 







 
    

  

 
    

    
      

        
        
         
         

        
            
            
             
                
                
            
            
        
        
        

        
            
              
                Guillem Padilla
                425 
            
             
         

            
                
                     
                 
            

    

 on  at 
                        
    
                    
    
                        
    
                        
    
                        
                          
                        
                      
    
                    
    
                  
    
                
    
              
    
              
    
                
    
                  
    
                    
    
                      
     RE: Fill date filter automatically with another date
    
                    
    
                    
    
                      
    
                        
    Do you mean the global variable? After the SETFILTER
    

    

    

    OnAfterGetCurrRecord -

If LastDateFilter <> Rec.GETFILTER("Date Filter") THEN BEGIN
  Rec.SETFILTER("Date Filter2",'%1..%2',CALCDATE('<-CY-1Y>', Rec.GETRANGEMIN("Date Filter")), CALCDATE('<CY-1Y>', Rec.GETRANGEMIN("Date Filter")));
  LastDateFilter := Rec.GETFILTER("Date Filter");
END;
    

    


     
    
                      
    
                    
    
                      
                          
                       
                        
                        
     
                          
                        
    
                        
                  
    
                
    
              
    
            
    
          
    
        
    • 
        
            
            
        
         
        
  • 
          
    
            
    
              
    
                
    
                  
    

                    
    
                    
    
                        

                      
    

                      
    
                     
    
                        






 
    


 
    
    
    
    
    
    
    
    
      
    
        
     

       

        
            Community Member Profile Picture
        

    

  
                        
    
                            
















 





 











 





 

  











 





 











 





 







 
    

  

 
    

    
      

        
        
         
         

        
            
            
            
                
            
            
        
        
        

        
            
              
                Community Member
                
            
             
         

            
                
                     
                        
                             
                                
                                    
                                        
                                        Microsoft Employee
                                        
                                    
                                
                            
                         
                     
                 
            

    

 on  at 
                        
    
                    
    
                        
    
                        
    
                        
                          
                        
                      
    
                    
    
                  
    
                
    
              
    
              
    
                
    
                  
    
                    
    
                      
     RE: Fill date filter automatically with another date
    
                    
    
                    
    
                      
    
                        
    Where I assign the value to DateFilter?
    

                      
    
                    
    
                      
                          
                       
                        
                        
     
                          
                        
    
                        
                  
    
                
    
              
    
            
    
          
    
        
    • 
        
            
            
        
         
        
  • 
          
    
            
    
              
    
                
    
                  
    

                    
    
                    
    
                        
                        
                           
    Suggested answer
    
                        
                        

                      
    

                      
    
                     
    
                        






 
    


 
    
    
    
    
    
    
    
    
      
    
        
     

       

        
            Guillem Padilla Profile Picture
        

    

  
                        
    
                            
















 





 











 





 

  











 





 











 





 







 
    

  

 
    

    
      

        
        
         
         

        
            
            
             
                
                
            
            
        
        
        

        
            
              
                Guillem Padilla
                425 
            
             
         

            
                
                     
                 
            

    

 on  at 
                        
    
                    
    
                        
    
                        
    
                        
                          
                        
                      
    
                    
    
                  
    
                
    
              
    
              
    
                
    
                  
    
                    
    
                      
     RE: Fill date filter automatically with another date
    
                    
    
                    
    
                      
    
                        
    No, it does not work.
    

    You can try to put this workaround in page:
    

    Create global variable to save "Date Filter"
    

    

    

    OnAfterGetCurrRecord -

If LastDateFilter <> Rec.GETFILTER("Date Filter") THEN BEGIN
Rec.SETFILTER("Date Filter2",'%1..%2',CALCDATE('<-CY-1Y>', Rec.GETRANGEMIN("Date Filter")), CALCDATE('<CY-1Y>', Rec.GETRANGEMIN("Date Filter")));
LastDateFilter := Rec.GETFILTER("Date Filter");
END;
  
    

    

    

    
                      
    
                    
    
                      
                          
                       
                        
                        
     
                          
                        
    
                        
                  
    
                
    
              
    
            
    
          
    
        
    • 
        
            
            
        
         
        
  • 
          
    
            
    
              
    
                
    
                  
    

                    
    
                    
    
                        

                      
    

                      
    
                     
    
                        






 
    


 
    
    
    
    
    
    
    
    
      
    
        
     

       

        
            Community Member Profile Picture
        

    

  
                        
    
                            
















 





 











 





 

  











 





 











 





 







 
    

  

 
    

    
      

        
        
         
         

        
            
            
            
                
            
            
        
        
        

        
            
              
                Community Member
                
            
             
         

            
                
                     
                        
                             
                                
                                    
                                        
                                        Microsoft Employee
                                        
                                    
                                
                            
                         
                     
                 
            

    

 on  at 
                        
    
                    
    
                        
    
                        
    
                        
                          
                        
                      
    
                    
    
                  
    
                
    
              
    
              
    
                
    
                  
    
                    
    
                      
     RE: Fill date filter automatically with another date
    
                    
    
                    
    
                      
    
                        
    That's the problem I can't find the call of this function anywhere in the page or table.. maybe a code unit i don't know..
    

    If you're saying to put this in date filter on validate() trigger
    

    Rec.SETFILTER("Date Filter2",'%1..%2',CALCDATE('<-CY-1Y>', Rec.GETRANGEMIN("Date Filter")), CALCDATE('<CY-1Y>', Rec.GETRANGEMIN("Date Filter")));
    

    

    

    It don't work either.. in page i fill the date filter but the column net change 2 don't change neither the column date filter2. The column date filter2 is empty in the page view, as the image of my last post
    
                      
    
                    
    
                      
                          
                       
                        
                        
     
                          
                        
    
                        
                  
    
                
    
              
    
            
    
          
    
        
    • 
        
            
            
        
         
        
  • 
          
    
            
    
              
    
                
    
                  
    

                    
    
                    
    
                        

                      
    

                      
    
                     
    
                        






 
    


 
    
    
    
    
    
    
    
    
      
    
        
     

       

        
            Guillem Padilla Profile Picture
        

    

  
                        
    
                            
















 





 











 





 

  











 





 











 





 







 
    

  

 
    

    
      

        
        
         
         

        
            
            
             
                
                
            
            
        
        
        

        
            
              
                Guillem Padilla
                425 
            
             
         

            
                
                     
                 
            

    

 on  at 
                        
    
                    
    
                        
    
                        
    
                        
                          
                        
                      
    
                    
    
                  
    
                
    
              
    
              
    
                
    
                  
    
                    
    
                      
     RE: Fill date filter automatically with another date
    
                    
    
                    
    
                      
    
                        
    I'm sorry, I got confused, I thought there were two lists on the same page, I did not realize that was a setup by making the screenshot to show it
    

    Where do you call GetBalance function?
    
                      
    
                    
    
                      
                          
                       
                        
                        
     
                          
                        
    
                        
                  
    
                
    
              
    
            
    
          
    
        
    • 
        
            
            
        
         
        
  • 
          
    
            
    
              
    
                
    
                  
    

                    
    
                    
    
                        

                      
    

                      
    
                     
    
                        






 
    


 
    
    
    
    
    
    
    
    
      
    
        
     

       

        
            Community Member Profile Picture
        

    

  
                        
    
                            
















 





 











 





 

  











 





 











 





 







 
    

  

 
    

    
      

        
        
         
         

        
            
            
            
                
            
            
        
        
        

        
            
              
                Community Member
                
            
             
         

            
                
                     
                        
                             
                                
                                    
                                        
                                        Microsoft Employee
                                        
                                    
                                
                            
                         
                     
                 
            

    

 on  at 
                        
    
                    
    
                        
    
                        
    
                        
                          
                        
                      
    
                    
    
                  
    
                
    
              
    
              
    
                
    
                  
    
                    
    
                      
     RE: Fill date filter automatically with another date
    
                    
    
                    
    
                      
    
                        
    I didn't understand your last reply sorry 
    

                      
    
                    
    
                      
                          
                       
                        
                        
     
                          
                        
    
                        
                  
    
                
    
              
    
            
    
          
    
        
    • 
        
            
            
        
         
        
  • 
          
    
            
    
              
    
                
    
                  
    

                    
    
                    
    
                        

                      
    

                      
    
                     
    
                        






 
    


 
    
    
    
    
    
    
    
    
      
    
        
     

       

        
            Community Member Profile Picture
        

    

  
                        
    
                            
















 





 











 





 

  











 





 











 





 







 
    

  

 
    

    
      

        
        
         
         

        
            
            
            
                
            
            
        
        
        

        
            
              
                Community Member
                
            
             
         

            
                
                     
                        
                             
                                
                                    
                                        
                                        Microsoft Employee
                                        
                                    
                                
                            
                         
                     
                 
            

    

 on  at 
                        
    
                    
    
                        
    
                        
    
                        
                          
                        
                      
    
                    
    
                  
    
                
    
              
    
              
    
                
    
                  
    
                    
    
                      
     RE: Fill date filter automatically with another date
    
                    
    
                    
    
                      
    
                        
    I doidn't understand your last reply sorry 
    

                      
    
                    
    
                      
                          
                       
                        
                        
     
                          
                        
    
                        
                  
    
                
    
              
    
            
    
          
    
        
    • 
        
            
            
        
         
        
  • 
          
    
            
    
              
    
                
    
                  
    

                    
    
                    
    
                        

                      
    

                      
    
                     
    
                        






 
    


 
    
    
    
    
    
    
    
    
      
    
        
     

       

        
            Guillem Padilla Profile Picture
        

    

  
                        
    
                            
















 





 











 





 

  











 





 











 





 







 
    

  

 
    

    
      

        
        
         
         

        
            
            
             
                
                
            
            
        
        
        

        
            
              
                Guillem Padilla
                425 
            
             
         

            
                
                     
                 
            

    

 on  at 
                        
    
                    
    
                        
    
                        
    
                        
                          
                        
                      
    
                    
    
                  
    
                
    
              
    
              
    
                
    
                  
    
                    
    
                      
     RE: Fill date filter automatically with another date
    
                    
    
                    
    
                      
    
                        
    Are both lists pointing to the same rec?
    

    What if you put the Date filter on first list?
    
                      
    
                    
    
                      
                          
                       
                        
                        
     
                          
                        
    
                        
                  
    
                
    
              
    
            
    
          
    
        
    • 
        
            
            
        
         
        
  • 
          
    
            
    
              
    
                
    
                  
    

                    
    
                    
    
                        

                      
    

                      
    
                     
    
                        






 
    


 
    
    
    
    
    
    
    
    
      
    
        
     

       

        
            Community Member Profile Picture
        

    

  
                        
    
                            
















 





 











 





 

  











 





 











 





 







 
    

  

 
    

    
      

        
        
         
         

        
            
            
            
                
            
            
        
        
        

        
            
              
                Community Member
                
            
             
         

            
                
                     
                        
                             
                                
                                    
                                        
                                        Microsoft Employee
                                        
                                    
                                
                            
                         
                     
                 
            

    

 on  at 
                        
    
                    
    
                        
    
                        
    
                        
                          
                        
                      
    
                    
    
                  
    
                
    
              
    
              
    
                
    
                  
    
                    
    
                      
     RE: Fill date filter automatically with another date
    
                    
    
                    
    
                      
    
                        
    The problem is the column net change 2 have always the same value... that code din't work because the date filter stay empty.. in the column date filter 2 i don't have any value.
    

    

    0412.plano-contas.png
    
                      
    
                    
    
                      
                          
                       
                        
                        
     
                          
                        
    
                        
                  
    
                
    
              
    
            
    
          
    
        
    • 
        
      







 
                              
                            

                                

                                    

                                        
Under review

                                        
                                    

                                    

                                        
 
                                            
                                            

                                                
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

                                            

                                            
                                            

                                                    
                                            

                                       

                                    

                                 
                                

                             
  
                            
                        

                        
 

                    

                    
                

                

                    
                        







 





 














   
       
   

    

        

            
Helpful resources

        

        
        





 





 







    
    

 
















    
    
    

    
        
    



        
            
            

                
Quick Links

                


  
	
 

	

		

				
					
		

		

			

				
Anton Venter – Community Spotlight

				

					
Kudos to our October Community Star of the month!

				

			

		

	

  
	
 

	

		

				
					
		

		

			

				
Announcing Our 2024 Season 2 Super Users!

				

					
A new season of Super Users has arrived, and we are so grateful for the daily…

				

			

		

	

  
	
 

	

		

				
					
		

		

			

				
Dynamics 365 Community Newsletter - September 2024

				

					
Check out the latest community news

				

			

		

	

	
 

	

		
more Community News

	



            

            
        

        

        
        




 





 

        

         
            
                
                    










 





 




    
    
    

            
            
            

    

                
            
        

         
         
            

          
          




 





 

          

            

                
Leaderboard
 
                
 
                 









 





 











 





 





 


























 





 









        
        
        
        
        
            
        

        

        
            
        

        
                

                    

                        
 
                            

                                
                                
                                
                                #1
                                

                                    
                                        André Arnaud de Calavon Profile Picture
                                     
                                

                                

                                    

                                        
















 





 











 





 

  











 





 











 





 








     

  

 
    

    
      

        
        
         
         

        
            
            
             
                
                
            
            
        
        
        

        
             
                 
                André Arnaud de Cal...
                290,564 
            
         

            
                
                     
                        
                             
                                
                                    
                                        
                                        Super User 2024 Season 2
                                        
                                    
                                
                                    
                                
                                    
                                
                                    
                                
                            
                         
                     
                 
            

    


                                         
                                    

                                

                            

                            
                        

                    

                

                
        
  

        
        
        
        
        
            
        

        

        
            
            
        

        
                

                    

                        
 
                            

                                
                                
                                
                                #2
                                

                                    
                                        Martin Dráb Profile Picture
                                     
                                

                                

                                    

                                        
















 





 











 





 

  











 





 











 





 








     

  

 
    

    
      

        
        
         
         

        
            
            
             
                
                
            
            
        
        
        

        
             
                 
                Martin Dráb
                228,651 
            
         

            
                
                     
                        
                             
                                
                                    
                                        
                                        Most Valuable Professional
                                        
                                    
                                
                                    
                                
                                    
                                
                                    
                                
                            
                         
                     
                 
            

    


                                         
                                    

                                

                            

                            
                        

                    

                

                
        
  

        
        
        
        
        
            
        

        

        
            
            
        

        
                

                    

                        
 
                            

                                
                                
                                
                                #3
                                

                                    
                                        nmaenpaa Profile Picture
                                     
                                

                                

                                    

                                        
















 





 











 





 

  











 





 











 





 








     

  

 
    

    
      

        
        
         
         

        
            
            
             
                
                
            
            
        
        
        

        
             
                 
                nmaenpaa
                101,148 
            
         

            
                
                     
                 
            

    


                                         
                                    

                                

                            

                            
                        

                    

                

                
        
  


  
                Leaderboard 
            

            
        

        

    






    
    

        
Featured topics

    

    

  

        

            
Product updates
 
            

            Dynamics 365 release plans