QueryGen Case: Replicating Forms

Copying forms from company to company is easy.

Archiving Forms with Query Export

Simply Select your form data:

Select FORM_ID,FORM_TYPE,FORM_NAME,COMPANY_ID,USER_ID,IP_ADDRESS,FORM_DATA,MEMO,LAST_CHANGE_DATETIME,LAST_CHANGE_USER,ACTIVE_FLAG FROM CCTFORM
cctform where form_id = 'ARSTM'

and Export it to a .csv or excel file. It will archive all form data but not image data.

• When you run the query it will just show the word "(Memo)" in the data cell. Don't worry, it the data is all there.

QueryGen to Update/Create Forms

Select:

Select FORM_ID,FORM_TYPE,FORM_NAME,COMPANY_ID,USER_ID,IP_ADDRESS,FORM_DATA,MEMO,LAST_CHANGE_DATETIME,LAST_CHANGE_USER,ACTIVE_FLAG FROM CCTFORM
cctform where form_id = 'ARSTM'

Model Query to Update:

/*  ============================================================ */
UPDATE CCTFORM SET FORM_DATA=
'{FORM_DATA}'  
WHERE FORM_ID='{FORM_ID}';

INSERT INTO CCTFORM
( FORM_ID, FORM_TYPE, FORM_NAME, COMPANY_ID, USER_ID, IP_ADDRESS, FORM_DATA, MEMO, LAST_CHANGE_DATETIME, LAST_CHANGE_USER, ACTIVE_FLAG)  
SELECT
 '{FORM_ID}', '{FORM_TYPE}', '{FORM_NAME}', '{COMPANY_ID}', '{USER_ID}', '{IP_ADDRESS}', '{FORM_DATA}', '{MEMO}', CAST('TODAY' AS TIMESTAMP), 'HIT', '{ACTIVE_FLAG}' 
 from artloc where loc_id=0 and not EXISTS (SELECT 'A' FROM CCTFORM WHERE FORM_ID= '{FORM_ID}'  );

Notes:

• This is designed to update an existing form and then insert it if missing.
• When you run the query it will just show the word "(Memo)" in the data cell. Don't worry, it the data is all there.