I just created a dataport for export using the Sales Invoice Header table. I could export most of the fields with no problem, but I noticed that it will not export any flowfields, like: Amount field, Amount Including Tax field, etc. So, what code should I put in order to resolve this issue? Another problem is when I looked at the exported data, I always found out the first record was emptied. How to avoid this situation? Sylvia Tsang
Hi Sylvia!
quote:
but I noticed that it will not export any flowfields, like: Amount field, Amount Including Tax field, etc.
There is a DataItem-Property “CalcFields”, where you can set the necessary FlowFields, the System will calculate them before the Export. Regards, Jörg Joerg A. Stryk Apollo-Optik, IT/ERP
Hello Sylvia 
For the first point of your question, you can use the calcfields property as Joerg said or you can also use the CALCFIELDS property on the record on the OnBeforeExportRecord event of the dataitem: OnBeforeExportRecord() “Sales Invoice Header”.CALCFIELDS(Amount,“Amount Including Tax”,… ); For the second problem, the reasom should be that you’ve an empty record on the table. You’ve two options: Option 1) Open directly the table from the object designer (using the run option) and delete the empty record. Option 2) On the OnBeforeExportRecord, put the convenient code for skipping that record (something like as follows: OnBeforeExportRecord() IF (“Sales Invoice Header”.“No.” = ‘’) THEN CurrDataport.SKIP; If you’re still having problems, feel free to ask Regards from L.A., – Alfonso Pertierra apertierra@teleline.es Spain Western Computer Los Angeles, California
Hi, Alfonso! Thank you for your help. The first problem has been resolved. I tried both options you suggested for my second problem, but they didn’t look like any one of it is working. Here is the situation: I have checked all the data in the table, and proved that there were no empty records. The interesting thing is that after the export, the process would create an empty record (displayed as the first record) in the text file, and the last record was never got exported. While the rest of the records are fine. So, do you think I have been doing something wrong? Or certain pieces of codes are missing? Here is another question: is it possible to export both Sales Header table and Sales Line table as one record using one dataport? If yes, how can I go by doing it? Sylvia Tsang
Your easiest method would be to use a Report to create a temporary/work table containing the combined Sales Header/Sales Line data (meaning a record formatted: SH Field 1…SH Field N, SL Field 1…SL Field M, for each Sales Line record), then call a DataPort to export from that table. Dave Studebaker das@libertyforever.com Liberty Grove Software A Navision Services Partner Edited by - daves on 2001 Sep 24 16:34:47
Or just put both items into the same dataport: try this: David Singleton Dataport Sales Header-lines.zip WW1 2.60 _________________________ David Singleton Navision Consultant since 1991 dmks22@home.com___________ Edited by - David Singleton on 2001 Sep 20 22:56:03
Hi, Sylvia! Ok, i’ve seen your dataports and the problem with the blank lines has a really simple reason: it’s what you’re telling the program to do: After reading a record from the table you’re getting the data from, the dataport event OnBeforeExportRecord() is executed, then the line is written to the file and then the OnAfterExportRecord() event is triggered. The OnBeforeExportRecord is used for initialization of the values you’re needing to calculate on the record you’re exporting to the file, and the OnAfterExportRecord is usually used for clearing values or futher calculations. On your code, you’re initializating the values on the OnAfterExportRecord() event, so when you read the first record, the record is not initializated, so you have an empty valued line. After writting that line, you make the values initialization, so the values are given to the variables. When you read the next record, instead of writting the values of that record you’re in fact writting the values of the previous one. That’s why you’re always having a first blanked line and you’re never getting the last record. Btw, your second dataport where you’re exporting the orders, on the way you’re doing it will be better based on Sales lines (table 37) filtered by “Document type” = Order than on Sales Header. OnBeforeExportRecord, you can get the sales header of that line (SalesHeader.GET(“Document type”,“Document No.”); ) and get the customer and the order data from it for using that info on your dataport, so you’ll have a list of the full order, and not just the total of the amounts… (you’ll be able of having a detailed order). Regards, Alfonso Pertierra – Alfonso Pertierra apertierra@teleline.es Spain Western Computer Los Angeles, California
David, I downloaded your sample dataport. Won’t that just output all the Sales Header records, then after that, all the Sales Line records? If there were 10 Header records and 100 Line records, you would output 110 records. I was assuming what was wanted was an output with all of both the related Header and Detail data in each output record. In my instance you would only output 100 records. Dave Studebaker das@libertyforever.com Liberty Grove Software A Navision Services Partner Edited by - daves on 2001 Sep 21 20:57:56
quote:
David first wrote Your easiest method would be to use a Report to create a temporary/work table containing the combined Sales Header/Sales Line data (meaning a record formatted: SH Field 1…SH Field N, SL Field 1…SL Field M, for each Sales Line record), then call a DataPort to export from that table. And then added I downloaded your sample dataport. Won’t that just output all the Sales Header records, then after that, all the Sales Line records? If there were 10 Header records and 100 Line records, you would output 110 records. I was assuming what was wanted was an output with all of both the related Header and Detail data in each output record. In my instance you would only output 100 records
David, if you base your dataport on Sales Lines instead of on Sales Headers and then just add a global variable type Sales Header (for example rSalesHeader)and add to your OnBeforeExportRecord trigger the following code: CLEAR (RSalesHeader); rSalesHeader.RESET; rSalesHeader.GET(“Document type”,“Document No.”); // sales lines always have a sales header. You can use then the sales header’s fields for your dataport directly by making new dataportfields in like rSalesHeader.Name Regards from L.A. – Alfonso Pertierra apertierra@teleline.es Spain Western Computer Los Angeles, California
Sure David, thats correct, I was just showing Sylvia where to start. It all depends on what is needed. This is really just a small snipet to get started. If you are going to combine the Header and Lines into one row of a dataport, the easiest, is just to base the Dataport on the Sales Line, and just add the necessary Header fields as required. Like the following: David Singleton Dataport Sales Header-lines combined.zip This will only export though… so it depends where the data is going to: If you are transfering from one Navision database to another, then use a seperate dataport element for each table. If you are exporting to another system it is generally better to use the second option. _________________________ David Singleton Navision Consultant since 1991 dmks22@home.com___________ Edited by - David Singleton on 2001 Sep 20 22:56:36
Hi, Alfonso! I am currently working on the Import Orders Dataport (it is used to import the orders that I just got from the Export Orders Dataport). I must have to create 2 tables: one is a Order Header, and the other one is Order Detail. The orders that I just exported from both Sales Header and Sales Line tables don’t really match with the fields of the two new tables. In other words, only a few of them match, and they are not in order. So, how could I trash all those fields that I don’t need, and rearrange them in the order that I want in order to insert those records into the new tables? I looked through all the functions from the reference guide, but they aren’t appropriate for my situation. Sincerely, Sylvia Tsang Management Software Solutions, Inc. stsang@mgmtsoftware.com
Hi Sylvia, the best way is to export the object as text, and modify it using your favorit text editor. _________________________ David Singleton Navision Consultant since 1991 dmks22@home.com___________
quote:
Sylvia wrote Hi, Alfonso! I am currently working on the Import Orders Dataport (it is used to import the orders that I just got from the Export Orders Dataport). I must have to create 2 tables: one is a Order Header, and the other one is Order Detail. The orders that I just exported from both Sales Header and Sales Line tables don’t really match with the fields of the two new tables. In other words, only a few of them match, and they are not in order. So, how could I trash all those fields that I don’t need, and rearrange them in the order that I want in order to insert those records into the new tables? I looked through all the functions from the reference guide, but they aren’t appropriate for my situation.
If you’ve more fields than needed (the method you used for your dataports was using variable positions), you can use a “dummie” text variable for getting the values on the positions you don’t want the fields (a text 260 variable will work fine…). The order of the fields is not important when reading the record on a dataport, just remember that all you need is to put the new tables fields in the right positions. So, as example, if you’ve defined your export dataport as follows: No. CODE 20 Description Text 30 Value Decimal Reference Code 20 and the table you’re getting the values is on the way: Reference No. Code 20 Description Text 30 , your import dataport should look something like this: Dummy Text 260 Description Text 30 Dummy Text 260 Reference No. Code 20 Where Dummy is a global variable type text 260. You can also disable on your export dataport the fields you don’t want to use by disabling them on the dataport fields list (the left click mark turned false).
quote:
David Singleton said: Hi Sylvia, the best way is to export the object as text, and modify it using your favorit text editor.
Well… in fact… i would like to know why?? you can modify a dataport perfectly from the object designer. If you’re saying about modifying the export file generated, i would like to know how long could it take you with your favourite text editor editing a 26 megs file… for example… (that’s a not too big company…)… Regards fron L.A. – Alfonso Pertierra apertierra@teleline.es Spain Western Computer Los Angeles, California
Hi Alfonso, it helps if you read the question.
quote:
Originally posted by yummi: In other words, only a few of them match, and they are not in order. So, how could I trash all those fields that I don’t need, and rearrange them in the order that I want in order to insert those records into the new tables?
Navision does not allow you to cut and paste in the Field Menu, so a text editor is the best solution. _________________________ David Singleton Navision Consultant since 1991 dmks22@home.com___________
Here is another issue: how do I know the position of the fields if the field(s) that I am going to skip have date, or time, or integer, or decimal as their data types. This is so confusing. Sincerely, Sylvia Tsang Management Software Solutions, Inc. stsang@mgmtsoftware.com
Field positions are only used if you use fixed length format, if you are using CSV format, just set all the positions and lengths to ‘0’. Remember also that CSV does not have to be a Comma, you can use any seperator, I often use something such as $ so that I avoid all the issues with commas and quote marks in text strings etc. _________________________ David Singleton Navision Consultant since 1991 dmks22@home.com___________
David, I understand what you are saying, but that is not what I am asking for. I just have no idea how to trash out some fields that I don’t want which they have date/decimal/integer(etc) as their datatypes. My situation is complicated: I am trying to import both Sales Header and Sales Lines (as a record) into two new tables (Order Header and Order Detail), which they would take some of the fields in the text files, and the order of these fields in the tables are all mixed up. I don’t know if I should use 2 tables as the dataitems, or just any one of these tables as a dataitem. Sincerely, Sylvia Tsang Management Software Solutions, Inc. stsang@mgmtsoftware.com
Hi Sylvia, why not start from the begining. Instead of addressing specific problems what is the overall aim. I see you want to import into new tables, where are you importing from, another Navision database, or another product. When you say the fields are mixed up, where are they mixed up, in the position that they are in the relative tables, i.e. export vs import, or are the types different etc. Also what is the basic objective? Are you trying to integrate a remote site, or is this some ind of batch automation. If you can give us a ver simple overview, I am sure we can get you started. _________________________ David Singleton Navision Consultant since 1991 dmks22@home.com___________