{"id":586,"date":"2010-09-16T07:03:22","date_gmt":"2010-09-16T14:03:22","guid":{"rendered":"http:\/\/45.63.48.66\/?p=586"},"modified":"2019-05-02T14:54:44","modified_gmt":"2019-05-02T21:54:44","slug":"cloning-extending-data-templates-in-dynamics-nav-using-tablefilter","status":"publish","type":"post","link":"https:\/\/gotcal.com\/index.php\/2010\/09\/cloning-extending-data-templates-in-dynamics-nav-using-tablefilter\/","title":{"rendered":"Cloning \u2013 Extending Data Templates in Dynamics NAV using TableFilter"},"content":{"rendered":"

\"\"Since Dynamics NAV version 5, when RIM was introduced there has been the functionality for Data Migration and Master Data Templates. I have never really used the Migration tool, as it just seemed to be too limited, compared to what you can accomplish with standard dataports. During my last go-live the customer was using the Master Data Templates, which is a nice little tool – still with a lot of limitations.<\/p>\n

The usages I have seen of the tool has primarily been around the common master data tables, such as Customer, Vendor and Items. Often there are some confusion around templates used for Customers, as there are “Data Templates” (generic templates) and “Customer Templates” (customer specific templates used from Contacts \/ Relationship Management).<\/p>\n

Lets get into some details, (in the classic client): <\/p>\n

The setups are found under “Administration->Application Setup->General->Setup Master Templates”:<\/p>\n

\"\"
\n<\/span><\/p>\n

The setup screen is pretty much self explaining, give you template a name, set the table it is related to, and then fill in the fields and their default values:<\/p>\n

\"\"
\n<\/span><\/p>\n

That is the setup part of it. Very simple!<\/p>\n

Now let’s look at the Item Card, and the code that triggers the template functionality. This is located on the Item Card, Functions, Apply Template:<\/p>\n

\nRecRef.GETTABLE(Rec);\nTemplateMgt.UpdateFromTemplateSelection(RecRef);\n<\/pre>\n
So it uses a RecordRef variable, which is initialized with your current form record. Then you call the UpdateFromTemplateSelection in codeunit 8612 Template Management. This opens up a form with all associated templates for your current record, and when you select one, it updates the fields on your master data.<\/p>\n
Notice if the fields have dependencies, like eg. “Base Unit of Measure” that needs to have a record created in the “Item Unit of Measure” table, then it will fail, if this hasn’t been created ahead of time.<\/p>\n
The functionality can be found here in standard NAV: Item, Customer, Vendor and Contact. Maybe other places too, that I haven’t seen, but the code to add is very simple.<\/p>\n
So yesterday I talked to colleague Henrik (aka E-Ship Guy<\/a>), he was about to do a mod for a customer that needed a generic report to update master data records. Without going into any detail of the original approach, we ended up using the Data Templates with a small modification to do batch updates on data. Lets see how it was done:<\/p>\n
On table  8618 Data Template Header I added a custom field of type TableFilter. This fieldtype is not commonly used and wouldn’t be of much use without the TableFilter2View function created by another of my colleagues Peter D. J\u00f8rgensen (see this post on Mibuso.com<\/a>).<\/p>\n
\"\"
\n<\/span><\/p>\n
In order to get the TableFilter fields working, you need to look into the properties:<\/p>\n
\"\"
\n<\/span><\/p>\n
The property “TableIDExpr” is the key here. It needs to have a source expression that tells it the table id. As we already have a field for TableID (field 3), we using this.<\/p>\n
Now open the table, and click on the assist button in the field:<\/p>\n
\"\"
\n<\/span><\/p>\n
You will see a standard filter form (like when you do CTRL+F7), on doing the lookup in the field column takes you to the fields of your source table.<\/p>\n
After setting your filters, you will see  the value stored in the field in a format like this:<\/p>\n
“Item: No.=1*,Manufacturer Code=CISCO”<\/p>\n
Our goal is to use this on a RecordRef with SETVIEW. Unfortunately the syntax is slightly different, and this is where Peter’s function comes in handy, it translates it into this:<\/p>\n
“WHERE( No.=FILTER(1*),Manufacturer Code=FILTER(CISCO))”<\/p>\n
Lets add the functionality to form 8618, to do batch updates. Here are the steps:<\/p>\n
    \n
  1. Add the new field to the form, and set AssistEdit=Yes. (otherwise the assist edit will not work)<\/li>\n
  2. \n
    Add a new menuitem under functions and add this code:<\/div>\n
    \n\/\/TESTFIELD("Data Filter");\nRecRef.OPEN(TableID);\nRecRef.SETVIEW(TableFilter2View(FORMAT("Data Filter")));\nIF NOT CONFIRM(\nSTRSUBSTNO('This will update %1 record(s) in table ''%2'', continue?\\\\Filters: ''%3''',\nRecRef.COUNT,RecRef.NAME,RecRef.GETFILTERS),FALSE)\nTHEN\nERROR('');\nWindow.OPEN('Applying template @1@@@@@@@@');\nIF RecRef.FINDSET THEN REPEAT\nCurrRec += 1;\nWindow.UPDATE(1,ROUND(CurrRec \/ RecRef.COUNT * 10000,1));\nTemplateMgmt.UpdateRecord(Rec,RecRef);\nUNTIL RecRef.NEXT = 0;\nWindow.CLOSE;\n<\/pre>\n<\/li>\n<\/ol>\n
    Important notice: A TESTFIELD on a TableFilter seems to crash the client, so it is commented out for now!<\/p>\n
    That is it, enjoy. Next I will show you how to use the Data Templates for mandatory field management. Stay tuned!<\/p>\n
    The function from Peter in its original version is here:<\/p>\n
    \nTableFilter2View(p_TableFilter : Text[1024]) r_View : Text[1024]\n\/\/ Funtion made by pdj - Aalborg\n\/\/ Feel free to use, but please keep all comment lines and report any errors at &lt;a href="http:\/\/mibuso.com\/forum\/viewtopic.php?t=7587"&gt;http:\/\/mibuso.com\/forum\/viewtopic.php?t=7587&lt;\/a&gt;\n\/\/ TableFilter format:\n\/\/ &lt;TableName&gt;:&lt;FieldCaption&gt;=&lt;FieldFilter&gt;,&lt;FieldCaption&gt;=&lt;FieldFilter&gt;,..\n\/\/ View format:\n\/\/   [SORTING(&lt;Key&gt;)] WHERE(&lt;FieldCaption&gt;=FILTER(&lt;FieldFilter&gt;),&lt;FieldCaption&gt;=FILTER(&lt;FieldFilter&gt;),...)\nIF p_TableFilter = '' THEN\nEXIT('');\nr_View := 'WHERE(';\nFOR l_CharNo := STRPOS(p_TableFilter,':') + 1 TO STRLEN(p_TableFilter) DO BEGIN\nCASE p_TableFilter[l_CharNo] OF\n'=': r_View := r_View + '=FILTER(';\n',': r_View := r_View + '),';\n'"':\nBEGIN\nl_CharNo := l_CharNo + 1;\nREPEAT\nr_View := r_View + FORMAT(p_TableFilter[l_CharNo]);\nl_CharNo := l_CharNo + 1;\nUNTIL p_TableFilter[l_CharNo] = '"';\nl_CharNo := l_CharNo + 1;\nEND;\nELSE\nr_View := r_View + FORMAT(p_TableFilter[l_CharNo]);\nEND;\nEND;\nr_View := r_View + '))';\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
    Since Dynamics NAV version 5, when RIM was introduced there has been the functionality for Data Migration and Master Data Templates. I have never really used the Migration tool, as it just seemed to be too limited, compared to what you can accomplish with standard dataports. During my last go-live the customer was using the […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[56,3],"tags":[77,26,38,27,7,39],"class_list":["post-586","post","type-post","status-publish","format-standard","hentry","category-basic-cal","category-navision","tag-navision","tag-filter","tag-guide","tag-how-to","tag-objects","tag-programming"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/posts\/586","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/comments?post=586"}],"version-history":[{"count":11,"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/posts\/586\/revisions"}],"predecessor-version":[{"id":1037,"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/posts\/586\/revisions\/1037"}],"wp:attachment":[{"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/media?parent=586"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/categories?post=586"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/gotcal.com\/index.php\/wp-json\/wp\/v2\/tags?post=586"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}