Question

importing data - unable to import date column

  • 29 December 2023
  • 6 replies
  • 107 views
Hugo Nienhuis
Rank
Userlevel 3
Badge +11

I am trying to import data from an excel-file, using the universal gui. This works, but certain records contain an end_date. These records are rejected by the import-module.

I have been checking for constraints, data-type, triggers and handlers, but nothing seems to work. I have another date-column (start_date) and that works fine, so it looks some functionality is blocking the insert. So, my main question is: when importing data, what functionality is applied to the records:

  • triggers?
  • handlers?
  • constraints?
  • defaults?
  • lay / ctx ?
  • anything else?

I just want to know where to look for the problem.

6 replies

E
Rank
Userlevel 6
Badge +16

Is there a difference in control type/date format (data type) between the ones that do and don't work?

Hugo Nienhuis
Rank
Userlevel 3
Badge +11

No, they use the same domain. The only difference in the functionailty I can find is in the insert trigger, but the code itself does not seem  to be a problem (to me):

       set pc.end_date = dateadd(day, -1, i.start_date)

 

Mark Jongeling
Rank
Userlevel 7
Badge +23

Hi Hugo,

If the end_date is set to be equal to one day before the start_date, that could be preventing the insert in some way. Is there a check that enforces end_date to be bigger that start_date?

"Wake me up before you low-code"
Jeroen van den Belt
Rank
Userlevel 5
Badge +8

Hi @Hugo Nienhuis,

Can you please respond to Mark his latest question? Thanks in advance!

Hugo Nienhuis
Rank
Userlevel 3
Badge +11

We have a check that enforces this behaviour. To make a long story short, we (have) experience(d) a lot of difficulties when importing data into this tabel. There are both triggers and handlers on the table, as well as quite some lay/def code for enforcing several of these rules. This makes importing data quite vulnerable to mistakes.

 

I have been thinking to create a separate importing-table to easily import the data, and creating a task to test and correctly insert that data. That will give me more control of the import process. If anyone has another possible solution to this I would be thankful. If not, you can close this subject.

Jeroen van den Belt
Rank
Userlevel 5
Badge +8

Allright Hugo, we can keep this topic open for a while to see if someone comes up with an alternative solution. If this is not the case I will make sure we'll close this topic after a while.

Reply


Terms & ConditionsCookie settings