Deleting Multiple Relationships in Primavera P6
The purpose of this article is to provide some different methods for mass deletion of activity relationships in Primavera P6. Instead of individually deleting relationships, which can be very tedious, this will provide some different options to delete multiple relationships at one time.
Option 1 - Delete All Relationships
I won't ask why, but there are times when you've totally screwed something up so badly that you just need to start over. Hence, your schedule has become one giant bowl of spaghetti and you want to wash off the marinara and start with just the noodles again.
Here's how you delete all of the relationships in your P6 file without having to go one by one. Keep in mind, this will delete ALL relationships. But you'll be able to keep all of your activities, activity codes, resources, etc. It's so easy, you'll be able to do it in one step!
Step 1 - Copy & Paste File
Navigate to the projects folder in P6 and to the project where you want to delete all of the relationships. Right click and choose "Copy". Then, right click again and choose "Paste".
Right Click + Copy
Right Click + Paste
Keep all options except the relationships:
First Box Options for Pasting and Click OK
Second Box Options for Pasting and Click OK
Third Box Options for Pasting - Uncheck Relationships and Click OK
BOOM!!! No more relationships!!! Congrats, you broke out of jail and you're back to "GO". I know what you're thinking…"Pat, that was more than one step". No, I put it all under Step 1, so it's one step.
Option 2 - Delete Some Relationships Using an Excel Import
My God, your schedule is impeccable! But, there's this one little blemish under the structure section and she's getting a little long in the tooth. We don't want full reconstructive surgery, but we want to shave things down a bit and slap on some veneers. This one's a little more complicated than our first option, but I promise, I'll do it all in one step. Here's how to mass delete SOME relationships from your schedule. i.e. you know the specific section of activities that you want to remove the relationships from, and you want to keep the rest of the schedule in-tact.
Here's an overview of what we'll be doing. The snapshot below shows my open project and I've broken it down into two sections: 1. The activities with the relationships we want to keep. 2. The activities with the relationships we want to delete (highlighted in blue).
IMPORTANT!! MAKE A COPY OF YOUR FILE BEFORE YOU DO ANYTHING! Since this is more complicated, I don't want emails saying "Pat, I messed up your very clear directions and now I can't feed my kids". That's not good. So make a copy before you do ANYTHING.
Again, because you are all amazing schedulers, I've combined everything into one step.
Step 1 - Open the Project, Export All of the Relationships in the Schedule, Identify the Activities Where You Want the Relationships to be Deleted, Copy Them Over to Excel, Run a VLOOKUP Function in Excel, Delete All Other Relationships, Put the "d" flag On All Remaining Activities, Clean Up the Excel File, Re-Import the Excel file.
I told you we can get this done in one step! But for those that want a little more detail, I'll break step one down a little further:
Step 1A - Open the Project
Right click the project and click "Open".
STEP 1B - EXPORT ALL RELATIONSHIPS
Once you have the project open, click "File" and then "Export".
STEP 1C - RUN THROUGH THE EXPORT WIZARD AS FOLLOWS
Select Spreadsheet - (XLSX) and click "Next"
Select "Activity Relationships" and Click "Next".
Make sure the "Export" checkbox is checked and click "Next"
Let's add a new template so click "Add".
When you create a new template they automatically provide a bunch of crap under the "Selected Options". We only need the following columns in our export. Feel free to rename the template as "Relationship Export" or something like that in the "Template Name" field at the top. Click "OK" when you're done setting this up.
It'll take you back to this screen and you'll want to select the template that was just created and click "Next"
Choose the folder location where you want to export this excel file to and click "Next".
Export Success!! Yay! Click "Close".
Step 1D - Prepping the Excel File for Re-import
Now, navigate to the location you chose for the export and open up the excel file. Once you open it up, it'll look something like this:
This sheet has all of the relationships that are in your schedule file. Note the (*) symbol for columns "D" and "E". This means that the values in those columns cannot be imported. So if we change them, it won't do anything to our activities upon import. I like to just get rid of them since we don't need them so go ahead and delete columns "D" and "E". Now, your spreadsheet should look something like this:
Let's add a new sheet at the bottom of our spreadsheet. This will be where we copy and paste the activities from our schedule file whose relationships we want to remove.
Go back to our P6 file and select all of the activities whose relationships you want to remove. Copy them. And paste them in the new excel sheet that you created (Sheet1).
Paste into Excel
Here's a quick overview of where we're going. To this point, we have exported all of the relationships from our schedule (Located in Sheet TASKPRED), and we have identified the activities whose relationships we want to delete and put that in Sheet1. So now, we need a way to lookup those same activities in Sheet1 with the relationships in the TASKPRED sheet. This is where VLOOKUP will help us.
Step 1E - VLOOKUP For Relationships We Want to Delete
If you don't know what the VLOOKUP function is in excel, it would be good to find an online tutorial to explain it as it's something we'll use a TON as master ninja schedulers. But here's a very quick description of how it works. If you have two lists of data, you can look up a cell from one of the lists and search the other list to see if it has that same data. If it does, you can return a different cell from the same row.
We'll walk through it so if you haven't used the function before, you'll still be able to finish the exercise. But I'd suggest getting more familiar with it if you haven't already.
Ok, back to our exercise. Let's clean up the sheet where we just pasted our activities from P6 (Sheet1). Delete all of the columns EXCEPT our activity ID column; which in this case, is Column A.
We'll use the "TRIM" function in excel to remove any spaces from our activity IDs.
Copy that same formula down:
Now, go back to the "TASKPRED" sheet where all of our relationships are. We're going to run our VLOOKUP function for both the predecessor IDs and the successor IDs to see which IDs from our master list of relationships also exist in Sheet1. Notice that I added two blank columns next to the Predecessor and Successor columns (Column B and D). This is where I'm going to run my VLOOKUP function. You can see in the snapshot below that I'm looking up the value in "A3" and searching the table in the other sheet (B2:B17). The $ means that I've locked the table in so that when I copy this formula down, it won't shift the cells for the table that I'm searching. The TRUE says that if the activity exists in the other table, I want that activity ID returned. If it doesn't exist, the FALSE statements will return #N/A.
Once I enter my formula, go ahead and copy it down for all of column B and then do the same thing in column D. Your sheet should now look like this:
We're almost there! We know that all of the activities where the VLOOKUP functions returned #N/A does not exist in our table from Sheet1. So, let's delete everything where BOTH the predecessor and the successor IDs have returned #N/A.
Notice the row highlighted in yellow. I kept this one because even though the predecessor returned #N/A, the successor returned an ID, which means that this is an external relationship that we want to delete when we run the import so let's keep that row.
So now, we've identified all of the relationships we want to delete in our schedule. We'll need to add the "d" record flag so that P6 can identify which relationships we want deleted when we run the import. Also, let's delete columns B and D since we only used those to identify the relationships and we don't want to include those on our import. So now, your excel file should look like this:
Take note of the "d" that has been added under column E. You need that because P6 will identify that you want those relationships deleted once you run the import. Before you save the excel file, go ahead and delete Sheet1 since we no longer need it.
Save the excel file and close it. Now, let's run the import to complete this exercise.
Step 1F - Import the Excel File
File + Import
Select Spreadsheet - (XLSX)
Browse and choose the file we want to import and click "Next"
Select "Activity Relationships" under Import Type. Click Next.
Make sure you select "Update Existing Project" under Import Action. Then double click the "Import To" cell and select the project where you want to import the excel file to. Click Next, then click Finish.
Go ahead and close this window and take a look at your beautiful new schedule!!! If you reschedule the file you'll notice that all of the relationships between and to the activities we identified under the "DELETE THESE RELATIONSHIPS" WBS were removed.
AND TO PUT ICING ON THE CAKE, WE DID IT ALL IN ONE STEP! Congrats and Happy Scheduling to You! Leave a comment and let me know how it worked out.