Advertisements

Creating Views – to Save Filtered Lists in NAV

Do you Know you can save the Page as View with filters.

Many of time we access to Page and apply same set of Filters to fetch our data. You can save the same for quick retrival of data applying the filters automatically in future access.

You save the Page with Filters as View.

Let us see small example how to achieve it.

First we will open the List Page in my case i am having one customized Customer List.

FilteredView-1

Suppose this is the Page which i use for Balances Retrival each month and on daily basis.

I Enter Filter as Balance > 5000 & Current Month Filter whenever i wish to check balances for my reporting or followup purpose.

For Date Filter i use (-CM..CM) sothat when i open this view next month it takes the appropriate Filter for that month.

FilteredView-2

Click on the Page Name post applying all the required Filters.

Select Save View as.

Give the Name for the View.

Select the Location to save the View.

On Confirmation respond as Yes.

FilteredView-3

Your View will be Saved, You can create as much Views as desired using different Filters.

FilteredView-4

Small and good Feature.

What you are waiting for give it a try.

I will comeup with more information in my upcoming posts, till then keep exploring and learning.

 

 

Advertisements

Filter Pages for Filtering Tables using FILTERPAGEBUILDER

In C/AL code, you can use the FILTERPAGEBUILDER data type to create a filter page that enables users to set filters on multiple tables. Filter pages contains one or more filter controls, where each filter control can be used to set filters on a specific table. In the Microsoft Dynamics NAV client, filter pages are generated at runtime and run in a modal dialog box.

For demo purpose I have created a List page on Item Table. I have added below Variables and Code on Page.
filterpagebuiler-1
When I Run the Page, First Request page is popup to accept filters. Then my Item List Page is opened applying the filter I provided on the Request Page.
filterpagebuiler-2
FILTERPAGEBUILER Data Type Functions

ADDTABLE Function

Adds a filter control for a table to a filter page.

ADDRECORD Function

Adds a filter control for a table to a filter page as specified by a record data type variable.

ADDRECORDREF Function

Adds filter control for a table to a filter page as specified by a recordref variable.

ADDFIELD Function

Adds a table field to the filter control for a table on filter page.

ADDFIELDNO Function

Adds a table field to the filter control for a table as specified by the field number.

GETVIEW function (FilterPageBuilder)

Gets the filter view (which defines the sort order, key, and filters) for the record in the specified filter control of a filter page.

SETVIEW Function

Sets the current filter view, which defines the sort order, key, and filters, for a record in a filter control on a filter page.

RUNMODAL Function (FilterPageBuilder)

Builds and runs the filter page.

COUNT Function (FilterPageBuilder)

Gets the number of filter controls that are specified in the FilterPageBuilder object instance.

NAME Function (FilterPageBuilder)

Gets the name of a table filter control that is included on a filter page based on an index number that is assigned to the filter control.

How to add Filter for data retrieval in PowerPivot Data model.

Recall from previous posts we have added Item Ledger Entry Table to our Data Model of PowerPivot.

Adding more tables to the Data Model using Existing Connection – In PowerPivot

Add relationships to Data Model in PowerPivot

My requirement is to retrieve only records related to Sales.

We will apply filter on Entry Type field to value [1]-[Sales] this way Item Leger Entry Sheet will have only Sales Data.

To do this we will add this filter to the query used for data retrieval.
PowerPivot-15

Select the Sheet for table – Item Ledger Entry from bottom.

Click on Design Tab on Top.

Now From Ribbon choose Table Properties.

PowerPivot-16

Select Query Editor from Switch to.

Add the Filter to your query.

Save your Query.

PowerPivot-17

From Home Tab, Select Refresh from Ribbon.

On Success completion Close the window.

Now your Item Ledger Entry is populated with Fresh data as per the modified Query.

I.e: All Entries which have Entry Type = 1 [Sales].

I will come up with more details in my upcoming post, till then keep practicing and stay tuned for future updates & posts on this topic.

Security filter—record-level security

Security filters—record level security/access control can be explained by for accessing G/L Accounts. Let’s take an example where we want the user to view only balance sheet G/L Accounts.

To accomplish the record-level security, we use security filters from the Permissions window. Click on the Assist Edit button in the Security Filters field in the Permissions window and select the field on which we want to apply filters.

SecurityFilter

Record-level security is available only if we are using the SQL database, need not to mention for NAV 2015.

How to: Set Security Filters

You set security filters to limit the access that a user has to data in a table. You set security filters on permission sets, which you assign to users.

To set a security filter

In the Search box, enter Permission Sets, and then choose the related link.

On the Permission Sets page, select the permission set to which you want to add a security filter, and then choose Permissions.

On the Permissions page, on the row for the table data to which you want to add a security filter, in the Security Filter column, choose the AssistEdit button.

The Table Filter page opens.

In the Table Filter page, in the Field Number column, select the field on which you want to limit a user’s access.

For example, if you want to create a security filter so that a user can view only sales with a specific salesperson code, then choose the field number for the Salesperson Code field.

The Field Caption column in the Table Filter page is filled in automatically after you select the field number.

In the Field Filter column, enter the value of field that you want to use to limit access.

For example, to limit a user’s access to only Annette Hill’s sales, enter AH, which is the salesperson code for Annette Hill, in the Field Filter column.

Note

Record level security filters do not support wildcard characters. This means that you cannot use * and ? in the filters. You can use other symbols, delimiters and, operators, such as, <, >, |, &, .., and =. If you do not enter an operator, then the default operator = is used.

Security filters support Unicode characters. The maximum length of a security filter is 200 characters, including all field names, delimiters, symbols, and operators that used in the filter.

When multiple permission sets that refer to the same table data are assigned to a user, they are combined so that the least restrictive filter is used. You should not repeat a table in multiple permission sets if you plan to combine those permissions sets for one user.

Security Filter Modes

Query objects and Record objects, including both explicit record variables and implicit records on pages, reports, or XMLports, have a property named SecurityFiltering, which describes how security filters are applied.

The possible values of the SecurityFiltering property are: Filtered, Validated, Ignored, Disallowed

Filtered

If a record is set to Filtered, then any security filters that have been set are respected for this instance of the record. To the user, it is as if records outside the security filters that do not exist.

An example scenario in which you use the Filtered value is that one salesperson is not allowed to view customers, sales quotes, or sales orders that belong to another salesperson. When the salesperson opens a list of customers or sales documents, or runs reports such as the Customer – Top 10 List report, he sees his filtered view of customers, sales quotes, and sales orders.

Validated

If a record is set to Validated, then any security filters that have been set are respected for this instance of the record, and an error occurs if the code attempts to access a record that is outside the range of the security filters.

An example scenario in which you use the Validated value is that one parts purchaser in a warehouse is responsible for only one location and can view and create purchase orders for only that location. However, some purchase orders have lines for multiple locations. Only a parts purchaser who has access to all locations should be allowed to post these purchase orders that have lines for multiple locations. All other purchasers receive an error.

If security filters have been set, then the Validated value decreases performance.

The Validated value is used mainly for compatibility with the security model in earlier versions of Microsoft Dynamics NAV. MS recommend that you use the other modes when you implement your security model.

Ignored

If a record is set to Ignored, then any security filters that have been set are ignored for this instance of the record.

An example scenario in which you use the Ignored value is that one salesperson is not allowed to view customers, sales quotes, or sales orders that belong to another salesperson. However, to post his sales orders, the salesperson must have access to all entries in the Customer Ledger Entry table so that the posting algorithm can find the last sales order entry number and generate the correct number for the next entry. In the code that posts sales orders, you set the SecurityFiltering property of the record variable to Ignored, but for record variables in other parts of the code, you set the SecurityFiltering property to either Filtered or Validated.

When you set the SecurityFiltering property on a record to Ignored, the administrator who sets a security filter is not informed that the security filter will be ignored. You must be careful when you set the SecurityFiltering property that it does not cause unintended information disclosure.

Disallowed

If a record is set to Disallowed, then setting any security filter on the record causes an error.

An example scenario in which you use the Disallowed value is that you have batch jobs that must be run only by users who have access to all records in the related tables, otherwise the batch jobs can cause incorrect and potentially harmful results. The partner developer sets record variables on these batch jobs to Disallowed so that a user who does not have access to all records in the related tables receives an error.

Important

It is not supported to change the default Filtered value of the SecurityFiltering property on implicit records on pages.

If you keep the default values after you upgrade from Microsoft Dynamics NAV 2009 to Microsoft Dynamics NAV 2015, then the behavior is the same in all cases except the following:

In earlier versions, the COUNT Function (Record) ignored security filters and always returned the total number of records unless you called the SETPERMISSIONFILTER function to get a filtered count. In Microsoft Dynamics NAV 2015, the COUNT function adheres to the SecurityFiltering property.

In earlier versions, on a page, you could modify or insert a record outside of the range of your security filters but in Microsoft Dynamics NAV 2015, you cannot.

Note

For Query variables, the Validated value of the SecurityFiltering property is not allowed.

Security Filters and FlowFields

If you set a security filter on a table that is used in a FlowField calculation, then the calculated value of the FlowField is filtered, based on the security filter and the security filter mode of the record variable for the record in the table.

For example, if you set a security filter so that a user can only view sales with a specific salesperson code, and if the security filter mode is Filtered, then when the user views a FlowField that calculates total sales, the user can see the total of only those sales that have the specific salesperson code. In earlier versions of Microsoft Dynamics NAV, the security filter mode value was Validated and in this example, the user received an error.

Programming Examples

For these examples, you have a table that has 100 records. Each record has an ID field that is the primary key of the table. The values of the ID field that are currently in the database range from 1 to 100. In this example, you set a security filter on the table data for ID=1..50. Then, in a codeunit you create a record variable for the table.

Security filter mode Example
Filtered If you set the SecurityFiltering property on the record variable to Filtered, then the code behaves as if records with ID values from 1 to 50 are the only records that exist.

If you call the FIND, FINDFIRST, FINDLAST, or FINDSET function on the record variable without any additional filters besides the security filter, then the function only finds records with ID values from 1 to 50.

If you call the DELETEALL function, then it successfully deletes records with ID values from 1 to 50. It does not delete records with ID values greater than 50, and it does not return an error.

If you modify a record, for example, by calling the MODIFY function on a record with an ID value less than 50, then the function succeeds. If you call the MODIFY function on a record with an ID value greater than 50, then it fails because you do not have access to the record.

If you insert a record with an ID less than 50 and the record does not already exist, the INSERT function succeeds. If you insert a record with an ID greater than 50 and the record does not already exist, it fails because you do not have access to the record.

If you call the GET function on a record with an ID value less than 50, then the function succeeds. If you call the GET function on a record with an ID value greater than 50, then it fails.

Validated If you set the SecurityFiltering property on the record variable to Validated, then the code behaves as if records with ID values from 1 to 100 exist, but you only have access to the records that are specified by the security filter.

If you call the FIND, FINDFIRST, FINDLAST, or FINDSET function on the record variable without additional filters, it finds all 100 records, but a NEXT function call to the record with ID value 51 fails because you do not have access to it.

If you call the DELETEALL function, it fails because it finds all 100 records but you do not have access to all of them.

If you modify a record with an ID value less than 50, it succeeds. If you modify a record with an ID value greater than 50, it fails.

If you insert a record with an ID less than 50 and the record does not already exist, it succeeds. If you insert a record with an ID greater than 50 and the record does not already exist, it fails.

Ignored If you set the SecurityFiltering property on the record variable to Ignored, then the code behaves as if all security filters that are set do not exist for that instance of the record.

Any of the FIND functions find all 100 records.

The DELETEALL function deletes all 100 records.

You can insert and modify records in any range, regardless of security filters.

Disallowed If you set the SecurityFiltering property on the record variable to Disallowed, then as long as a security filter is set on the record, any code that uses the record variable causes an error.

Performance Impact of Security Filtering Mode

If security filters are set on a table, then setting the SecurityFiltering property to Validated on a record instance of that table causes a decrease in performance.

The Microsoft Dynamics NAV Server must go through every record in the table to validate the record instead of adding the filters to the query that is sent to SQL Server.

If security filters are not set, then setting the SecurityFiltering property to Validated has no performance impact.

Note

MS recommend that you change commonly used record variables from the default value of Validated to either Filtered or Ignored to improve performance.

Important

Most of the contents you find in this blog will be either inherited from MSDN or Navision Developer IT Pro Help. Some places images are also directly taken from these sites. Purpose is simple to try those stuffs and re-produce adding few things as per my understanding to make easy understanding for others and quick reference.

Here nothing under my own brand or authorship of the content. At any point of time we are just promoting Microsoft stuffs nothing personnel with same.

Hope stuffs used here will not violate any copyright agreement with them. In case by mistake or in-intestinally it happens and the Microsoft feels these should not be used Microsoft have full right to inform me about same and will be glad to take down any such content which may be violating the norms.

Purpose is to promote Navision and share with community.

FB Profile

Like FB Page

%d bloggers like this: