Repost - Writing data from 1 file in 2 tables

Phan

It is indeed perhaps better to start a new topic concerning this issue.

Phan wrote, original post:

Heya,

Is there also a way to have 1 dataport to grab the first 3 columns of a CSV file and put them in a table in Navision, and 3 other columns of the same CSV file in another table? Or do i have to write 2 dataports for this?

Any help would be much appreciated

Mark Brummel wrote:

@Phan

Maybe it is better to make a new topic.

You can make 3 variables and use them in the dataport fields. In the OnAfterImportRecord() trigger you can read the second table from a variable table and store the data in the table.

RobertMo wrote:

you can create noraml dataport for first table - define only first 3 dataport fields as fields from first table.
then define recored variable and 3 variables for 3 other fields (of proper type).
Define this other 3 vars as 4th 5th 6th dataport field (you have to type them).
then use proper trigger (onafterimport) to write simple code:

rTable2.Field1 := cdMyCodeVar;
rTable2.Field2 := tMyTextVar;
rTable2.Field3 := dcMyDecimalVar;
rTable2.INSERT;

be sure to empty variables before importing otherwise value from previous rec can be inserted.

BTW, as I can see now Mark was faster...
_________________
®obi
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯

ok, I'm quite new to Navision but I'm working on it.

I wrote the dataport that does indeed read the information from 1 CSV file into 2 tables, had to fiddle a bit with global vars, but makes perfect sense now. However it only works if the information did not already exist in the current tables. If it exists it will just throw an error in my face.

Every period our suppliers send us new product lists with updated prices. Most of the products already exist in the tables and need to be updated. How can I get my code to update the data?

Thanks [-o<

Marije_Brummel

You need to find out what is the primairy key of the table. If you know this you can get the table by using

Record.GET("KeyField")

Then you can update the fields and instead of an Insert you can use a Modify.

You can use

IF Record.GET("KeyField") THEN...

To check if the records exists.

Phan

:-k

I'm testing the .GET function with something simple. A one line CSV file that is a new product will be inserted by the dataport. So the first time i want it to respond 'not found', and the second time 'found'. In the table named 'GABI Trade Item' is the unique key field 'item code'. It never says it finds it.

IF "GABI Trade Item".GET("Item Code") THEN
MESSAGE('Found')
ELSE
MESSAGE('Not Found');

The C/SIDE manual indicates i have to be more specific(like .GET('100')), but I've tried some things but they didnt work. Also how will the script know to look in 'Item Code', i assume it will look automaticly in the key field.

IF Customer.GET('1120') THEN
MESSAGE(Text000)
ELSE
MESSAGE(Text001);

I'm guessing I dont understand how .GET works. Obviously misunderstanding it. ](*,)

Thanks in advance

Marije_Brummel

OK.

The get statement only works on the primairy key field. If you know all key fields you can use this. Like in a Cronus W1 database

Cust.GET('10000');

Will get the first customer.

If you want to get a record via an other field you should set a filter.

Do you have some kind of c/side programming manual?

You can find a record useing someting like

Cust.Setfilter("Name", 'Cronus');
Cust.Find('-');

DenSter

It looks to me that you are on the right track, but need just a little more information.

The GET method is used to uniquely identify a record from a table. In other words, you have to specify all primary key values of the table that your record variable refers to.

For instance, the Item table's primary key is the "No." field, so if you want to GET an Item record, you do:

MyItem.GET(SomeItemCodeValue);

For another instance, the Sales Header table has two fields as the primary key, the "Document Type" and the "Document No." fields, so if you want to GET a Sales Header record, you have to specify two values, like so:

MySalesHeader.GET(MySalesHeader::Order,SomeSalesOrderNumber);

You set filters on fields if you don't have enough information to uniquely identify a record, but you have some information to limit the number of possible records to check. So, if you want to loop through all Orders, you do this:

MySalesHeader.SETRANGE("Document Type"::Order);
IF MySalesHeader.FIND('-') THEN REPEAT
  // do your thing here
UNTIL MySalesHeader.NEXT = 0;

Your "GABI Trade Item" table has a primary key of the item code only, so that is the reason of your confusion. What I suspect is that your variable is a temporary variable. If that is the case, you will not find any records unless you insert them before in your code. A temporary variable is nothing more than a piece of computer memory that has the same structure as the table that you base the variable on. Using the GET method doesn't work on a temporary record variable, because it doesn't look into the table.

Let us know if that is the case.

Phan

Perhaps I'm making this more difficult for myself then strickly necessary. And perhaps I'm not as good as I'd like to, explaining myself. I dont think I need filters at all. I'm kindoff only stuck on the fact that writing to 2 tables does not allow me to overwrite records which already contain information. This is why i figured I needed some code to check if a record exists etc. And I want it to update it in that case.

The thing that I wrote about is that I would like to save information from 1 CSV file into 2 tables. Thats works, if I use the insert command that works like a charm.

But somehow if the information already exists (in the table.INSERT part)it does not insert the info OR update it. Just gives the error 'this record already exists'.

C/AL Globals

Name	     DataType	Subtype	Length
DummyText	     Text		30
Var1	     Code		20
Var2	     Decimal		
Var3	     Decimal		
rTable2	     Record	     GABI Trade Item Price History

Var1 - 3 are saved into the price history table

"GABI Trade Item".Vendor := 'Vendorname';
rTable2.Vendor := 'Vendorname';

"GABI Trade Item"."Item Code" := Var1;
rTable2."Item Code" := Var1;
rTable2."Gross Price" := Var2;
rTable2."Quantity Per Price" := Var3;
rTable2.INSERT;

Basicly I only want to grab 3 columns of this file and put them in the price history, and 5 other columns in the item table(the item code must be in both tables ofcource). This is easily done with 2 simple dataports, they seem to check and overwrite records appropriatly. However specially since I feel there will be more tables to fill with 1 file in the future I was thinking to use C/AL code to help out filling multiple tables.

Sorry for the confusement. I'm just started with some simple navision programming and importing.

Mark, in reply to your question, i dont have a C/SIDE manual, other then the Digital In-NAVISION C/SIDE Reference guide which explains some functions and syntaxis.

Thanks for the replies.

Marije_Brummel

If the primary key of the history table is the Item No. you code should look something like:

IF NOT rTable2.GET(Var1) THEN BEGIN
  rTable2.INIT;
  rTable2."Item Code" := Var1; 
  rTable2.INSERT;
END;
rTable2."Gross Price" := Var2; 
rTable2."Quantity Per Price" := Var3; 
rTable2.MODIFY;

Hope this helps.

DenSter

or:

IF NOT Item.INSERT THEN
  Item.MODIFY;

Phan

Yes this helps, thanks alot. It appears I mistook the primairy key, it is a combination rather then a single value, i guess thats why I kept getting 'this record already exists errors' :roll: . GET("Lev nr.", "Item Code") works properly.