SQL to add new Task with Flag

[aaron.bouwman]

Background: Recently introduced a new template with 20 more tasks than
the previous template. Trying to write a query to update existing
templates with the 20 new tasks, and also set a Custom Field value
(Enterprise Flag used for filtering).

I have successfully added the 20 tasks to a project that is using the
older template with the following query (repeated 20 times with some
different values of course):

INSERT INTO MSP_TASKS VALUES
(0,152,0.000000,0.000000,0.000000,0,0,'4.7',
0,0,0.000000,0.000000,0.000000,0,49315950,NULL,
354,176,0,1,0,1,0,0,0,1,0,0,0,0,0,0,0,0,0,1,0,0,'2005-08-11
08:30:00.000','2049-03-03 08:30:00.000',NULL,NULL,
49315950,2,0,21,0,0,0,41,0,0,10,'2005-08-11 08:30:00.000','2005-08-11
08:30:00.000',NULL,NULL,NULL,NULL,NULL,500,0,0,1,3,'2007-02-01
13:43:00.000',NULL,NULL,'2005-08-11 08:30:00.000','2049-03-03
08:30:00.000',-1,NULL,
0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,0.000000,NULL,NULL,NULL,NULL,NULL,NULL,'Project
Costs',NULL,NULL,NULL,0.000000,0,0.000000,0.000000)

The next thing I wanted to do was set the value of a Custom Field. In
this case it's an Enterprise Flag. The following query partially
works:

INSERT INTO MSP_FLAG_FIELDS (PROJ_ID, FLAG_CATEGORY, FLAG_REF_UID,
FLAG_FIELD_ID, FLAG_VALUE)
VALUES (152, 0, 354, 188744339, 1)
INSERT INTO MSP_FLAG_FIELDS (PROJ_ID, FLAG_CATEGORY, FLAG_REF_UID,
FLAG_FIELD_ID, FLAG_VALUE)
VALUES (152, 0, 354, 188744649, 1)

Here's what I mean by partially works... when I run the query the
values are definately inserted into MSP_FLAG_FIELDS and FLAG_VALUE is
set to 1 (SELECT * FROM MSP_FLAG_FIELDS
WHERE PROJ_ID = 152 AND FLAG_REF_UID = 354)... but when I open the
project in MS Project, double click the task, goto Custom Fields tab,
the Enterprise Flag has not been updated (from the default of 'NO' to
what I set it to 'YES'). This is where it's not working.
Now that I have the Project open, and the flag hasn't been updated, I
set it manually to 'YES', then SAVE. Run a query against the
MSP_FLAG_FIELDS table and the results are the same as before (when I
ran the SELECT query), the FLAG_VALUE is set to 1... Now back to the
same task, open and change the flag value to 'NO', then SAVE and
close. Run the SELECT query and the FLAG_VALUE is now set to NULL...
interesting. Now I run an update query to change the FLAG_VALUE to 1
(UPDATE MSP_FLAG_FIELDS SET FLAG_VALUE = 1 WHERE PROJ_ID = 152 AND
FLAG_REF_UID = 354)... Open the project again, select the task, goto
Custom Fields tab, and see the Enterprise Flag has now been updated to
'YES'! From here I can go back and forth setting the Flag from Yes to
No in my query and it works. So this is where it's working.

What am I missing? Something in the creation of the Flag record. Is
there another step besides just inserting it into the MSP_FLAG_FIELDS
table?

Thanks in advance for any help.

 

[aaron.bouwman]

Figured it out...

After inserting the tasks and flags an update query has to be done
against the Project record:

UPDATE MSP_PROJECTS SET PROJ_EXT_EDITED_FLAG = 1 WHERE PROJ_ID = 152

Now when the project is opened, all custom flag fields are updated.

I found the answer here: http://www.mcse.ms/message2375803.html

 

[Rod Gill]

Correct, you need to thoroughly read and digest the pjdb.htm file (its in
one of Project's program folders).

--

Rod Gill
Project MVP

NEW!! Project VBA Book, for details visit: http://www.projectvbabook.com