Hello!
I am starting to experiment with using the Local Use fields in the constituent import file and LP_CONSTITUENT_IMPORT and I am a little stuck with my first pass.
In this case I want to update TX_CUST_TKW to insert the Customer No and have the appropriate Interest selected. Based on another forum thread I tried the following in LP_CONSTITUENT_IMPORT:
-- Local code goes here: -- Local code goes here:
if @run_mode = 1 and @mode = 'A'
Insert into [dbo].TX_CUST_TKW (Customer_no, Tkw, Selected)
Select customer_no, a.local_use0, a.local_use1
From dbo.tw_constituent_import a
where A.customer_no = @customer_no and a.session = @session_id
And a.success_ind = 'Y'
and a.local_use0 = 10 and a.local_use1 = 'Y'
RETURN
While the import does run successfully and the new constituents are created, no update is made to TX_CUST_TKW.
After playing around and removing A.customer_no = @customer_no from the where clause I was seeing the first imported record update TX_CUST_TKW but subsequent records produced a foreign key error because it was attempting to insert the customer ID from the first record over and over rather then the customer number for the next new record. I feel like I am close on this but not sure what might be wrong.
Thanks!
Remember that this procedure is called multiple times for every single customer imported. If you are using @mode = 'A', then you can only be inserting for one constituent at a time. So, you do need a filter on @customer_no. If you aren't getting the inserts to work with that filter, my guess is you'll need to look more closely at the rest of your WHERE clause. You might consider running a test import and then invoking your code manually for certain values of @session_id and @customer_no to validate that what you expect to be in tw_constituent_import is actually there.
Thanks Nick!
Looks like it did not like the A.success = 'Y' . once i removed this I get the results i expected!