Searching for Requirements based on documents or vise versa

Mehrnaz Ahmadi Joobaneh

Hello 


I am trying to create a couple searches for dashboard parts unsuccessfully and was wondering if anyone has any suggestions. These are the searches:

• All requirements that have no external documents attached to them
• All external documents that are attached to a particular type of requirement (for instance FSR)

In advanced search, when choosing requirements, there don't seem to be any access to document fields and vise versa: no access to requirement fields from documents. I am not how the data is linked in database, but they know about each other somehow. They both seem to be using a lot of Activity fields. I wished I could peek into the database and see how the tables look like.

Does anyone has any suggestions?


Thank you


Mehrnaz

Justin Finighan

Hi Mehrnaz,


These are quite tricky queries as they require some understanding of how Activities are stored in BBGM. The Activities table in BBGM is used to store records of different Types and Classes, and BBGM presents these as Activities, Documents, Requirements etc to the user. Requirements are actually Activities where the Class ID is 3 or 4 - Grant Requirements or Payment Requirements. Documents are Activities where Class ID = 6, and External Documents are a sub-set of these. To distinguish between different Document types, we have to use the Activity Type ID. For External Documents, the Activity Type ID is 20.


When you have a Document attached to a Requirement, what you actually have is an Activity attached to another Activity. The Activity record has a column called Activity ID, and this is used to connect (in this example) the Document Activity to the Requirement Activity.


I am going to try to write a clearer explanation of all this one day, but for now, this is the search you need for your first query. The logic of this search (which must be run as a Requirements Search) is "Show me all the Requirements for which there is no related External Document Activity".


Activities.ID NOT IN (SELECT r.ID FROM Activities r INNER JOIN Activities ed ON r.ID = ed.Activity_ID AND ed.Type_ID = 20)


For your second query, you first need to find out the Type ID for your FSR Requirement. Start with a Filter Search for that Requirement Type, and change that to an Advanced Search. You will see that it will now look something like this: Activities.Type_ID = 5458 - this is the code for your selected Requirement Type.


Now you can do your Documents Search:

Activities.Type_ID = 20 AND Activities.Activity_ID IN (SELECT r.ID FROM Activities r WHERE Type_ID = 5458)


Let me know if these work out for you.


Cheers

Justin

Mehrnaz Ahmadi Joobaneh

Thank you Justin. This did the trick. It makes sense now. I was looking into Requirement_Ext instead.

Thanks

Mehrnaz