Saving Empty Date Fields to Retool Database (Revisit 2)

This has been raised before...

Saving Empty Date Fields to Retool Database

For the reasons haj mentioned and others, I think this one deserves being revisited for the following reasons

  1. It prevents using {{myForm.data}} being passed as an object to an update query which is the most "low code" way of doing it

  2. If you use the built-in feature to "Generate Form", Retool selects this date time field as the default one to use for timestamp fields. You would therefore expect the date time field to return a compatible value which "" is not.

  3. The behaviour of this field is to automatically change a set value of null to "". As a result, without making any changes to the form data, the output from {{myForm.data}} has already changed. In addition, there is already a difference between {{myForm.data}} and {{myForm.initialData}}

  4. Not sure if anyone else does this but I sometimes set a field's background colour value to something like
    {{ myForm.initialData?.[self.formDataKey] === myForm.data?.[self.formDataKey] ? "" : "highlight" }}
    This code can be added to any field and will highlight the background of any field that the user has changed. The date and time field is as far as I've encountered is the only field where this code does not work, further evidence that it's behaviour is inconsistent with others.

Thanks

2 Likes

Hello @Ross_Coombes,

Thank you for this feedback. I completely agree, it should be as easy has passing in {{myForm.data}} and in a perfect world, users should not need to sanitize their data to replace "" with null values.

I believe the root cause of this problem is stemming from some very old and deeply embedded code from the early days of Retool, that to replace would involve a massive overhaul of the Form component.

For update queries to Retool DB, Joe's solution in the linked thread, of adding in a JS Query code block to delete or re-assign "" to be null and then pass the data from the JS Query block into the update query should work for most cases.

I understand it is tedious to do this for data objects that have multiple date time fields but it should produce consistent results.

I can make a feature request for having the logic to check for the form's date time input, to check for "" and replace it with a user inputted value to be set up as an embedded feature than users can toggle on and off for the component.

Although this may not solve the issue of points 3 and 4, as the initial may still be different from the data after something like a JS Query block replacement runs :thinking:

Could you share a screenshot of the field being set to null and then changing?

From my testing I am not able to change a date time input component's value to be null, but if the component is being set to null and then changing I can make a feature request for that as well. If the value cannot be assigned to null at all that could be another option for a feature request to allow it to be set as a non-string value.

Thanks for your response.

Excuse the manipulated screenshot but I think this basically shows what's happening. On the left I've created a variable set to null. The field is bound to that variable. You can see from the inspector that it sees "null" as an object and says it will convert it to "".

So no, I don't believe you can set it to null directly but really this issue is that it can't inherit null from the form's datasource either thus introducing an unnecessary process for cleaning the data before it's saved.

As you say, probably deep rooted to ensure you can't have the value of any field being an object but given the date field is actually representing an object not a string in dealing with the date datatype, the onus would be on this component to either allow null as an input or at least return a valid output for "".

1 Like

Thank you for sharing the screen shot and explaining!

You are 100% correct the tooltip is indicating that the null value is being changed by the component to an empty string of "" and this is not easy to work with and is not what you want to be sending to your database.

I have filed a feature request to our engineering team and this change to allow the date time field to be set to and remain null has been added to the roadmap for the Form component.

I will keep you updated on any further news I hear from the team :saluting_face:

For now the best work around might be to have a little bit of validation logic where if the value is an empty string, to not send the form data to the DB until you add in a moment object you create then or a null value you append in to the form data's object in the place of the empty string to then send in a query.