I have three tables:
(1) Audit Findings [up to 100 records per audit, depending on the standard],
(2) Types of Documents to check [different types and number, depending on the standard against which an audit is conducted], and
(3) Names of the documents that need to be present.
Records of (1), where the main audit findings are entered, contain a portal that lists the required documents. This list is provided by table (2). Next to this portal list, a "Document Type" relationship between (2) and (3) ensures that the correct document names appear after the relevant document types. Example: Shipping Notes: SN2234, SN8926; Sales Invoices: IV5673, IV7251, etc.
I now need to link the document names of table (3) to the audit at hand, i.e. table (1), to avoid that audit findings for any company always list the same ported document names. In other words, the 2 = 3 relationship needs to be filtered based on the audit date (clients are audited once a year), client number and standard info (most clients are multi-cetified) contained in table (1).
Is this possible? And how?
This is more of a guess than an answer. It is based on the following assumptions:
Three tables, related as:
AuditFindings --< DocumentTypes --< DocumentNames;
There is a portal to DocumentNames, placed on a layout of AuditFindings, showing all the DocumentNames "grandchildren" of the currently viewed record in AuditFindings.;
There is a ActiveDate field in AuditFindings table;
There is a DocumentDate field, in the DocumentNames table.
Now, in order to filter the portal mentioned in point #2 above, so that it shows only records of matching dates, set the portal filter to show records only when:
AuditFindings::ActiveDate = DocumentNames::DocumentDate