Code in Dataport

[sarmig]

Hello,

Today’s task is: creating a Dataport!

I followed this walkthrough and it didn’t seem too hard:

msdn.microsoft.com/…/dd301335.aspx

But, obviously, real problems to solve don’t have walkthroughs…

If you remember my previous posts, I’ve been working with the Routing Header/Routing Line tables and this time is no exception.

My task is to create a dataport that imports the Run Time for all items into the Routing Line, but there are a few catches:

• If the item doesn’t have a Routing Header/Line, it has to be created.
• Despite what’s in the Routing Line of an object, the Routing Line Run Time that is to be imported has to be “Operation No.” = 1, which means that whatever’s already there has to become number 2 (2 Run Times for 2 operations).

I already have the .csv file with 3 columns filled (Routing No. (which is the item number), No. (another field which hasn’t been used until now) and Run Time).

My first problem is where to start. I know the code from creating Routing Header/Line will be used somewhere, but under which trigger?

Anyway, I’m just sharing my task with you. Any help you can provide will be much appreciated.

[sarmig]

Ok, so I started programming…

I created a dataport in the Routing Line data item (that where I want my records to be imported to).

Then, in the DataItemTableView, I chose to sort by Routing No.

I left the file format in “variable” because I don’t really know which one I should use.

And then, I chose the dataport fields. Since in my .csv the order is: Routing No., No. and Run Time, that was the order I set the fields in.

So far so good?

And then came the code…

I wrote this on the OnBeforeImportRecord, because I thought that it might make sense to create a Routing Line/Header and only then fill them with values.

This is what I came up with so far…

RecRoutingHeader.RESET;
RecRoutingHeader.SETRANGE(“No.”,RecItem.“No.”);

IF NOT RecRoutingHeader.FINDFIRST THEN BEGIN
RecRoutingHeader.“No.” := RecItem.“No.”;
RecRoutingHeader.Description := RecItem.Description;
RecRoutingHeader.Status := RecRoutingHeader.Status::Certified;
RecRoutingHeader.INSERT;
END;

RecRoutingLine.RESET;
RecRoutingLine.SETRANGE(“Routing No.”, RecItem.“No.”);

IF NOT RecRoutingLine.FINDFIRST THEN BEGIN
RecRoutingLine.“Routing No.” := RecItem.“No.”;
RecRoutingLine.“Operation No.” := FORMAT(‘1’);
RecRoutingLine.“Work Center No.” := “Work Center No.”;
RecRoutingLine.“Work Center Group Code” := “Work Center Group Code”;
RecRoutingLine.Type := RecRoutingLine.Type::“Machine Center”;
RecRoutingLine.INSERT;
END;

Most of this is copied from another implementation I made (you’ll know, if you followed my posts).

It compiles, but it’s not really working. Can you tell me what’s missing?

[MattTrax]

In the OnBeforeImport trigger, the data has not been imported yet. That means you don’t have access to the line from the filet that you need yet. You can really only deal with the data from the file AFTER you import it.

[sarmig]

Hello, once again.

I’ve postponed this task for a couple of days and now I’m on it again…

Basically what I want is to import a few fields into the Routing Line table.

My .csv file is ready and it has over 5000 entries.

I have to import 5 fields into the Routing Line table, the primary key of that table consists of 3 fields (Routing No., Operation No., and No.) (The explanation of this is that each product (Routing No.) can have one or more operations (Operation No.) and each operation has a No. (No. is like a location code).

I have to be careful because if a product doesn’t have a Routing Line/Header it has to be created (the code above should do it), but I have to be extra careful because most products, if not all, already have an operation in the Routing Line table, and that operation has to become the last, despite the number of operations being imported.

And that’s how it’s supposed to work. Now I’m going to try coding that into the OnAfterGetRecord trigger. I hope that you can correct my possible (and probable) mistakes once I post here my first version of the code.

[sarmig]

Ok, the first draft is this (don’t be too mean to me):

Routing Line - OnBeforeImportRecord()
RecordsNumber := RecRoutingLine.COUNT;

Routing Line - OnAfterImportRecord()
RecRoutingHeader.RESET;
RecRoutingHeader.SETRANGE(“No.”, “Routing No.”); //don’t really know if i have to set the filter in the header too

RecRoutingLine.RESET;
RecRoutingLine.SETRANGE(“Routing No.”, “Routing No.”); //don’t really know if i’m supposed to use a record variable of this table

IF RecRoutingLine.FINDFIRST THEN BEGIN
RecRoutingLine.“Operation No.” := FORMAT(‘RecordsNumber+1’); //puts the operation that was already there in the last place
NEXT;
REPEAT
RecRoutingLine.“Operation No.” := FORMAT(‘1’); // sets the first imported record as operation number 1. operation.no is “code”
NEXT;
UNTIL
RecRoutingLine.“Operation No.” = FORMAT(‘RecordsNumber’); // until it reaches the end of the imported records
END;

RecRoutingLine.MODIFY;

Is there anything that’s correct?

[MattTrax]

I’ll be honest, the code is very confusing. And I have to ask, have you gone through the chapters like “Working with Record Variables” in the NAV documentation?

Routing Line - OnBeforeImportRecord()
RecordsNumber := RecRoutingLine.COUNT;

I’m not sure what the purpose of this is, or if it would work. I honestly have never added code to OnBeforeImportRecord because I have never needed to. I don’t see any filters on it, so I’m assuming it’s a global and it is keeping the filters that you assign in the OnAfterImportRecord trigger?

The more common way to do this is all in one trigger / function. Something maybe like this.

RecordNumber := 0;
RecRoutingLine.SETFILTER…
IF NOT RecRoutingLine.ISEMPTY THEN
RecordNumber := RecRoutingLine.COUNT;
RecordNumber += 1;

RecRoutingHeader.RESET;
RecRoutingHeader.SETRANGE(“No.”, “Routing No.”); //don’t really know if i have to set the filter in the header too

You don’t ever use the RecRoutingHeader variable, so you don’t see to do anything with it.

NEXT;

This function is used to move to the next record in a data set. So if you have a record variable named MyRec with 5 records in it, you would use MyRec.NEXT to move to the next record. When you have it in the trigger like this, with no prefix, it uses the DataItem as the prefix. I’m not sure what the consequences are of having them there.

IF RecRoutingLine.FINDFIRST THEN BEGIN
RecRoutingLine.“Operation No.” := FORMAT(‘RecordsNumber+1’); //puts the operation that was already there in the last place
NEXT;
REPEAT
RecRoutingLine.“Operation No.” := FORMAT(‘1’); // sets the first imported record as operation number 1. operation.no is “code”
NEXT;
UNTIL
RecRoutingLine.“Operation No.” = FORMAT(‘RecordsNumber’); // until it reaches the end of the imported records
END;

You shouldn’t need to loop through anything. You can use the sample code above as a starting point to figure out what the Operation No. should be. Then you just need the one line:

RecRoutingLine.“Operation No.” = FORMAT(‘RecordsNumber’);

I hope that helps some. I strongly encourage you to go through some more basic example first.

[sarmig]

My problem is that I have a record with an entry before importing, with operation no = 1, and I can’t let that entry be overwritten after the other entries are imported. Is there a way I can save that entry at a “temporary” record and then after importing the rest, I can “paste” it after the last one?

[MattTrax]

Are you importing “Operation No.”? Or are you just trying to assign it a value in the code?

You can determine the last operation number used in code similar to what I provided:

NextOperationNo := 0;
Line.SETFILTER…
IF Line.FINDLAST THEN
NextOperationNo := Line.“Operation No.”;
NextOperationNo += 1;

If a line exists the result will be that lines operation number plus one. If a line does NOT exist, the result will be zero plus one.

Then you can do

“Operation No.” := NextOperationNo;

[sarmig]

Both! I’m importing several records with their operation number defined, and I’m assigning a new value to the operation number that was already there. And that new value has to be NumberOfRecordsImported + 1…

If I use that code, I won’t lose the record that was there before importing?

[sarmig]

I managed to do most of what I was trying to do.

I decided to try a different approach. I would import the data, overwriting what was there, and then inserting the record that was there (“with predefined values”) in the next new line.

For that, I decided on two dataports: one for importing new data, and another for importing the Routing numbers, and inserting data for each one.

As for the first dataport, everything is working. But for the second one, when I try to run it, it throws the error: “the routing line already exists”

Look at this code and tell me if you think I’m close. In order to know the number of operations, I’m using the integer variable RecordsNumber:

RecRoutingHeader.RESET;
RecRoutingHeader.SETRANGE(“No.”,“Routing No.”);

IF NOT RecRoutingHeader.FINDFIRST THEN BEGIN
RecRoutingHeader.“No.” := “Routing No.”;
RecRoutingHeader.Status := RecRoutingHeader.Status::Certified;
RecRoutingHeader.INSERT;
END ELSE BEGIN
RecRoutingHeader.Status := RecRoutingHeader.Status::Certified;
RecRoutingHeader.MODIFY;
END;

RecRoutingLine.RESET;
RecRoutingLine.SETRANGE(“Routing No.”, “Routing No.”);
RecordsNumber := RecRoutingLine.COUNT;

IF RecRoutingLine.FINDFIRST THEN BEGIN
RecRoutingLine.“Operation No.” := FORMAT (RecordsNumber+1);
RecRoutingLine.Type := RecRoutingLine.Type::“Work Center”;
RecRoutingLine.“No.” := ‘CT00017’;
RecRoutingLine.Description := ‘Controlo Final’;
RecRoutingLine.“Work Center No.” := “Work Center No.”;
RecRoutingLine.“Work Center Group Code” := “Work Center Group Code”;
RecRoutingLine.“Run Time” := 1;
RecRoutingLine.INSERT;
END;

[MattTrax]

Think about it with some sample data. Say you have the following lines

1
2
3

Now you are doing a FINDFIRST on your record set which will give you record number 1. You are adding 1 to that, which gives you 2. You try to insert that, but you already have a 2. So instead of doing a FINDFIRST you want to do a…

[sarmig]

A FINDLAST, because the next one is supposed to be empty.

I don even need the code to create a Routing Header in case it doesn’t exists, as the previous dataport already had that code…

So, now its like this…

RecRoutingHeader.RESET;
RecRoutingHeader.SETRANGE(“No.”,“Routing No.”);

RecRoutingLine.RESET;
RecRoutingLine.SETRANGE(“Routing No.”, “Routing No.”);
RecordsNumber := RecRoutingLine.COUNT;

IF RecRoutingLine.FINDLAST THEN BEGIN
RecRoutingLine.“Operation No.” := FORMAT (RecordsNumber+1);
RecRoutingLine.Type := RecRoutingLine.Type::“Work Center”;
RecRoutingLine.“No.” := ‘CT00017’;
RecRoutingLine.Description := ‘Controlo Final’;
RecRoutingLine.“Work Center No.” := “Work Center No.”;
RecRoutingLine.“Work Center Group Code” := “Work Center Group Code”;
RecRoutingLine.“Run Time” := 1;
RecRoutingLine.INSERT;
END;

Anf it still throws the same error…

[MattTrax]

The key for the routing line table contains three fields, Routing No., Version No., and Operation No. You are not filtering on or assigning a version number, but that doesn’t mean there isn’t a routing line with multiple versions.

Routing No. Version No. Operation No.
1 0 1
1 0 2
1 1 1

Now you code, only filtering on Routing No., will find the last record, which doesn’t have the same version. You need to filter on as many of the primary key fields as you can.

[sarmig]

The version number is not the problem, because, looking at the Routing Line table, all the entries have no version number…

But I found the problem!!

I found out that the error message showed up in the first record whose first Operation No. wasn’t 1

Basically, if the first operation was number 1, everything was ok, but if it was number 2, there was an error.

I tried to solve it by writing this:

IF RecRoutingLine.FINDFIRST THEN BEGIN
RecRoutingLine.“Operation No.” := FORMAT(‘1’);
i := 1;
RecRoutingLine.NEXT;
WHILE (RecRoutingLine.“Operation No.” < FORMAT(RecordsNumber)) DO BEGIN
RecRoutingLine.“Operation No.” := FORMAT(i + 1);
END;
END;

It compiles, but you know what happens? It crashes Navision!! It’s the first time that this happens to me. Do you know why?

[sarmig]

I solved it!!!

The cycle was not necessary…

But look at the result. I have a line with no number and I want it to disappear…

http://imgur.com/E8xQY

[MattTrax]

WHILE (RecRoutingLine.“Operation No.” < FORMAT(RecordsNumber)) DO BEGIN
RecRoutingLine.“Operation No.” := FORMAT(i + 1);
END;

For one, this is an infinite loop. Step through your code using the debugger, line by line. Compare the values of the variables as you step through it to what you think they should be.

[sarmig]

I solved that problem too. I deactivated the autosave and autoupdate properties and it worked.

Just one more thing: supposedly, in the Routing Line table (at least in my company’s Routing Line table), when I validate the “No.” field, the “Description” field is automatically filled. I’m trying to do that OnAfterImportRecord. I’m using:

RecRoutingLine.VALIDATE(“No.”);

The problem is, it only works for the first entry of each Routing Line, it doesn’t work on the others. Am I missing something?

[MattTrax]

Are you sure the Description isn’t getting overwritten by something else after the VALIDATE, but before the INSERT.

[sarmig]

Yes, I am…
RecRoutingLine.Type := RecRoutingLine.Type::“Machine Center”;
RecRoutingLine.VALIDATE(“No.”);
RecRoutingLine.INSERT;

[sarmig]

Curiously, when I test it on the form, if I just press enter in that field (the No. is already there, since it was imported), nothing happens.

The only way I can get the description to appear automatically is to press the up arrow on the right and then OK, and then enter again on the form.

It’s like it needs two validations or something…