Bug: 5.1d BatchUploader does not validate and replace csvString->foreignKey-ID-value

[Blama]

Hi Isomorphic,

please see the testcase from this thread, but with the ds.xml from post #3.
If you upload the file, you'll get this:


Please note that the 1st row should display a validation error as there is not Manager "NOTKNOWN".

This is not the case. See the fetchMissingValueReply-request, which goes for a wrong field:


I then select a Manager from the list for one of the rows:



I then save the data. See the client request from the Developer Console RPC Tab (attached).


In total, this seems to be heavily related to the wrong fetchMissingValues in the beginning.

This is a very important one for me (from my reports everything BatchUploader-related has currently the highest priority for me).

Best regards
Blama

[Blama]

Hi Isomorphic,

it seems you are working on this as I don't see the fetchMissingValueReply-request anymore with SNAPSHOT_v10.1d_2015-09-27, but could you confirm that you see this one? Do you have an ETA for it?

Best regards
Blama

[Isomorphic]

Confirmed, we are looking into this one and we'll keep this thread updated.

[Blama]

Hi Isomorphic,

do you have an ETA for it? This is a really important and urgent one for me.

Best regards
Blama

[Isomorphic]

Sorry for the delay on this.
We're a little confused by the test case here. The ds.xml file has the ReportsTo field specified like this:

        <!-- Removed type="integer" in ReportsTo, as this triggers another error -->
        <field name="ReportsTo" uploadFieldName="ReportsTo" title="Manager" required="true" foreignKey="employees.EmployeeId" />

The foreignKey for this field is employees.EmployeeId - a numeric field from another dataSource.
So the type="integer" would seem to be appropriate.

Your test data looks like this:

EmployeeId,Name,ReportsTo,Job,Gender
7000,Michael Mann,NOTKNOWN,Developer,male
8000,Ray Sun,Charles Madigen,IT-Infrastructure,

Now your "ReportsTo" field values are both strings - Name rather than ID field values from that employees DS.

This seems to just be bad data, and the failure reflects this.
The expected failure would be an immediate validation error for non integer values.
But you're not getting that - instead you're getting server failures attempting to convert the field value from string to integer type data.

The -10 error code by the way is a transaction failure code. From the docs:

STATUS_TRANSACTION_FAILED

Indicates that the request was either never attempted or was rolled back, because automatic or user transactions are in force and another request in the same transaction failed. Note that the request(s) that actually failed will have a code specific to the failure; it is only the requests that would otherwise have succeeded that are marked with this failure code.

It seems like we're missing something on your intent or your configuration here. Could you show us the complete set of relevant files for *this particular* issue in case we're missing something?

Thanks
Isomorphic Software

[Blama]

Hi Isomorphic,

sorry, I made a small mistake that would have made the problem clearer, but the problem itself is exactly the same with the revised file. See the new employeesUpload.ds.xml:

<DataSource ID="employeesUpload" serverType="sql" tableName="employeeTable" recordName="employee">
    <fields>
        <field name="EmployeeId" uploadFieldName="EmployeeId" title="Employee ID" type="integer" primaryKey="true" required="true" />

        <field name="Name" uploadFieldName="Name" title="Name" type="text" length="128" />

[B]      <!-- Removed type="integer" in ReportsTo, as this triggers another error -->
        <field name="ReportsTo" uploadFieldName="ReportsTo" displayField="employeeName" title="Manager" required="true" foreignKey="employees.EmployeeId" />
        <field name="employeeName" [/B][B]includeFrom[/B][B]="employees.Name" />[/B]

        <field name="Job" uploadFieldName="Job" title="Title" type="text" length="128">
            <valueMap>
                <value>Developer</value>
                <value>IT-Infrastructure</value>
            </valueMap>
        </field>

        <field name="Gender" uploadFieldName="Gender" title="Gender" type="text" length="7">
            <valueMap>
                <value>male</value>
                <value>female</value>
            </valueMap>
        </field>
    </fields>
</DataSource>

What I was trying to say all the time is that the very feature (#2) of the Feature Sponsorship you implemented in 5.1d in March (should be in 5.1d 2015-03-17) is not working. See this thread and the mails with you from end of February.

From the "completed" mail:

The sponsored features are now both complete, and will be ready for your review in *tomorrow's* nightly build of 5.1d.

1. Feature Sponsorship: Allow iterative fixing of errors in the batchUploader flow, so the user can fix some errors, then commit, then fix more errors, commit again, etc.
2. Feature Sponsorship: Automatic resolution of key values from display values during import, for fields that declare both a foreignKey and a displayField in the .ds.xml file

Best regards
Blama

[Blama]

Here the new .ds.xml as attachment.
employeesUpload.ds.xml

[Isomorphic]

We're taking another look.

[Isomorphic]

Just a quick note to let you know this is still under active investigation (not forgotten!), and we will be following up when we have something for you.

[Blama]

Hi Isomorphic,

this one becomes very pressing for me now as business development was already selling the feature and a customer demands it. It would be great if this could be sorted out quickly.
Do you need any more information from me?

Thank you & Best regards
Blama

[Isomorphic]

Ok - sorry for the delay

We actually ship a sample in the 5.1p package which does essentially what you're doing here - a batch upload with a field with foreignKey and displayField - where the displayField is picked up from another DS via includeFrom (see the "units" and "unitName" field of the batch upload sample).
So the basic feature is known to work - and it took us a while to figure out what was special about your case.

Firstly - the "UNKNOWN" field value.
This leads to a failure - an attempt to map a string to an integer (or, if you have type="integer" on the field, a validation failure indicating the value is a string, not an int).
This is actually expected - the server has logic in place to look up the related record - when it fails to find one, it attempts to save the value exactly as entered. This leads to the type mismatch problem.

The foreignKey won't raise a warning in this case automatically.
The fact that there is a foreignKey declared on the field does not imply that the foreign record is required - foreignKey is a property you can use to enable features, like includeFrom; it is not there to enforce referential integrity.

The behavior you want is a validation failure indicating that this is an Unknown value. If you look at the sample from 5.1p, you will see that it specifies a "hasRelatedRecords" validator - that is how the sample is able to issue a sensible "bad value" error for the unknown value ("EACH" instead of "Ea")

Secondly - if you resolve the bad value in the CSV file, you can end up with a SQL error of the form Not unique table/alias: 'employeeTable'
What's happening here is that the employee and employeesUpload dataSources have the same referenced SQL table and you need some additional settings to make this work.

When you join a table to itself via foreignKey, you have to tell the SQL subsystem how to resolve this with the "relatedTableAlias" property. The attached "employeesUpload" dataSource adds the related record validation and the necessary relatedTableAlias attribute to prevent the bad SQL on update.

With this DS, you get an error on the "NOTKNOWN" manager. You can correct this or ignore it; assuming you ignore it and click Commit, it will warn you that there are errors and it isn't going to write the bad records, then you click OK and it goes ahead and writes out "Ray Sun" but not "Michael Mann", and pops up a confirmation box.

Hopefully this gets you the behavior you need!

Regards
Isomorphic Software

[Blama]

Hi Isomorphic,

great, thank you. I'll try it tomorrow, it's now evening here.
Could you also explain how the other Feature Sponsorship ("iterative fixing of errors in the batchUploader flow") is used? (Not time critical for me, currently.)

Best regards
Blama

[Isomorphic]

Again, the 5.1 Showcase batch upload example demonstrates this, but basically you use the RETAIN PartialCommitOption

[Blama]

Hi Isomorphic,

thank you very much, the main feature csvString->foreignKey-ID-value seems to work and I can continue!

Two other bugs (using SNAPSHOT_v10.1d_2015-10-14/PowerEdition Deployment and your .ds.xml):

1. Change the csv to:

EmployeeId,Name,ReportsTo,Job,Gender
7022,Michael Mann,NOTKNOWN,[B]Develope2r[/B],male
8022,Ray Sun,Charles Madigen,IT-Infrastructure,

Even though the Job-field has a valueMap, the uploaded value is not validated against it.

2. Upload the file and click Commit. Confirm the popup (only displayed if your data had errors).
Now the data is written to the DB and the application always displays this error message (not depending on the data having errors):


Best regards
Blama

[Blama]

Update: The 2nd error with the warning does not happen always - I did not find the reason for it, yet.