Adding a Master Record and a Linked Detail Record

Application Note for QuNect ODBC for QuickBase

A scalar function built in to the QuNect ODBC for QuickBase driver returns the Quickbase built in field, Record ID# value of the record most recently created by the current QuNect ODBC for QuickBase connection. This function is called IDENTITY and takes no arguments. Also you can pass two SQL statements to QuNect ODBC for QuickBase. These statements have to be separated by a semicolon. Only the last statement can return a result set. This is an example of a SQL statement that uses the IDENTITY function to create a master record and attach a detail record. The key field of the master record is the built-in field called Record ID#. This technique will not work if, and is not necessary, if the key field of the master table is not the built-in field called Record ID#. The built-in Quickbase field Record ID# can be renamed so it may have another name.

 
    INSERT INTO "Master Table bce33vr7t" ("Master Text Field")
    VALUES ('This is the master record');
    INSERT INTO "Details Table bce33vr7s" ("Detail Text Field", "Reference Field")
    VALUES ('This is the detail record', {fn IDENTITY()})

In SQL Server you can use a statement like this to get the Record ID# value of the most recently created record.

 
	DECLARE @newestRecordID int 
	SELECT @newestRecordID = Newest_Record_ID from OpenQuery(QUICKBASE, 'SELECT @@identity')
	PRINT @newestRecordID