This is for NOTE fields
-- =============================================
--get note fields
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'ddf_GetExtenderNoteFieldID')
DROP FUNCTION ddf_GetExtenderNoteFieldID
GO
CREATE FUNCTION ddf_GetExtenderNoteFieldID (@fieldnam varchar(31))
--run this line to test the function:
-- select dbo.ddf_GetExtenderNoteFieldID ('students')
Returns int
AS
begin
declare @Field_ID int
select @Field_ID = lnitmseq from ext43204 where Note_Type_Description = @fieldnam
return (@field_id)
end
go
grant exec on ddf_GetExtenderNoteFieldID to public
This is for string type fields
-- =============================================
--string fields
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'ddf_GetExtenderFieldID')
DROP FUNCTION ddf_GetExtenderFieldID
GO
CREATE FUNCTION ddf_GetExtenderFieldID (@fieldnam varchar(31))
--run this line to test the function:
--select dbo.ddf_GetExtenderFieldID ('employee id')
Returns int
AS
begin
declare @Field_ID int
select @Field_ID = Field_ID from EXT20010 where FIELDNAM = @fieldnam
return (@field_id)
end
go
grant exec on ddf_GetExtenderFieldID to public
This is the general purpose script that I use to view Extender data on an order. This customer has text fields and note fields
select h.sopnumbe,
hEmployeeID.STRGA255 as Employee,
--header
e0.Extender_Record_ID,
eh4.STRGA255 as EmployeeName,
consult.STRGA255 as Consultant,
hEmployeeID.STRGA255 as EmployeeID,
filenum.STRGA255 as FileNum,
secpo.STRGA255 as SecPO,
consult.STRGA255 as Consultant,
--lines
l.itemnmbr,
l.LNITMSEQ,
el.Extender_Record_ID,
student.TXTFIELD as student,
classno.TXTFIELD as classno,
comment.TXTFIELD as comment
from sop30200 h
join sop30300 l on l.sopnumbe = h.sopnumbe and l.soptype = h.soptype
--extender header
left join ext01100 e0 on e0.Extender_Window_ID = 'SO_HDR_ADDINFO' and e0.Extender_Key_Values_1 = h.SOPNUMBE
left join ext01101 eh4 on eh4.Extender_Record_ID = e0.Extender_Record_ID and eh4.Field_ID = dbo.ddf_GetExtenderFieldID ('employee name')
left join ext01101 hEmployeeID on hEmployeeID.Extender_Record_ID = e0.Extender_Record_ID and hEmployeeID.Field_ID = dbo.ddf_GetExtenderFieldID ('employee id')
left join ext01101 filenum on filenum.Extender_Record_ID = e0.Extender_Record_ID and filenum.Field_ID = dbo.ddf_GetExtenderFieldID ('file no')
left join ext01101 secpo on secpo.Extender_Record_ID = e0.Extender_Record_ID and secpo.Field_ID = dbo.ddf_GetExtenderFieldID ('Secondary PO')
left join ext01101 consult on consult.Extender_Record_ID = e0.Extender_Record_ID and consult.Field_ID = dbo.ddf_GetExtenderFieldID ('Consultant')
--extender line
left join ext01100 el on el.Extender_Window_ID = 'SOLINE' and el.Extender_Key_Values_2 = l.SOPNUMBE and el.Extender_Key_Values_1 = l.lnitmseq
left join ext01500 student on student.Extender_Record_ID = el.Extender_Record_ID and student.Note_Type = dbo.ddf_GetExtenderNoteFieldID ('students')
left join ext01500 classno on classno.Extender_Record_ID = el.Extender_Record_ID and classno.Note_Type = dbo.ddf_GetExtenderNoteFieldID ('Class No')
left join ext01500 comment on comment.Extender_Record_ID = el.Extender_Record_ID and comment.Note_Type = dbo.ddf_GetExtenderNoteFieldID ('Comments')
where h.sopnumbe = 'INV0107'