09.06.2022
09:51
Hi.
When I import data from an external data source and the content of a field doesn't match the data type of the target field (e.g. target field is "Date", but the source delivers a "text") I get an error and the import is aborted.
Is ther a chance to "catch" that and set the value e.g. "empty"?
Kind regards,
Klaus
09.06.2022
23:20
Hi Klaus,
maybe I missed something but I didn't understand which action/method you are using for the data import.
Are you using some external tool which uses the REST API, a start workflow (sql) action, import using excel in a dictionary or item list? Or is it something else?
A screenshot could help too. :)
Best regards,
Daniel
10.06.2022
08:50
Hi Klaus,
maybe I missed something but I didn't understand which action/method you are using for the data import.
Are you using some external tool which uses the REST API, a start workflow (sql) action, import using excel in a dictionary or item list? Or is it something else?
A screenshot could help too. :)
Best regards,
Daniel
Hi Daniel,
sorry for the incomplete scenario description.
The actual case is a SQL Database connection where I have defined a data source as a SQL table with that connection.
So I'm talking about "pure SQL".
That Data source is used as data source for a choice field. Via the advanced configuration of the Autocomplete behaviour I set other fields with column values of that data source.
And here I have the issue that the "origin data" is not as properly defined as I wished. There are fields which are supposed to be a "date", but are unfortunately a "nvarchar" field. And sometimes the content does not meet the format of a date.
That is the situation where I wished I could check whether the data fits or in case not, set this value to "empty"
Kind regards
Klaus
10.06.2022
12:22
Hi Daniel,
sorry for the incomplete scenario description.
The actual case is a SQL Database connection where I have defined a data source as a SQL table with that connection.
So I'm talking about "pure SQL".
That Data source is used as data source for a choice field. Via the advanced configuration of the Autocomplete behaviour I set other fields with column values of that data source.
And here I have the issue that the "origin data" is not as properly defined as I wished. There are fields which are supposed to be a "date", but are unfortunately a "nvarchar" field. And sometimes the content does not meet the format of a date.
That is the situation where I wished I could check whether the data fits or in case not, set this value to "empty"
Kind regards
Klaus
Hi Klaus,
in this case I would convert the origin data to a value which is excepted using sql.
For example
select Inserted,
case
when Inserted = '' then null
when Inserted = null then null
else convert(datetime,Inserted)
end as InsertedDate
from (
select '2022-01-01' as Inserted
union select null as Inserted
union select '' as Inserted
union select '2022-12-01' as Inserted
) as temp
Best regards,
Daniel
