API Return a Value For Nulls

Prepify

I have a Query All Records step in my API. Based on some joins in the API one of my returned columns either has a 1 or a null. I instead would like it to return a 1 or 0 before the final output.

What is the most efficient way to achieve this recoding from null to 0? Is it possible to address it in the original Query All Records step, or do I need to create a second function? If I need an additional function, what is the most light weight/fastest way to do this?

Pawel Magdanski

Do you want to just replace the null's in the functions stuck and the output of the endpoint or in table itself?
How many records do you have? do you just need to have 0's displayed instead of null on frontend side?

If the first then you can have a loop with update variable where you are replacing null with 0 in the path of your field. If there is a lot of records then it may take some time and no be very efficient. If you want to change in table then the same story but you only need to update it once and edit the records with the changes.

If you just need to show 0 instead of null on front-end but you want null to remain in table then maybe best solution is to replace null with 0 on the front-end side.

If you can provide some more details then we can think about a particular solution for your case

Prepify

@Pawel Magdanski Thank you for the questions.

For the column that I am trying to manipulate, the nulls are being created by the fact that the table I am joining onto the parent table doesn't have records to correspond to every row in the parent table. So there isn't really any values to manipulate in the original table itself since those rows don't even exist.

I was mostly expecting to manipulate the output of the original "Query All Records" within the function stack. Right now, I am just setting up the database, so there aren't many records to loop over, but the table will grow over time, so ideally a looping method would not be the chosen option. I was hoping there was a way similar to a python dataframe to apply() a function/calcuation to all rows at once rather than looping over one at a time. Or even like a .fillna() function.

Theoretically I could have it happen in the front end if that is possible. I am using Weweb. And the particular use case I am trying to achieve is using the adjusted column with 1's & 0's (rather than 1's and nulls) as a sorting criteria. I know I can manipulate the appearance of nulls as they are displayed on a page. But I don't think there is a way to manipulate it before using it as a sorting criteria.

Pawel Magdanski

Ok I think I found something. You can use the eval feature in the query and choose set the field that is null, then use coalesce filter on it.

Prepify

@Pawel Magdanski Spectacular! This is exactly what I needed! It worked like a charm. Thank you!

Pawel Magdanski

No problem, happy to help!