Update Datatype & ignore empty fields not working for Booleans and ENUM fields

Klaas Van Hoeck - Know It Now

So I try to do a simple edit action to one of my data fields and ignore the fields that are not send in the call. I do this by using the Set_ifnotempty/null filter. This works for strings and dates, but not for booleans or ENUMS. Although the fact I use set_ifnotnull, my boolean fields always get reset to false if I don't send them and my ENUM fields are getting reset to the first option in the list.

I wanted to check if I am missing something super easy here. Just a simple update action can't be that hard 😁

MattLaro

Hmmm this is strange. If your input is nullable without any default value and you use the set_ifnotnull filter, it normally should leave the value intact.

How do you proceed exactly ?

As for my working solution, what I do is that I first get the row in the database table that is stored in variable ROW.

I update variable ROW with variable ROW and thend add set_ifnotnull filters for all fields of my row that will be modified.

ex: set_ifnotnull field: name, value: input:name.

I then edit the data in the database table with ROW by clicking on the magic wand that fills all table columns automatically with object ROW.

Klaas Van Hoeck - Know It Now

Hi @MattLaro -
Thanks for your reaction.

In the video you can see that I use this method of set_ifnotempty/null. https://www.loom.com/share/a63368d0ca0348029fa9778292d1a960?sid=a6129bd7-fd90-49ff-b7b1-e1dfe29a9659

So it does work for Strings and Dates, but ENUM and Boolean seems to be difficult.
Can you spot the error?
Highly appreciate you help!

MattLaro

Are you using Database link as your function input values ?

I always had the most difficult time dealing with these since I'm NEVER sure about the behaviour of the null value in these kind of fields or not and I have no idea what's the impact of putting a nullable field in this…

I'd say logically, you have to make sure that your database field has no default value and is nullable in the field settings :

HOWEVER, a very important comment about changing a field to nullable in the database (only if you never used them). A nullable field in the database complicates all queries made on that field since there's no "is_empty" filter in database queries, forcing you to always query against the field with empty and null if you want rows where that field has no value. If you forget null or empty, you will miss some rows and it is unintuitive. I would wildly advise against nullable values in DB unless you know what you'll get into, especially when it comes to using a null value for function logic only.

Another important note about changing type : The red square makes a very important statement here. Try it in an independent field of the same type first to make sure that converting Nullable from No to Yes won't erase all your data.

If you proceed with these settings, you can then try again your query.

What if I don't want to make any changes to my database ?!

Hide the field that is problematic with null values in your function settings.

Create a new input field with a slightly different name in your function. Use that field instead where you explicitely specify that it is not required and nullable, like so :

Use this new input field against your set_ifnotnull filter.

That way, you absolutely have 100% control on the field's behaviour and expectations while keeping your database field settings intact. The downside, as stated, is that you may have to maintain that field in multiple places instead of letting Xano adapt your input according to your DB esttings.

Klaas Van Hoeck - Know It Now

hi @MattLaro - Got a reaction from Chris from Xano.
In the end, the easiest way to fix it is making use of Strings.
Strings are easily recognised as empty and do work to set Boolean and ENUM fields. (even when my enum field has a default value, it now works)
Thanks for taking the time though!