What is the best way to achieve the following:
(a) I have a data inventory form, and each record tells me whether a certain assurance activity should be undertaken by the data it holds. For instance the data says a special audit should be undertaken.
(b) I have a second form which details the special audits that have occured. As an audit can cover multiple data inventory records under (a) the ID's of the records from form (a) covered by the audit are recorded as a list on form (b) - call it b_in_scope_records
so a scenario is data inventory records 1,3,5,7 on (a) need a special audit.
on (b) special audit rec 1, contains ID (from (a) of 1,7,8,99
on (b) special audit rec 2 contains ID (from (a) of 2 5
I now need to show how many of (a) data inventory records are included in all special audits (b). So in the scenario above how can I tell ID 3 off(a) has not had a special audit ?
I have multiple records on (a) and I have multiple records on (b). Special audits (b) can be done many times on records on (a) and therefore the ID of (a) can be recorded on multiple (b) records (within the b_in_scope_records)
- It is straight forward to select off (a) all of the records that need a special audit
- collection_a = form_a[ID !=0 && criteria];
- i can collect records off (b) by
- collection_b = form_b[b_in_scope_records == collection_a];
and that gives me the (b) records that contain the ID of (a) within b_in_scope_records but it is included within collection_b if only one ID is found.
It does not tell me (and this is what I am trying to work out the best way to achieve) if there any of data inventory records from (a) that are not included in any special audits (b) (within the field b_in_scope_records)
What is the best way to achieve this?