Code in dataport

sarmig

Hello,

Today's task is: creating a Dataport!

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

http://msdn.microsoft.com/en-us/library/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.

MBerger

For easy conversion/code when importing using a dataport, you can use the OnAfterImport trigger. In this case though, i'd use tha dataport to read the contents of your file into a table, and then write some code that traverses the imported records and implements the functionality you want.

sarmig

MBerger wrote:

For easy conversion/code when importing using a dataport, you can use the OnAfterImport trigger. In this case though, i'd use tha dataport to read the contents of your file into a table, and then write some code that traverses the imported records and implements the functionality you want.

So, which trigger do you suggest I should code?

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?

Savatage

you're not telling us what it's "NOT" doing.
Are there no imported lines?
or are the lines that are imported, incorrect?

I usually use unlinked variable names just to be sure //ie instead of RecItem."No." I would use ImportedNo
where ImportedNo manually entered into one of the dataport fields. It just the way I like it.
For key fields I would go
VALIDATE(RecRoutingHeader."No.", ImportedNo);

are you using onafterimport? edit i see "no" to that answer.
Import all data into variavles & map them back to the desired Nav fields OnAfterGetRecord and see if that helps.

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?

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;

Savatage

The last line of code says "Insert" .

So if you found a line that already exists you need to use modify.

sarmig

Savatage wrote:

The last line of code says "Insert" .

So if you found a line that already exists you need to use modify.

Now it tells me "routing line does not exist", but I know it exists because the previous dataport either created it, or added values to it...

Savatage

So what you are saying is if you use "Insert" it gives you an error that it already exists.
if you use "Modify" it says it doesn't exist?

in RecRoutingLine.RESET;
RecRoutingLine.SETRANGE("Routing No.", "Routing No.");
RecordsNumber := RecRoutingLine.COUNT;

add message('%1',"Routing No."); to see if it has the correct value.

sometimes adding message in certain spots in the code can help you discover what's happening & when
then when working as expected you can comment out or delete the message code

sarmig

I made it.

Here is my final version of the code

RecRoutingHeader.RESET;
RecRoutingHeader.SETRANGE("No.","Routing No.");

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

RecRoutingLine.RESET;
RecRoutingLine.SETRANGE("Routing No.", RecRoutingHeader."No.");
RecWorkCenter.RESET;
RecWorkCenter.SETRANGE("No.", "Routing Line"."Work Center No.");
RecWorkCenterGroup.RESET;
RecWorkCenterGroup.SETRANGE(Code, "Routing Line"."Work Center Group Code");
RecCapacityUnitOfMeasure.RESET;
RecCapacityUnitOfMeasure.SETRANGE(Code, 'MIN');

//IF NOT RecRoutingLine.FINDFIRST THEN BEGIN
 RecRoutingLine.Description := '';
 RecRoutingLine."Routing No." := "Routing No.";
 RecRoutingLine."Operation No." := "Operation No.";
 RecRoutingLine."Work Center No." := "Work Center No.";
 RecRoutingLine."Work Center Group Code" := "Work Center Group Code";
 RecRoutingLine.Type := RecRoutingLine.Type::"Machine Center";
 RecRoutingLine."No." := "No.";
 RecRoutingLine."Run Time" := "Run Time";
 RecRoutingLine."Run Time Unit of Meas. Code" := FORMAT('MIN');
 RecRoutingLine.VALIDATE("Run Time Unit of Meas. Code");
 RecRoutingLine.VALIDATE("No.");

 IF RecRoutingLine.FIND THEN BEGIN
 RecRoutingLine."Run Time Unit of Meas. Code" := FORMAT('MIN');

 RecRoutingLine.MODIFY;
 END ELSE BEGIN
 RecRoutingLine."Run Time Unit of Meas. Code" := FORMAT('MIN');

 RecRoutingLine.INSERT;
END;


 IF RecRoutingLine.FINDFIRST THEN BEGIN
 RecRoutingLine.VALIDATE("No.");
 RecRoutingLine."Run Time Unit of Meas. Code" := FORMAT('MIN');
 RecRoutingLine.MODIFY;
 END;