RapidStart not import data when first line is blank

Purvesh_Maisuria

Hi Experts,

I am using NAV2013R2's base functionality : RapidStart Services. I have table with primary key field Code, and its property NotBlank=No.

I have below data in table

Code Description

---

(Blank) (Blank)
111 111
222 222
333 333

If I export my data to Excel then it takes all data out with (Blank) value.

But if I import this excel file data in another database then it will not import anything.

If I remove first blank line from excel file then it import data perfectly fine.

My client want all data with (Blank) value, But RapidStart Service not importing anything when first line is blank.

Please give expert advise, if some one is gone through it.

Thanks,
Purvesh Maisuria.

vremeni4

Hi,

This is contradiction in itself. The field Code must not be blank. (property NotBlank=No.)
You cannot import a blank field in a Code field if the property is set to NotBlank.
To have a record with blank in Code field indicates that something is very wrong in your database.

You have two options to resolve this problem.
Delete the
(Blank) (Blank)
record from the database , as it should not be there as it is against NAV property setup.
Run Rapid start after that.

Second option is to change the property from NotBlank=No. to Yes, but I would not recommend this.

I hope this helps.
Thanks.

Purvesh_Maisuria

Hi vremeni4,

When field's datatype = Code and it's NotBlank=No, then you can insert (blank) value in table. As my field "Code" is primary key, so I can insert 1 row with (blank) value.

Please check it.

Your both solutions are not proper.

Your 1st solution : Delete the (blank) row, I don't want to delete it, if I delete it and migrate data through RapidStart Services then its working fine, that I mention in my question, I want to migrate as it is data with blank row.

Your 2nd Solution : I can not change NotBlank=No, to Yes. Because I need (Blank) value.

NotBlank property's behavior, NotBlank=No=You can insert (Blank) value
NotBlank=Yes=You cannot insert (Blank) value.

There is nothing very wrong in my database.

Please check it & try to migrate data through RapidStart Service with first (Blank) value, it is not copying a single row. I need the solution for this.

Thanks & Regards,
Purvesh Maisuria.

vremeni4

Hi,

Yes, you are right I think I misread your first email regrading the NotBlank properties.
I tested this and you are right as long as the first record is (Blank) (Blank) the system does not import anything.
If (Blank) (Blank) record is second or at any other place in the file then it works fine.
It is only when it is the first record.

So I looked at the code and I found that in the codeunit 8614 Config. XML Exchange
there is a function PackageDataExistsInXML which checks if the Excel document contains any data.
This functions checks if the first record in the Excel document has any field different then blank.
If any field is different then blank then it considers that the Excel file is not empty so the data is imported.
If all fields in the first record are blank, the system declares the excel file for empty and it finishes the import without looking at any data below that. This is the issue that you have.

In other words (Blank) (Blank) in the first line of the excel documents indicates that Excel document is empty.
In normal circumstance this is also correct.

If we look at this issue, what is the information value of the record (Blank) (Blank) ?
It has no value, it is a useless record which does not tell anything to the user.
To keep this record in the database is just waste of resources and time. (On top of it, it creates problems)
If you client can explain to you the value of having this record in the database then I will be surprised.

Bottom line, this behaviour is by design provided by Microsoft.
You can try to change the code in the function PackageDataExistsInXML but it will be very difficult to find another way to identify empty excel document.

You can also try to submit this as a bug to Microsoft, and maybe (which I doubt) they will change this function and provide a HotFix.

I hope this helps.
Thanks.

jglathe

Hi there,

simply checking two lines, maybe? Would be my approach. You're right about the empty record, they usually are cruft and can lead to problems.

with best regards

Jens

Purvesh_Maisuria

Hi jglathe & vremeni4,

Thanks for reply guys.

My client need blank value, that is fixed.

I put customized code in base function PackageDataExistsInXML in CU 8614 as perfect place search by vremeni4. Thanks for the same vremeni4.

Before Code of Function PackageDataExistsInXML
IF NOT ConfigPackageTable.GET(PackageCode,TableID) THEN
EXIT(FALSE);

ConfigPackageTable.CALCFIELDS("Table Name");
RecordNodes := TableNode.SelectNodes(GetElementName(ConfigPackageTable."Table Name"));

IF RecordNodes.Count = 0 THEN
EXIT(FALSE);

RecordNode := RecordNodes.Item(0);
IF RecordNode.HasChildNodes THEN BEGIN
RecRef.OPEN(ConfigPackageTable."Table ID");
ConfigPackageField.SETRANGE("Package Code",ConfigPackageTable."Package Code");
ConfigPackageField.SETRANGE("Table ID",ConfigPackageTable."Table ID");
IF ConfigPackageField.FINDSET THEN BEGIN
REPEAT
IF ConfigPackageField."Include Field" AND FieldNodeExists(RecordNode,GetElementName(ConfigPackageField."Field Name")) THEN
IF GetNodeValue(RecordNode,GetElementName(ConfigPackageField."Field Name")) <> '' THEN
EXIT(TRUE);
UNTIL ConfigPackageField.NEXT = 0;
END;
END;

EXIT(FALSE);

After Code of Function PackageDataExistsInXML

IF NOT ConfigPackageTable.GET(PackageCode,TableID) THEN
EXIT(FALSE);

ConfigPackageTable.CALCFIELDS("Table Name");
RecordNodes := TableNode.SelectNodes(GetElementName(ConfigPackageTable."Table Name"));

IF RecordNodes.Count = 0 THEN
EXIT(FALSE);

RecordNode := RecordNodes.Item(0);
IF RecordNode.HasChildNodes THEN BEGIN
RecRef.OPEN(ConfigPackageTable."Table ID");
ConfigPackageField.SETRANGE("Package Code",ConfigPackageTable."Package Code");
ConfigPackageField.SETRANGE("Table ID",ConfigPackageTable."Table ID");
// PPM >>>
RecCnt := ConfigPackageField.COUNT;
CurrCnt := 0;
// PPM <<<
IF ConfigPackageField.FINDSET THEN BEGIN
REPEAT
// PPM >>>
CurrCnt +=1;
// PPM <<<
IF ConfigPackageField."Include Field" AND FieldNodeExists(RecordNode,GetElementName(ConfigPackageField."Field Name")) THEN
IF GetNodeValue(RecordNode,GetElementName(ConfigPackageField."Field Name")) <> '' THEN
// PPM >>>
IF RecCnt = 1 THEN
EXIT(TRUE)
ELSE IF RecCnt > 1 THEN
IF CurrCnt <> 1 THEN
EXIT(TRUE);
// PPM <<<
UNTIL ConfigPackageField.NEXT = 0;
END;
END;

EXIT(FALSE);

---

I check by this code is, if second line is blank then it stop the execution. It is working fine. Still I want to ask to the experts, is it fine or have some issues with this customization. ?

Expect your valuable inputs. Thanks...

Thanks & Regards,
Purvesh Maisuria.

Purvesh_Maisuria

Hi,

I was wrong, it is not working. #-o

I have to find the proper place.

Purvesh_Maisuria

Hello Experts,

Sorry to active old post again, as above details.
Rapid Start is not importing the data when first line of table id blank,
I did below solution for this issue,

LOCAL PackageDataExistsInXML(PackageCode : Code[20];TableID : Integer;VAR TableNode : DotNet "System.Xml.XmlNode") : Boolean
IF NOT ConfigPackageTable.GET(PackageCode,TableID) THEN
EXIT(FALSE);

ConfigPackageTable.CALCFIELDS("Table Name");
RecordNodes := TableNode.SelectNodes(GetElementName(ConfigPackageTable."Table Name"));

IF RecordNodes.Count = 0 THEN
EXIT(FALSE);

RecordNode := RecordNodes.Item(0);
IF RecordNode.HasChildNodes THEN BEGIN
RecRef.OPEN(ConfigPackageTable."Table ID");
ConfigPackageField.SETRANGE("Package Code",ConfigPackageTable."Package Code");
ConfigPackageField.SETRANGE("Table ID",ConfigPackageTable."Table ID");
IF ConfigPackageField.FINDSET THEN BEGIN
REPEAT
IF ConfigPackageField."Include Field" AND FieldNodeExists(RecordNode,GetElementName(ConfigPackageField."Field Name")) THEN
// > Start
// IF GetNodeValue(RecordNode,GetElementName(ConfigPackageField."Field Name")) <> '' THEN
// < End EXIT(TRUE);
UNTIL ConfigPackageField.NEXT = 0;
END;
END;

EXIT(FALSE);

But not it is creating more other issues.

So need to do some other solution for the same issue.

Thanks,
Purvesh Maisuria.