Jump to content

Populate Page Count Field via SQL


Ipro_TannerComes

Recommended Posts

  • IPRO Employee

If you need to populate a page count field as part of a production requirement, and this data isn't already in your Eclipse case, you can use the below query to populate a page count field.

 

 

Keep the following in mind when using this query:

 

1. It's best to back up a database prior to running any DML queries against it.

 

2. This query should be run against the case database.

 

3. Populate the name of the field as it appears in the case in the first DECLARE statement.

 

4. This query will update the specified Page Count field and the Has Image field based on information it finds in the DocumentPages field.

 

5. The query will fail if the specified Page Count field is either not found in the case, or is not a WholeNumber field.

 

/****************************************************************************
Update Page Count and HasImage for a case.
Use: Run against your case Database. 
Enter the field name between the quotes of the @PageCountFieldName variable.
If the field does not exist, or is not a WholeNumber value, the query will
fail and provide instructions to continue.

Author: TComes@Iprotech 05 12 2017
******************************************************************************/


DECLARE @PageCountFieldName nvarchar(20) = '' --Enter the field name here.
DECLARE @PageCountDBFieldName nvarchar(20)
DECLARE @PageCount NVARCHAR(7)
DECLARE @DocID INT
DECLARE @SQL NVARCHAR(MAX), @HasImageSQL NVARCHAR(MAX)
DECLARE @HasImageField NVARCHAR(20) = (SELECT ColumnName from vFieldDefinition where systemfieldtype = 25)
SET NOCOUNT ON

IF NOT EXISTS(SELECT TOP 1 1 FROM vFieldDefinition WHERE FieldName = @PageCountFieldName)
   THROW 50001, 'The specified field does not exist. Please ensure you''ve typed the field name exactly as it appears in the case and try again.', 1
ELSE IF ((SELECT TOP 1 FieldDataType FROM vFieldDefinition WHERE FieldName = @PageCountFieldName) <> 2) 
   THROW 50001, 'The specified field exists, however it is not of WholeNumber type. Please choose a different field or correct the field.', 1

SET @PageCountDBFieldName = (SELECT TOP 1 ColumnName FROM vFieldDefinition WHERE FieldName = @PageCountFieldName)

IF OBJECT_ID('tempdb..##docs') IS NOT NULL DROP TABLE ##docs
CREATE TABLE ##docs (
   DocID INT
)

INSERT INTO ##docs 
SELECT Docid from vDocumentFields

WHILE EXISTS (SELECT TOP 1 1 FROM ##docs)
BEGIN
   SET @DocID = (SELECT TOP 1 DocID FROM ##docs)
   SET @PageCount = (SELECT COUNT(*) FROM DocumentPages WHERE DocID = @DocId)
   SET @HasImageSQL = 'UPDATE vDocumentFields
       SET ' + @HasImageField + ' = ' + CAST(IIF(@PageCount < 1, 0, 1) AS NVARCHAR) + '
       WHERE DocID = ' + CAST(@DocID AS NVARCHAR)

   IF @PageCount = 0 SET @PageCount = ' '

   SET @SQL = 'UPDATE vDocumentFields
               SET ' + @PageCountDBFieldName + ' = ' + CAST(IIF(@PageCount = ' ', '''''', @PageCount) AS NVARCHAR) + '
               WHERE DocID = ' + CAST(@DocID AS NVARCHAR)
   EXEC(@SQL)
   EXEC(@HasImageSQL)

   PRINT 'Doc ID ' + CAST(@DocID AS NVARCHAR)+ ' has ' + CAST(IIF(@PageCount = ' ', 0, @PageCount) AS NVARCHAR) + ' pages.'
   DELETE FROM ##docs WHERE DocID = CAST(@DocID AS NVARCHAR)
END 



 

Link to comment
Share on other sites

  • IPRO Employee

If you need to populate a page count field as part of a production requirement, and this data isn't already in your Eclipse case, you can use the below query to populate a page count field.

 

 

Keep the following in mind when using this query:

 

1. It's best to back up a database prior to running any DML queries against it.

 

2. This query should be run against the case database.

 

3. Populate the name of the field as it appears in the case in the first DECLARE statement.

 

4. This query will update the specified Page Count field and the Has Image field based on information it finds in the DocumentPages field.

 

5. The query will fail if the specified Page Count field is either not found in the case, or is not a WholeNumber field.

 

/****************************************************************************
Update Page Count and HasImage for a case.
Use: Run against your case Database. 
Enter the field name between the quotes of the @PageCountFieldName variable.
If the field does not exist, or is not a WholeNumber value, the query will
fail and provide instructions to continue.

Author: TComes@Iprotech 05 12 2017
******************************************************************************/


DECLARE @PageCountFieldName nvarchar(20) = '' --Enter the field name here.
DECLARE @PageCountDBFieldName nvarchar(20)
DECLARE @PageCount NVARCHAR(7)
DECLARE @DocID INT
DECLARE @SQL NVARCHAR(MAX), @HasImageSQL NVARCHAR(MAX)
DECLARE @HasImageField NVARCHAR(20) = (SELECT ColumnName from vFieldDefinition where systemfieldtype = 25)
SET NOCOUNT ON

IF NOT EXISTS(SELECT TOP 1 1 FROM vFieldDefinition WHERE FieldName = @PageCountFieldName)
   THROW 50001, 'The specified field does not exist. Please ensure you''ve typed the field name exactly as it appears in the case and try again.', 1
ELSE IF ((SELECT TOP 1 FieldDataType FROM vFieldDefinition WHERE FieldName = @PageCountFieldName) <> 2) 
   THROW 50001, 'The specified field exists, however it is not of WholeNumber type. Please choose a different field or correct the field.', 1

SET @PageCountDBFieldName = (SELECT TOP 1 ColumnName FROM vFieldDefinition WHERE FieldName = @PageCountFieldName)

IF OBJECT_ID('tempdb..##docs') IS NOT NULL DROP TABLE ##docs
CREATE TABLE ##docs (
   DocID INT
)

INSERT INTO ##docs 
SELECT Docid from vDocumentFields

WHILE EXISTS (SELECT TOP 1 1 FROM ##docs)
BEGIN
   SET @DocID = (SELECT TOP 1 DocID FROM ##docs)
   SET @PageCount = (SELECT COUNT(*) FROM DocumentPages WHERE DocID = @DocId)
   SET @HasImageSQL = 'UPDATE vDocumentFields
       SET ' + @HasImageField + ' = ' + CAST(IIF(@PageCount < 1, 0, 1) AS NVARCHAR) + '
       WHERE DocID = ' + CAST(@DocID AS NVARCHAR)

   IF @PageCount = 0 SET @PageCount = ' '

   SET @SQL = 'UPDATE vDocumentFields
               SET ' + @PageCountDBFieldName + ' = ' + CAST(IIF(@PageCount = ' ', '''''', @PageCount) AS NVARCHAR) + '
               WHERE DocID = ' + CAST(@DocID AS NVARCHAR)
   EXEC(@SQL)
   EXEC(@HasImageSQL)

   PRINT 'Doc ID ' + CAST(@DocID AS NVARCHAR)+ ' has ' + CAST(IIF(@PageCount = ' ', 0, @PageCount) AS NVARCHAR) + ' pages.'
   DELETE FROM ##docs WHERE DocID = CAST(@DocID AS NVARCHAR)
END 



 

Link to comment
Share on other sites

  • IPRO Employee

If you need to populate a page count field as part of a production requirement, and this data isn't already in your Eclipse case, you can use the below query to populate a page count field.

 

 

Keep the following in mind when using this query:

 

1. It's best to back up a database prior to running any DML queries against it.

 

2. This query should be run against the case database.

 

3. Populate the name of the field as it appears in the case in the first DECLARE statement.

 

4. This query will update the specified Page Count field and the Has Image field based on information it finds in the DocumentPages field.

 

5. The query will fail if the specified Page Count field is either not found in the case, or is not a WholeNumber field.

 

/****************************************************************************
Update Page Count and HasImage for a case.
Use: Run against your case Database. 
Enter the field name between the quotes of the @PageCountFieldName variable.
If the field does not exist, or is not a WholeNumber value, the query will
fail and provide instructions to continue.

Author: TComes@Iprotech 05 12 2017
******************************************************************************/


DECLARE @PageCountFieldName nvarchar(20) = '' --Enter the field name here.
DECLARE @PageCountDBFieldName nvarchar(20)
DECLARE @PageCount NVARCHAR(7)
DECLARE @DocID INT
DECLARE @SQL NVARCHAR(MAX), @HasImageSQL NVARCHAR(MAX)
DECLARE @HasImageField NVARCHAR(20) = (SELECT ColumnName from vFieldDefinition where systemfieldtype = 25)
SET NOCOUNT ON

IF NOT EXISTS(SELECT TOP 1 1 FROM vFieldDefinition WHERE FieldName = @PageCountFieldName)
   THROW 50001, 'The specified field does not exist. Please ensure you''ve typed the field name exactly as it appears in the case and try again.', 1
ELSE IF ((SELECT TOP 1 FieldDataType FROM vFieldDefinition WHERE FieldName = @PageCountFieldName) <> 2) 
   THROW 50001, 'The specified field exists, however it is not of WholeNumber type. Please choose a different field or correct the field.', 1

SET @PageCountDBFieldName = (SELECT TOP 1 ColumnName FROM vFieldDefinition WHERE FieldName = @PageCountFieldName)

IF OBJECT_ID('tempdb..##docs') IS NOT NULL DROP TABLE ##docs
CREATE TABLE ##docs (
   DocID INT
)

INSERT INTO ##docs 
SELECT Docid from vDocumentFields

WHILE EXISTS (SELECT TOP 1 1 FROM ##docs)
BEGIN
   SET @DocID = (SELECT TOP 1 DocID FROM ##docs)
   SET @PageCount = (SELECT COUNT(*) FROM DocumentPages WHERE DocID = @DocId)
   SET @HasImageSQL = 'UPDATE vDocumentFields
       SET ' + @HasImageField + ' = ' + CAST(IIF(@PageCount < 1, 0, 1) AS NVARCHAR) + '
       WHERE DocID = ' + CAST(@DocID AS NVARCHAR)

   IF @PageCount = 0 SET @PageCount = ' '

   SET @SQL = 'UPDATE vDocumentFields
               SET ' + @PageCountDBFieldName + ' = ' + CAST(IIF(@PageCount = ' ', '''''', @PageCount) AS NVARCHAR) + '
               WHERE DocID = ' + CAST(@DocID AS NVARCHAR)
   EXEC(@SQL)
   EXEC(@HasImageSQL)

   PRINT 'Doc ID ' + CAST(@DocID AS NVARCHAR)+ ' has ' + CAST(IIF(@PageCount = ' ', 0, @PageCount) AS NVARCHAR) + ' pages.'
   DELETE FROM ##docs WHERE DocID = CAST(@DocID AS NVARCHAR)
END 



 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...