DQL for deleting/ Removeing one value in repeating attribute

In one of my workflow using Process Builder, I have "DQL Write" step, I want to write a DQL in there to deleting/ Removeing one value in repeating attribute.

I have a custom type called invoice_document inherited from dm_document, with repeating attribute of item_name of STRING type. In the DQL I want to delete any item_name with say "Paper" as value for a given r_object_id.

Can I write one DQL to accomplish that ?

Note

I tried to directly update underlying repeating attribute table

update dm_dbo.invoice_document_r set item_name = NULL where r_object_id = 'XXXXXXXXXXXXX' and item_name = 'paper'

I get this error Error occured during query execution :[DM_QUERY_E_TABLE_NO_ACCESS]error: "The table, invoice_document_r, is not registered or you do not have access to it."

But when I do select on dm_dbo.invoice_document_r , then I get the result, for example the query below returns me the result

select * from dm_dbo.invoice_document_r where r_object_id = 'XXXXXXXXXXXXX' and item_name = 'paper'

Find more posts tagged with

Comments

DCTM_Guru

Your syntax is all wrong! You are mixing registered table syntax (dm_dbo.) and then using table names (invoice_document_r) where you should just be using UPDATE OBJECT syntax.

update invoice_document object set item_name = NULL where r_object_id = 'XXXXXXXXXXXXX' and item_name = 'paper'

Your select DQL is also wrong; it should be the following:

select * from invoice_document where r_object_id = 'XXXXXXXXXXXXX' and item_name = 'paper'

DQL allows you to not worry about table joins, so your query should be more "object oriented" vs typical SQL like calls. Read the DQL manual for the details.

uvarasan

Couple of things...

Added ANY since item_name is a repeating attribute..

update invoice_document object set item_name = NULL where r_object_id = 'XXXXXXXXXXXXX' and ANY item_name = 'paper'

Basically this DQL will update always Index 0 position only.

I don;t think so it is going to set NULL value, it will expect value as a String like set item_name='NULL'

and value will be stored as a NULL string.

I don;t see any DQL to replace with new value without index position on the repeating attribute...it always update 0 index if position not specified.

-Uva

DCTM_Guru

Didnt play close attention to post. If you want NULL value for string, just replace it with two single quotes:

update invoice_document object set item_name = '' where r_object_id = 'XXXXXXXXXXXXX' and ANY item_name = 'paper'