Master/Detail problems

jrheisler · Post by **jrheisler** » Sun 27 Nov 2011 18:27

I am using two TUniQuery components with SQL Server.

The mater component is a simple select on a table which includes the following fields:

ASB_ID;ASB_REF;ASB_NAME;ASB_REV and several others.

My Detail table though is not a straight forward relationship. It's sql is as follows:

select b.note_num,b.title,c.text_value
FROM object_ids a, cm_object_notes b,cm_note_strings c
WHERE a.obj_id = b.obj_id
AND a.obj_key = 'ASBREF*'+:ASB_ID+'*'+:ASB_REF+'*'+:ASB_NAME+'*'+:ASB_REV
and c.string_id=SUBSTRing(b.handle,17,10)

I have the MasterSource set to the master table's TUNIDataSource, and I have the MasterFields set to ASB_ID;ASB_REF;ASB_NAME;ASB_REV.

After I open the master table, I do not get any values into the :ASB_ID, :ASB_REF... parameters.

What am I doing wrong?

Thanks!

AndreyZ · Post by **AndreyZ** » Mon 28 Nov 2011 12:32

Hello,

Please post here or send to andreyz*devart*com a script to create all tables that are used in this master/detail relationship.

jrheisler · Post by **jrheisler** » Mon 28 Nov 2011 12:44

Here are the scripts to make the three tables. Thanks!!!

CREATE TABLE OBJECT_IDS
(
OBJ_ID NUMERIC(10) NOT NULL,
OBJ_KEY VARCHAR(260) NOT NULL
)

CREATE INDEX OBJECT_IDS_KEY_NDX ON OBJECT_IDS
(OBJ_KEY)

CREATE UNIQUE INDEX PK_OBJECT_IDS ON OBJECT_IDS
(OBJ_ID)

CREATE TABLE CM_OBJECT_NOTES
(
OBJ_ID NUMERIC(10) NOT NULL,
NOTE_NUM NUMERIC(10) NOT NULL,
TITLE VARCHAR(100) NOT NULL,
HANDLE VARCHAR(76) NOT NULL
)

CREATE UNIQUE INDEX PK_CM_OBJECT_NOTES ON CM_OBJECT_NOTES
(OBJ_ID, NOTE_NUM)

CREATE TABLE CM_NOTE_STRINGS
(
STRING_ID NUMERIC(10) NOT NULL,
ROW_SEQUENCE NUMERIC(10) NOT NULL,
TEXT_TOTAL NUMERIC(10) NOT NULL,
TEXT_VALUE VARCHAR(1786) NOT NULL
)

CREATE UNIQUE INDEX PK_CM_NOTE_STRINGS ON CM_NOTE_STRINGS
(STRING_ID, ROW_SEQUENCE)

AndreyZ · Post by **AndreyZ** » Mon 28 Nov 2011 15:59

I cannot reproduce the problem. Please specify the following:
- a script to create your master table;
- the exact version of UniDAC. You can learn it from the About sheet of TUniConnection Editor.

jrheisler · Post by **jrheisler** » Mon 28 Nov 2011 16:11

The scripts are in my previous post, this is the version I am using 4.0.2 for Delphi 7.

Thanks!

AndreyZ · Post by **AndreyZ** » Tue 29 Nov 2011 09:46

I still cannot reproduce the problem. You didn't provide a script to create your master table (that has the ASB_ID, ASB_REF, ASB_NAME, ASB_REV columns). I created the following master table:

Code: Select all

CREATE TABLE [dbo].[ASB](
	[ASB_ID] [varchar](20) NULL,
	[ASB_REF] [varchar](20) NULL,
	[ASB_NAME] [varchar](20) NULL,
	[ASB_REV] [varchar](20) NULL
)

There were no problems with receiving parameters for the detail table from this master table. Please post a script to create your master table here.

jrheisler · Post by **jrheisler** » Tue 29 Nov 2011 12:23

Sorry, I forgot the parent:

CREATE TABLE ASBUILT_MASTER
(
ASB_ID NUMERIC(10) NOT NULL,
ASB_NAME VARCHAR(32) NOT NULL,
CONTR_NUM VARCHAR(20) NOT NULL,
PROJNAME VARCHAR(20) NOT NULL,
CI_NUM VARCHAR(32) NOT NULL,
PART_NUM VARCHAR(32) NOT NULL,
PART_CAGE VARCHAR(6) NOT NULL,
TOPSERIAL_NUM VARCHAR(32) NOT NULL,
TOPSERIAL_DATE DATETIMEOFFSET,
SERIAL_TRACKED VARCHAR(2) NOT NULL,
APPLY_CHILDREN VARCHAR(2) NOT NULL,
REF_DESIG_CONCAT VARCHAR(2) DEFAULT ' ' NOT NULL
)

CREATE UNIQUE INDEX PK_ASBUILT_MASTER ON ASBUILT_MASTER
(ASB_ID)

AndreyZ · Post by **AndreyZ** » Wed 30 Nov 2011 17:58

The point is that values for parameters in the detail table are taken from the master table fields that have the same names as parameters. Your master table doesn't have the ASB_REF and ASB_REV fields. That's why values of these parameters are NULL. To solve the problem, you should change the ASB_REF and ASB_REV parameters names to names of existent fields of your master table.