Searching for Requirements based on documents or vise versa

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