Method Community

I have an action that creates new rows in a table based on existing rows (basic copy). One field in the table is a date field. On the UI, this date field is set using the Calendar widget. Empty is a valid value and when the record is saved from my custom UI screen, a NULL (or empty) value is written and it works just fine.

In my action when copying this NULL value from one row to the new one, the value actually saved is 'Dec-31-1899'.

Why is this? How can I write a NULL value to a date field in an action's Insert statement? Obviously, the Calendar widget can do this. How can I do this in an action?

Does this make sense?

Thx!

I'm testing this on my local account and will post up a reply when I can confirm this behaviour.

~C

Blake C:

Why is this?

Which table are you copying into?  Depending on the table the date might be a required field so it may not accept a NULL value.  

~C

Blake C:

Does this make sense?

I was just able to recreate this problem and it only happens when you try to insert no date into a table which requires a date.  You cannot write a NULL value into a table when that field is required.

~C

This is custom table of mine. The date field is not currently required. The screen where we maintain this data has a Date dropdown that allows an empty value to be saved. I've exported this data and it's currently saved as an empty value.

However, in my custom action where I retrieve the empty date value from one record, then insert that value into a new record, behind the scenes that empty date is intercepted and replaced by the 'Dec-31-1899' date (a Min Date, it seems).

It seems like the Insert Record action assumes the Date field is required and is replacing the empty with the Min Date rather than looking at the column's definition that should allow an empty value.

Make sense?

Blake C:

Make sense?

It makes sense but I'll have to do some more testing to recreate your scenario described.  In my testing I was using the Insert Records action and I created a blank table with 2 fields, name and date.  I added several records with and without dates then created a button to copy the selected row and insert it back into the table.  In my testing this worked perfectly and blank dates were copied no problem.  

I then recreated this screen with a table where the date was required.  I had to modify the Insert action but I basically copied the row and zeroed out the date field before calling Insert Record - every time I tried this I got the min date (Dec-31-1899).  If the date is not required as you say then I would explore the action sequence where you insert - something here must be triggering the min date value.

Try a Show Message action with the Action Result of the date field before you insert and after the copy action.

~C

One thing that might make a difference is that I'm adjusting the date (Date Add of 1 year) when copying one record and inserting a new one.

I'm creating an Action Result first, then if the date field on the row I'm copying has a value (e.g. if it's not empty), I do a Date Add and use that value to insert the new row. If the date field I'm copying from does not have a value, then the empty Action Result I assigned initially is written (it's just a blank value).

Perhaps that empty Action Result I created is un-typed (or at least it's type is NOT a date time) and so the insert is just putting the Min Date in there by default?

That might be what is going on.

I would add a couple of show message popups with your action results before and after your Date Add function.  I'm not sure the Insert action would do something like add a min date - the insert action just inserts a new row with the information you give it.  As a test, try changing to a different, blank AR to eliminate any possibility of the coppied AR causing a problem when it has no value.  Make sure to keep adding a show message popup before and after any AR changes to see where it might be breaking down.

~C

Thx.

Using show message isn't allowed since this is all being done in a loop. What I have done is write these values into a variable and then dump it to a field on the screen (like trace or debug info).

This trace info shows an empty value prior to insert. But the insert statement goes the Min Date route, for some reason.

I may have to break down and do a subsequent update on the record after the insert, looking for the Min Date and updating it to an empty value and see if that works.

The other thing I'm thinking about that might make this problem moot (more of a process improvement) is disallow an empty date for these records altogether. I'm starting to think this may serve my salesmen and the order process better in the long run anyway. So that option is on the table at this point, as well.

Thx

This might be a little redundant but for debugging it could help.  This is a trick I use to capture all the processing steps on an AR to see if something went wrong and where.

Call one of your ARs debug and use the character function to join the results together as they process.  For example, as soon as you pull the date to do your processing, call a character function and set the value of this date to the debug AR before and after processing.  Join all your results with a comma and make sure you join the Action Result to itself so you'll continue to build the list.  In my example, all my character functions end with Join to: Action Result - debug, Action Result name: debug.

Let me know if this makes sense.

~C

Makes total sense...that's pretty much what I described in my previous post. As soon as I started doing more things in loops within Actions, I started doing something very similar to what you describe. It's been helpful.

Thx

After typing the post I searched for debugging on the forums and found this great thread (which you started!) with other tips.  My favourite trick was from LaCrews - use a label object on screen and use the enter value into field on screen as you step through the loop.  It wouldn't work in all instances but it's another nice way to see what's happening as your loop processes.

Try some of the debugging tips and you should be able to figure out where that min date is being added.