Here I am with the second part of my Bootcamp notes: Pages & Queries.
I will again list all new and important stuff only.
- Grid Layout: next to fixed layout, we can now use a grid layout in Pages. Basically, we can force multiple columns on a single row, and sort of manage how it will look. We can define the GridLayout to use subgroups as rows, or to use them as columns.
- Page Types: NAV 2013 has become more strict on the page types, because the SharePoint or WebClient will just render them differently…
- Use worksheet instead of list for journals. These are typically recognized by a field before the repeater. If there is a field above the repeater, web services will use this as a parameter.
- Use type document when showing detail lines, do not use a card.
- Use ListPlus for master records with detail list parts, instead of Document type. This is how Microsoft does it.
- Confirmation Dialog: Show information while asking confirmation. The information is not editable and not customizable for the user. Example: Availability Notice.
- Navigate Page is used for wizards / navigate pages which use “Under Tabs”, actions are shown as buttons with property “InFooterBar”. Please remember that these are not supported in the WebClient! Create different SubTabs by creating groups. The first group is the first tab. Then you need to add a list part, all groups after will be seen as other tabs.
- StandardDialog: This is a new type. You can add fields without FastTabs. An OK and Cancel button is shown. The fields can be editable, but the Page is still not customizable for the user. Example: AssistEdit on Currency Code of Sales Order.
- Charts are not supported in the WebClient
- The action menus have been replaced with a ribbon. We can see different tabs here:
- Home Tab: System Actions + User / Developer promoted actions
- Navigate: Actions in group RelatedInformation
- Actions: Functions added by Developer (actions to execute on the record) – NewDocumentItems
- ActivityButtons group is used for example for Posted Documents
- User can add more Tabs, developer can not
- Action Images are selectable from a list. (Help is not up to date, the list on MSDN is)
- Separator can be added, but is not working
- Preview Mode on Pages: click any field in the preview to select the field in the designer. Right click a field to get a context menu and to be able to view properties or go to the code of the field/action
- Up to 10 categories for Promoted Actions
- Quick Entry Property: Default true. Setting it to false, allows the user to skip fields when tabbing. You can use a variable to dynamically set this property.
Well, Queries is a new object type. It does what you expect it to do. You can “join” data from multiple tables into a single call to SQL. However, the data returned is read-only. And also remember that data from queries is not (yet) cached by NST…
When creating Queries, it will definitely help if you know how to make SQL statements to SQL Server directly.
- Adding filters on a DataItem: These cannot be removed.
- Add a filter on a DataItem through code: this will merge with the DataItem Table Filter
- Select a column to show, and you can filter on it when calling the query by SetRange and SetFilter
- Add a filter directly in the properties of a column.
- Add a column of type Filter for data on which you will allow filtering, but do not want the data returned
- No FlowFilters
- You can use FlowFields though (for data catching & filtering)
Group By property is automatically calculated when using Totals. You can make totals by the following agregate functions: Average, Max, Min, Sum and Count.
Select to only return “Top Number Of Rows” with your Query. (Possible to override when calling the Query).
Set correct link between DataItems:
- Use Default Values if No Match: Return all records from base DataItem, even if no match to child DataItem
- Exclude Row if No Match: Do not show the record when no link was found in the child DataItem
- SQL Advanced Options: Please take a look at the NAV help files for more information. Microsoft took the time to explain it quite nice, even with examples and graphics.
- Inner Join
- Left Outer Join
- Right Outer Join
- Full Outer Join
- Cross Join
NAV typically uses Queries for charts.
Other options for the Query:
- OrderBy: Order the records how you want
- ReadState: ReadUncommitted, ReadShared and ReadExclusive. The first is default, and can result in dirty reads. ReadShared & ReadExclusive only read committed data, but locks are placed on the data read to make sure nothing is updated in between. Again, check help for more detailed information. (ReadUncommitted is also the most performing one)
- Method Type on columns: Totals & Date. Date can be set on a date to extract a part of a date with the Method Day, Month or Year.
Using Queries in code:
MyQuery.SETRANGE("Some Field",'Some Filter');
MyQuery.TOPNUMBEROFROWS := 0; // Override TopNumberOfRows in the Query Object
IF MyQuery.OPEN THEN
WHILE MyQuery.READ DO BEGIN
MESSAGE(MyQuery."Some Other Field");
Notice that instead of Repeat Until, we use While Do.
There is actually not much more about Queries. Just open one and play around with it. I am sure you will find good use for them and maybe replace some nested repeats for data checking with simple and much faster Queries…