Using multilookup upsert¶
/*
Using Mulitlookup upsert to modify of delete items in a multi lookup scenario
*/
/*
create contacts if not already done
*/
exec dbo.spMFCreateTable @ClassName = N'Contact Person' -- nvarchar(128)
, @Debug = 0 -- smallint
exec dbo.spMFUpdateTable @MFTableName = N'MFContactPerson' -- nvarchar(200)
, @UpdateMethod = 1
-- to add items to the list use updateflag = 1
select dbo.fnMFMultiLookupUpsert('12,456,34','6,12',1)
-- example of usage to add all the contacts for customer to the customer project
declare @NewContact_ID int
update mcp
set mcp.Contact_Person_ID = dbo.fnMFMultiLookupUpsert(mcp.Contact_Person_ID,cast(mcp2.ObjID as varchar(10)),1), process_ID = 1
-- select *
from dbo.MFCustomerProject as mcp
cross apply dbo.fnMFSplitString(customer_ID,',') as fmss
left join dbo.MFContactPerson as mcp2
on fmss.Item = mcp2.Owner_Customer_ID
where mcp.Project_Manager_ID is not null
-- check before update
select * from dbo.MFCustomerProject as mcp where mcp.Process_ID = 1
-- update to MF
declare @PROCESSBATCH_id INT
exec dbo.spMFUpdateTable @MFTableName = N'MFCustomerProject' -- nvarchar(200)
, @UpdateMethod = 0
,@ProcessBatch_ID = @PROCESSBATCH_id output
,@debug = 0
SELECT * FROM dbo.MFProcessBatchDetail AS mpbd WHERE mpbd.ProcessBatch_ID = @ProcessBatch_ID
-- Check result
select * from dbo.MFCustomerProject as mcp
-- to delete an item from the list use updateflag = -1
select dbo.fnMFMultiLookupUpsert('12,456,34','34',-1)