Import into picklist

[AndKanPA]

Hello community,

I had problems to import string data into a picklist/optionlist.

In first run of dataflow it works fine on different entities.
Picklist is always the same.

In next run with some entities it works, in other there are strange error messages:

Either:
Error code: 0x8004431a, Message: A validation error occurred. The value 999999 of 'cr847_kcolor' on record of type 'cr847_dcars' is outside the valid range. Accepted Values: 742200000,742200001,742200002,742200003, ...

That's really funny, because imported colors are strings like red, blue and so on and sure not '999999'

Or: 
"Data type mismatch with [ cr847_dcars.cr847_ktyp ] - Integer <-> String"

And again: Imported 'types' are strings like coupe, van and so on.

In first run there was no problem to convert a string into related option field.

I.e. 'red' imported as '742200000'

No, it didn't work to recreate this entity attribute again. So it is empty, at works like a first import. 😉

Any idea to solve?

Why it imports in two entoties and in two others, there are errors.

Thanks for help,

AndKan

[EricRegnier]

Hi @AndKanPA,

Just trying to get a bit more context on your Dataflow. I assume your loading your data to an existing entity since option set  destination type is not supported yet for new entities? Option set fields are represented by a key value pair where the key is an integer value and what's saved in the records. That said, the error message makes sense, but DataFlows should be smart enough to automatically map the data source label/text to the option set item display name. Would it be possible to share screenshots of the Edit Query and Mapping steps of the Dataflow?

Also, try to edit the flow, save and rerun to see if it works again or even create a new one?

Thanks

[AndKanPA · Accepted solution]

Hello Eric,

your are right: error message makes sense, but DataFlows should be smart enough
It is smart ... and mysterious

1st error: "Error code: 0x8004431a, ... The value 999999... is outside the valid range. Accepted Values:..."
There was the value in picklist missing ...
Solution: Added the missing value into picklist and import works also.

2nd problem:
I recreated both entities new.
DataFlow import into pickuplist works fine.

Then I added an m:1 relationship and import fails again.
???

Related entity had no primary key; normally this is created with first import from SQL primary key.
Missing destination field cr847_entity02.cr847_id_entity01 in scheme
And pickup list has error Integer <-> String again.
So I removed relationship and: Import into picklist works again.
???
Solution:
- First I added a primary key manual.
- After that, I added m:1 relationship.
This workflow removes the error "Missing destination field ...".
And now the dataflow import works, including picklists.

And just another one:
I thought, my entity is crashed at all:
"General error","An item with the same key has already been added."

Of course there is a record with this key already, because Dataflow runs again. I'll try to update data.

This is a wrong error message ... It means: There is a duplicate entry in pickup list.
???
Solution:
Yeah ... you can enter any values like 'red', 'Red', 'reD' in pickuplist. No problem.
But you can only case-insensitiv import into picklist.
Otherwise you get the general error above.

Best regards,

AndKan

[ganesh421]

Reason: Bad Request, Header x-ms-client-request-id d38da504-1eac-4dd7-aa94-dbe4a16d936a, Error code: 0x0, Message: An error occurred while validating input parameters: Microsoft.OData.ODataException: Cannot convert the literal 'Dog'' to the expected type 'Edm.Int32'. ---> System.FormatException: Input string was not in a correct format. at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) at System.String.System.IConvertible.ToInt32(IFormatProvider provider) at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) at Microsoft.OData.ODataPayloadValueConverter.ConvertStringValue(String stringValue, Type targetType) at Microsoft.OData.ODataPayloadValueConverter.ConvertFromPayloadValue(Object value, IEdmTypeReference edmTypeReference) --- End of inner exception stack trace --- at Microsoft.OData.ODataPayloadValueConverter.ConvertFromPayloadValue(Object value, IEdmTypeReference edmTypeReference) at Microsoft.Crm.Extensibility.ODataV4.CrmPrimitivePayloadValueConverter.ConvertFromPayloadValue(Object value, IEdmTypeReference edmTypeReference) at Microsoft.OData.JsonLight.ODataJsonLightReaderUtils.ConvertValue(Object value, IEdmPrimitiveTypeReference primitiveTypeReference, ODataMessageReaderSettings messageReaderSettings, Boolean validateNullValue, String propertyName, ODataPayloadValueConverter converter) at Microsoft.OData.JsonLight.ODataJsonLightPropertyAndValueDeserializer.ReadPrimitiveValue(Boolean insideJsonObjectValue, IEdmPrimitiveTypeReference expectedValueTypeReference, Boolean validateNullValue, String propertyName) at Microsoft.OData.JsonLight.ODataJsonLightPropertyAndValueDeserializer.ReadNonEntityValueImplementation(String payloadTypeName, IEdmTypeReference expectedTypeReference, PropertyAndAnnotationCollector propertyAndAnnotationCollector, CollectionWithoutExpectedTypeValidator collectionValidator, Boolean validateNullValue, Boolean isTopLevelPropertyValue, Boolean insideResourceValue, String propertyName, Nullable`1 isDynamicProperty) at Microsoft.OData.JsonLight.ODataJsonLightResourceDeserializer.ReadEntryDataProperty(IODataJsonLightReaderResourceState resourceState, IEdmProperty edmProperty, String propertyTypeName) at Microsoft.OData.JsonLight.ODataJsonLightResourceDeserializer.ReadPropertyWithValue(IODataJsonLightReaderResourceState resourceState, String propertyName, Boolean isDeltaResourceSet) at Microsoft.OData.JsonLight.ODataJsonLightResourceDeserializer.<>c__DisplayClass9_0.<ReadResourceContent>b__0(PropertyParsingResult propertyParsingResult, String propertyName) at Microsoft.OData.JsonLight.ODataJsonLightDeserializer.ProcessProperty(PropertyAndAnnotationCollector propertyAndAnnotationCollector, Func`2 readPropertyAnnotationValue, Action`2 handleProperty) at Microsoft.OData.JsonLight.ODataJsonLightResourceDeserializer.ReadResourceContent(IODataJsonLightReaderResourceState resourceState) at Microsoft.OData.JsonLight.ODataJsonLightReader.StartReadingResource() at Microsoft.OData.JsonLight.ODataJsonLightReader.ReadResourceSetItemStart(PropertyAndAnnotationCollector propertyAndAnnotationCollector, SelectedPropertiesNode selectedProperties) at Microsoft.OData.JsonLight.ODataJsonLightReader.ReadAtStartImplementationSynchronously(PropertyAndAnnotationCollector propertyAndAnnotationCollector) at Microsoft.OData.ODataReaderCore.ReadImplementation() at Microsoft.OData.ODataReaderCore.InterceptException[T](Func`1 action) at System.Web.OData.Formatter.Deserialization.ODataReaderExtensions.ReadResourceOrResourceSet(ODataReader reader) at System.Web.OData.Formatter.Deserialization.ODataResourceDeserializer.Read(ODataMessageReader messageReader, Type type, ODataDeserializerContext readContext) at System.Web.OData.Formatter.ODataMediaTypeFormatter.ReadFromStream(Type type, Stream readStream, HttpContent content, IFormatterLogger formatterLogger)

I am getting this kind of error message When I am trying import data from excel. I tried to enter values for Species Column as 'Dog' in excel and upload, but still getting validation issue. 

Reason: Bad Request, Header x-ms-client-request-id 81426f9e-c353-424f-b6b0-fbe7520c79dd, Error code: 0x8004431a, Message: A validation error occurred. The value 0 of 'cr8a6_species' on record of type 'cr8a6_pet' is outside the valid range. Accepted Values: 389490000,389490001

How can I enter String Values.

Thanks in Advance

[EricRegnier · Accepted solution]

Hi @ganesh421,

Quick answer is that you can't. Choice (aka optionsets) are stored as integers in the database, so you'll have to find the integer representation of "Dog" and so on to import. You can simply do a find/replace in Excel.
To find the integer values, go to make.powerapps.com --> Data (sometimes called Dataverse) --> Tables --> select the table --> Columns --> select the column --> click on "Edit Choice" button --> click on the ellipses, view more.

Hope this helps!

[ganesh421]

Thanks Eric

Later I tried using integers and now its working. Now I saw the reply. Thanks for the Answer