SOLVED

Project Site Email Addresses

%3CLINGO-SUB%20id%3D%22lingo-sub-3068468%22%20slang%3D%22en-US%22%3EProject%20Site%20Email%20Addresses%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3068468%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20confirm%20where%20on%20the%20SQL%20db%20that%20sits%20beneath%20PWA%20are%20the%20project%20site%20emails%20stored%3F%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20are%20looking%20for%20a%20list%20of%20Projects%20%2F%20ProjectUIDs%20and%20the%20Project%20Site%20email%20address%20that%20can%20be%20queried%20in%20T-SQL.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

 

Can anyone confirm where on the SQL db that sits beneath PWA are the project site emails stored? 

We are looking for a list of Projects / ProjectUIDs and the Project Site email address that can be queried in T-SQL.

 

Thanks in advance.

6 Replies
best response confirmed by APS1979 (Occasional Contributor)
Solution

Hello @APS1979 ,

A list of Projects / Project IDs can be found in the Reporting schema in the PWA database. The email address for SharePoint site mailboxes will be in the SP content DB but that is not supported to query that schema directly with T-SQL, you will need to use the SP APIs but I'm not sure what the API method is that returns the site mailbox details - if there is one.

Paul

Hi Paul,

is it possible to create a project custom field called e.g. "Project Site Email Address" where it's formula based and concatenates an existing custom field that holds the Project Code field, and adds it to to a set suffix?

e.g. =[Project Code]+"@globalemailaddress.com"

that way the field would then return a full email address? (I'm aware I;m very much over simplifying the formula somewhat, but you know what I'm getting at :) )

Hello @APS1979,

This wont be possible using the out of the box Enterprise custom fields aa they are only aware of Project related fields / data. The SharePoint data is separate unfortunately. 

Paul

Sorry Paul, I perhaps didn't phrase the question well enough; i'm not talking about the SharePoint data itself, I'm talking about creating a new custom field to effectively fake the email address per project without having to have any manual entry or duplicating of information in fields.  Our email addresses are always the project code prefix.

We already have a custom field called 'Project Code', and was wondering if a new custom field could have a formula to look at the Project Code value and add a suffix (in this case the global email letters) to provide what will in effect be an email address value in total?

Project Code field = AB-1234

New Custom field formula = [Project Code]+"@myworkemail.com"

New Custom field value would then be AB-1234 @ myworkemail . com

 

(Separated characters due to forum enforcing removal of any address)

Hello @APS1979 ,

You could do that:

PaulMather_0-1643293048074.png

Paul

you beautiful man. Thanks Paul!