175
Views
8
Comments
Solved
Update a Single Field without overwriting Nulls on other Fields
Question

I'm updating a single field on a table using the standard Update action that comes with all tables on the data tab. When I do this, any null field in that row is changed to a default value (0, '', or '1900-01-01'). Is there any way to prevent this, i.e. to only update the field I want to update and leave the other fields alone? I'm familiar with the SQL action component, which should work? Is that the best way to accomplish this task, or is there a better way?

Thanks



Solution

Hi Kevin,

I believe you only have 2 options, use an SQL query, or using a Stored Procedure, but that's probably unnecessarily complicated.

Thing is, in External References, the Update Behaviour is disabled:

In fact, all of the Advances setting are.

Is this record on a foreign table or an OutSystems table (entity)? Reason I am asking is that if this is an OS entity, your record when created will be populated with 'default' values. This is because OS have no null values inserting into the database.

Rank: #27146

The table is on a Microsoft SQL Server database connected to the website through Integration Studio. The table allows Nulls on some fields and I am not allowed to  change those fields.

mvp_badge
MVP
Rank: #43

Hi Kevin,

I don't think there's a way to prevent this with the default generated Update CRUD. If you want this specific behaviour, there's a couple of solutions you can try:

a) you could write an Advanced Query with an UPDATE statement for your specific field with a WHERE clause restricting it the record(s) you want updated. This should leave other Entity fields untouched;

b) I don't have access to an external entity to check, but do you have access to the Update Behaviour property in those external entities? It can be changed to only set fields that have been changed, so it cause the Update CRUD to not mess with your remaining fields. "Supports Nulls" might also be worth a try.

Rank: #27146

Afonso. 

Thanks for the response. Changing the Update Behavior property sounds like the solution, but I cannot see it on the Entity tab so I don't think I have access to it. There is a Default Value behavior that allows 'Convert to/from NULL' but that did not work. Please let me know if you have any ideas about how I might change Update Behavior.

mvp_badge
MVP
Rank: #43

Just to make sure, do you not have access to these settings in your Entities? Sorry for not being able to check, I don't have an environment with external entities on hand.


If neither of those properties worked out, then I think your other alternative is to write SQL in an Advanced Query to only update the fields you want to change.

Solution

Hi Kevin,

I believe you only have 2 options, use an SQL query, or using a Stored Procedure, but that's probably unnecessarily complicated.

Thing is, in External References, the Update Behaviour is disabled:

In fact, all of the Advances setting are.

Rank: #27146

Yeah. I figured SQL query might be my only real option. No problem. I was just looking for the best way to do this. 

Thanks Afonso and Carlos for your responses.

Rank: #32903

In Integration Studio there is a property named "Ignore" for the table attributes.  I have a column in all my tables named "modified_at".  This column is updated automatically by a trigger and I never want Outsystems to update that column.  Can I simply check the box to "ignore" this column?  Will doing so NOT update it?

Thanks in advance for any help.

Scott