0

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?

Sheqqee
  • 3
  • 1
  • I find it impossible to follow your description. I **think** you're saying you have two relationships: `AuditFindings --< DocumentTypes` (one AuditFinding has many DocumentTypes) and `DocumentTypes --< DocumentNames` (one DocumentType has many DocumentNames). Is that correct? – michael.hor257k Feb 05 '15 at 22:51
  • Now, If you want to filter a relationship (or a portal based on this relationship - which is not the same thing) to be filtered, you need to explain what the filtering fields are and where can they be found. For example, to filter `DocumentTypes --< DocumentNames` by audit date: is there a date field in the DocumentNames table? -- Perhaps you should also explain in more detail what these entities represent in real life; I have the uneasy feeling that your basic approach may be flawed. – michael.hor257k Feb 05 '15 at 22:52
  • Michael, 2 rels are in place: AuditFindings --< DocumentTypes (link: the Standard) and DocumentTypes --< DocumentNames (link: DocType). Document names appear in portal on records of table (1), next to the type description supplied by (2). I.e.: Purchase invoice: SCV-2014-01-02.AR/FT The portals shown are the same on all Table-(1) records of the same standard (i.e. ISO9001). That is fine. But I now want to restrict DocumentName display to current date + current client + current standard. Tables (1) and (3) contains such fields for matching purposes. How can I filter the DocumentNames portal? – Sheqqee Feb 06 '15 at 08:55

1 Answers1

0

This is more of a guess than an answer. It is based on the following assumptions:

  1. Three tables, related as:
    AuditFindings --< DocumentTypes --< DocumentNames;

  2. There is a portal to DocumentNames, placed on a layout of AuditFindings, showing all the DocumentNames "grandchildren" of the currently viewed record in AuditFindings.;

  3. There is a ActiveDate field in AuditFindings table;

  4. 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
michael.hor257k
  • 113,275
  • 6
  • 33
  • 51
  • Thanks, that's what I suspected, but I had to establish a relationship from "AuditFindings" to a copy of "DocumentNames" to make it work (I had tried the other way round, and that didn't work.) You made my day! – Sheqqee Feb 06 '15 at 14:53