DataPort - being careful to erase a field, populate a field

shogan@mila.ie · 2010-07-09

Hi everyone,

Hope you are well.

I do have the PACKT "Programming Microsoft Dynamics NAV" book, but this and the forum hasn't really hinted at what I want to achieve.

We have a custom text field on the item card that I need to erase. I was thinking of using a Dataport to do this, and having all of the Item numbers in a CSV file, and the text field populated with '' (or something similar) in the second column.

Really, on thinking about it, would it not be better to write a loop that does something similar to:

For each Item in Items Do
   Item.textField.Value = '';
Next item

(I know, it's not perfect!)

(or even be more efficient and test the field for being null/not null already...)

Secondly, I would like to import a CSV file where some items will have a value populating the same text field. My only worry here (and it has happened before) is that the supplier of the list may have item numbers on the Excel sheet that do not exist in our system, and thus start creating new items with no associated data!

In the Dataport, where can I test that the Item No. in the CSV file matches an Item no. in the Item list, and populates the text field from the second column on the CSV file for the item in question?

My main reason is that different items may need the text field populate this year from last year.

Thanks for your help guys in gals

Note - we upgraded to NAV 5.0 SP1 Update 2 mid-June. Still suffering somewhat!

David_Singleton · 2010-07-09

shogan@mila.ie wrote:
We have a custom text field on the item card that I need to erase. I was thinking of using a Dataport to do this, and having all of the Item numbers in a CSV file, and the text field populated with '' (or something similar) in the second column.

Really, on thinking about it, would it not be better to write a loop that does something similar to:
For each Item in Items Do
   Item.textField.Value = '';
Next item
(I know, it's not perfect!)

(or even be more efficient and test the field for being null/not null already...)

:?: This is generally the way I think most people would do it. That or a non printing report. I have never heard of using a dataport to do this, and would never do it that way myself.

By the way don't forget the MODIFY;

Marije_Brummel · 2010-07-09

what about

Item.MODIFYALL("New Field", '');

David_Singleton · 2010-07-09

shogan@mila.ie wrote:

Secondly, I would like to import a CSV file where some items will have a value populating the same text field. My only worry here (and it has happened before) is that the supplier of the list may have item numbers on the Excel sheet that do not exist in our system, and thus start creating new items with no associated data!

In the Dataport, where can I test that the Item No. in the CSV file matches an Item no. in the Item list, and populates the text field from the second column on the CSV file for the item in question?

My main reason is that different items may need the text field populate this year from last year.

Create a new variabl in the dataport ItemImport record of Item. Turn off all the auto options in the dataport. In the first line of the onimportdata add something like:

If ItemImport.get("no.") then begin
  // Item exists in db...
end else begin
  // Item dos not exist
end;

shogan@mila.ie · 2010-07-09

Thanks for coming back to me guys.

Mark - I assume that MODIFYALL does not need a SETRANGE or SETFILTER if it is a 'global' change?

Savatage · 2010-07-09

A report on the item table is the way to go if you simply want to clear a field in the table for every record.
if you don't understand Marks post - you're basically doing
*Report*Dataitem*Item

OnAfterGetRecord()
CLEAR(YourFieldNameHere);
Modify;

Or 1 line with Marks code.

Using a dataport is weird but possible as david showed.

On the populating side, I would import the data into variables and map them OnAfterImport
If ItemImport.get("no.") then begin
// Item exists in db...
end else begin
// Item dos not exist **you can do a CurrDataport.SKIP; here
end;

David_Singleton · 2010-07-09

Mark Brummel wrote:

what about

Item.MODIFYALL("New Field", '');

Yes MODIFYALL is another option. Its just one of those functions that I never use. The main reason is becasue though various versions of Navision, the function has acted differently and never seemed ver stable to me. For example if you look at the online help:

Record.MODIFYALL(Field, NewValue [, RunTrigger])
RunTrigger
Data type: boolean
This parameter lets you run the C/AL code in the OnModify trigger.

Yet the RunTrigger paramater does not work. *

In early Native versions of Navision we were told that the reason to use MODIFYALL is becasue the client sends a single request over the network to the server and the entire command runs on the server. Of course if it is running on the server, then there is no way that validating the OnModify trigger can be possible.

And since 2.50 when SQL option came out the function has been unreliable and since it seems that now the client scans the table and modifies each record individually (even in Native) there is no performance advantage.

I am sure the function is now stable and works fine (if you don't use the RunTrigger paramater) but I just remember lots of problems so stopped using it.

*I guess someone is now going to tell me that it's been fixed.

shogan@mila.ie · 2010-07-09

Always bit scared making global changes to such a critical table, I opted for Savatage's approach on a test db and it worked perfectly.

But now I am looking at David's import solution -

IF ItemImport.GET("No.") THEN BEGIN

   // Item exists in db...
   // But how would I commit the value found in the csv file into the text field here?
   // Item."textfield" := ItemImport."textfield";  ???????

END
ELSE BEGIN   // Item does not exist
   
   MESSAGE('%1\', ItemImport."No.");  // to flag what item no.'s are in the CSV but not an Item record - can I do this?
   CurrDataport.SKIP;

END;

Savatage · 2010-07-09

You can create a file that contains all the item numbers that don't exist.
In Globals..
ExportFile ->Type "File"

OnPreDataport Trigger
ExportFile.Create('C:\Non_Existing_Numbers.txt');
ExportFile.TEXTMODE(TRUE):
ExportFile.WRITEMODE(TRUE);

OnPostDataport Trigger
ExportFile.CLOSE:

What is ItemImport.<Field>? ItemImport?

I would Create Variables to import your data into.
Type in these variables in the "Dataport Fields"

OnAfterImportRecord Trigger

IF Item.GET("varItemNo.") THEN BEGIN
   Item."textfield" := "varTEXTFIELD"; 
END
ELSE BEGIN 
   ExportFile.WRITE("VarItemNo.");
   CurrDataport.SKIP;
END;

something like that - test first in test database!!!

They ket to updating just one field without clearing everything else out is the
AutoSave, AutoUpdate & AutoReplace properties. Make sure you set them correctly.
The Application Designers Guide that comes with the product CD as a chapter on DATAPORTS for further info.

shogan@mila.ie · 2010-07-09

I actually think I have it....

Looking at http://www.mibuso.com/forum/viewtopic.php?f=23&t=10837&start=0 I've modified my code...

IF ItemImport.GET("No.") THEN BEGIN
   
   // Item exists in db...
   ItemImport."Text 5" := Item."Text 5";
   ItemImport.MODIFY(TRUE);


END
ELSE BEGIN   

   // Item does not exist   
   MESSAGE('%1\', ItemImport."No.");
   CurrDataport.SKIP;

END;

...and it seems to work

Let me know if you think I am incorrect.

Logic initially seemed to be reversed to the way I would have := the fields, but it is starting to make sense to me now.

shogan@mila.ie · 2010-07-09

Savatage/Harry - excellent, as I had thought of that, but did not want to over-extend my welcome on the issue.

Was also thinking of using the ENVIRON() to force the text file to be created on the desktop; (I've done this in the past - bit of a DOS batch file whizz here!)

ItemImport is a Record of object Item - maybe I should rename this to recItem?

It's now 5pm on Friday - about to go home. Over the weekend I'll play with this and see how it works on the test system.

shogan@mila.ie · 2010-07-12

Well thanks to you all, I have written my first DataPort that validates Item No.'s during the import:

Dataport - OnPreDataport()
// Creates a text file that records item no.'s in the import file that do not exist in the Item table.
// File is saved on the user's desktop.

desktopLoc := ENVIRON('USERPROFILE')+'\Desktop\';

exportFile.TEXTMODE := TRUE;
exportFile.CREATE(desktopLoc + 'Non_Existing_Numbers.txt');
exportFile.WRITEMODE := TRUE;

Dataport - OnPostDataport()
// Close the file holding all item no.'s in the import file that do not exist in the Item table.

exportFile.CLOSE;


Item - OnAfterImportRecord()
IF Item.GET(varItemNo) THEN
BEGIN
   
   // Item exists in Item Table...
   
   Item."Text 5" := varText5;
   Item.MODIFY(TRUE);


END
ELSE BEGIN

   // Item does not exist in Item Table...
   
   //MESSAGE('%1\', varItemNo);
   exportFile.WRITE(varItemNo);
   CurrDataport.SKIP;

END;

I notice that you have to set the TEXTMODE of the file before creating it (makes sense), and also setting the TEXTMODE and WRITEMODE is of a slightly different syntax.

Nonetheless, I like learning all these things now.

What I may attempt to do now is create a form that clears down this text field and imports from a CSV file so that a user can use it.

I'll need to build in permissions/restrictions, and maybe more meaningful messages, but for now, this is perfect for what I want it to do.

Cheers guys

The datalore

David_Singleton · 2010-07-12

Great that you solved the issue.

One thing though. Its relly important when you get started in Navision to do things "The Navision Way" form the start. If you don't, and instead, you decide to do things your own way, you will find more an more frustrations down the line.

So when you do a begin end else then write it as

If condition THEN BEGIN
  // two spaces to indent
END ELSE BEGIN
  // The begins are always on the line above except for a CASE statement
END; // always put a semi colon even at the end where it is not needed.

Make sure to read the style guide and follow the variable naming conventions. Don't EVER put prefixes on Variables (dec int rec etc) they turn the code into unreadable spaghetti, even if you understand it, the next programmer wont, but more importantly you will be integrating your code with code from other developers and of course the base product. So use Text5var instead of varText5. If you start mixing styles the code is unreadable, its impossible to debug, and when you make a mistake of missing a begin or using the wrong variable name it will waste hours to find it.

It absolutely does not matter what standard we use, provided we all use the same one, and that has to be the one that Navision set.

Howdy, Stranger!

Quick Links

Categories

DataPort - being careful to erase a field, populate a field

Answers