Set Boolean to False with Server Action On a Timer

Hello,

I am building a Web Reactive App. What I'm trying to do is to change the value of a Boolean attribute in a specific time. I did a server action that calls the Entity with an Aggregate (it filters all the records I want to change) and then use For Each to run a SQL. In the SQL I tried to change the value of the Boolean attribute with setting it equal to 2 and then tried with False and none have made the change.

Hi Yomarie,

If you used sql for update boolean type varibale than you need to set True for 1 and False for 0

Like

Update TableName set BooleanColumn=1 where Condition

or

Update TableName set BooleanColumn=0 where Condition


Regards

Rahul Sahu

Hi Yomarie,

Putting single quotes around False will also work, like this: 'False' or you can follow Rahul's suggestion and use 0 (for False) and 1 (for True).

But more importantly, if you want to bulk update a set of records it is a best practice to apply the filter directly inside the SQL tool instead of using an aggregate with a For Each node.

Hope this helps!

Regards,

Nordin

Yes,

exactly what Nordin says.


Also, just want to point out, if you leave as is, with the aggregate and for each, you will now update all rows several times over, not just the selected ones, as you have no where clause in your sql.


Dorine

Hi, thank you all for the reply.

I changed the flow as you recommended and still does not work. I don't know if the Where clause is correct tho. Since is not all the true attributes I want to change I tried to filtered comparing the current month with the month of the record.

Hi Yomarie, 

Dont know of you entity data. but 

{UnidadInfo}.[MesMarbete] is hold date type data then you need to change in where condition like.

Update {UnidadInfo} set {UnidadInfo}.[Marbete]='False'

Where Month({UnidadInfo}.[MesMarbete])=Month(getdate())


Regards

Rahul Sahu

Hi Rahul,

MesMarbete is integer data type. I understand that the function

Month(CurrDate())

gives back a integer number as well. I don't think  Month({UnidadInfo}.[MesMarbete]) is going to work since is not Date data type.


Solution

Hi Yomarie,

Boolean Attributes in the OutSystems database are actuall "bit" values (in MSSQL, in Oracle it will be equavalent), and you should therefore use 0 or 1 for False and True when assigning them or testing them. So if you want to set all "Marbete" values to False for the current month, use:

UPDATE {UnidadInfo}
SET {UnidadInfo}.[Marbete] = 0
WHERE {UnidadInfo}.[MesMarbete] = @Month
   AND {UnidadInfo}.[Marbete] = 1

Notes:

  1. Use an input parameter instead of Month(CurrDate()). CurrDate() isn't SQL, so it won't work, and using the SQL equivalent GetDate() is tricky, as you may run into problems when running the query around midnight.
  2. Only update the records that currently are not yet set to False, this will lessen the amount of logging that's done in the database itself (and may improve performance depending on the amount of records).


Solution

Hello Kilian, I modify the SQL with your recommendation and the problem persist. When testing it the outcome is that there is no rows returned. 

Of course there's no rows returned, this is an update query!

Even with the timer it won't make the update 

Are you sure the timer has run ?


If I were you, i'd put the action in a test screen first, and debug it until it works.  Then put it behind a timer.

Thank you for the observation Dorine, the timer wasn't running because of the difference in the time zone. It's updating now, thank you all!