Can't Get the Primary Key of a record that was just created

[Manoj Chaurasia]

I am using the Microsoft SQLServer Connector with Scribe online to create a parent record and related child records in a simple SQLServer database. I can create the parent record fine, but when I try to set the foreign key field in the child record to the value of the primary key on the parent record, I get an error that NULL cannot be inserted into that field. If I query the parent record later, the primary key is properly populated. The primary key is defined as follows:

[ClaimID] [bigint] IDENTITY(1,1) NOT NULL,

CONSTRAINT [PK_Claim] PRIMARY KEY CLUSTERED

(

[ClaimID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

I have tried setting the foreign key of the child record to the value of the parent record create block and have also tried doing a lookup on the parent after the create where the record id = the create block record id. Neither works.

Does anyone have any suggestions

Posted by Jim F. on May 31 2015 11:20PM { U4307 , F35 , T3991 , C12043 }

[Manoj Chaurasia]

I think I need to see your maps to really know what is going on but I will help you from the info I have. If you are using an advanced map you can query your source parent record and you then can update/insert the target (SQL) parent table and the next block shopuld be a for each child block and an update/insert of the child record. In the field maping you can use the results from the first update/insert of the parent record in the parent record key field. Please send me an email with more detail or some screenshots and I will be happy to help further.

Tom Brown Sales Engineer tom.brown@scribesoft.com

Posted by Tom B. on Jun 1 2015 1:30PM { U4642 , C12045 }

[James Houck]

@OpenMind - did you ever solve this I'm getting this issue as well. Trying to create new records using the primary key of previously inserted record as a field value but it returns NULL even though the first record was successfully inserted in the database. It's maddening!

[Ash Arendsz]

Double check if the create Parent record block is using batch, turn it off to troubleshoot and see if that solves the problem.

 

Also verify how are you passing/mapping your record ID to the child create block (are there any other SQL level constraints, what is the datatype being mapped from and to, are they the same ).

 

To troubleshoot/test if you are getting record id, try using Debug or Scribe labs Tools  connector.

 

https://support.tibco.com/s/article/Scribe-Labs-Tools (shows you how to write to Log file for troubleshooting.)