Semi-Related relations

[LaRetta]

We need a report based upon the many side (Invoices) to the one side (Purchase Orders). But it is a bit more complex than that. Tables are:

Contracts:

ContractID

PurchaseOrders:

POID

ContractID

Invoices:

InvoiceID

POID

ContractID

Why do we have the ContractID in Invoices? Because sometimes invoices come through and we know which contract they belong to, but we haven't matched them to their specific PO yet. On the other side, we have Purchase Orders who have no invoices yet and THEY must reside in the report as well. Theoretically we should stand in the Invoices table for the report but to do so will mean we can't include POs without Invoices.

The report needs to be summarized by PO. It will be generated only on one Contract. So the final report should look like this (using the calculation PO instead of real PO to account for any invoices without POIDs)

PO00 (using calc currently in Ad Hoc)

Invoice 22 $25

Invoice 31 $30

Balance -55

PO45 $325

(no invoices)

Bal $325

PO47 $400

Invoice 11 $15

Balance 385

My plan is to pull in ALL invoices which belong to the Contract (done). Then I need to find any POs which have no Invoice children (for that same Contract). I decided I should be able to find which POs don't have children by twisted logic (something I usually excel in, ha ha) ... by determining ALL POs that exist (for that Contract) in Ad Hoc (by importing the invoices first) and THEN matching to POs (for that Contract) that aren't in that list.

Stipulations:

1) I do NOT want to put a calculation in POs (something like: IsEmpty ( Invoices::POID) ).

2) I cannot add unique validation to POID in Ad Hoc because that table is used for many reports where multiple POs are allowed in.

Possible?

UPDATE: I should mention that the table occurrence from Ad Hoc to POs is my weak attempt to filter out the POs which already exist in Ad Hoc. And of course it didn't work (I didn't think it would). But I'm unsure how to use List() and Filter() or ? to solve it.

AdHocReport.zip

Edited February 4, 2009 by Guest
Added Update

[LaRetta]

Ummmm, also, the cPO calc should be unchecked on 'Do not evaluate if all referenced fields are empty'.

UPDATE: Of course my plan was then to Import any POs without Invoices into Ad Hoc so I could complete my report. Sorry for this omission.

Edited February 4, 2009 by Guest
Added update

[comment]

Your sample data does not match the description: for example, Invoice #5 belongs to PO#3 AND to Contract#1 - but PO#3 is a child of Contract#3. Is this possible in the real situation?

[LaRetta]

No, I just dummied a bunch of numbers - too much in a hurry, sorry, Michael.

An Invoice can only belong to one PO. And one PO can only belong to one Contract.

Edited February 4, 2009 by Guest

[comment]

OK, so what exactly is the issue here? I would build the entire report in a global field, but I understand you prefer to import all of contract's invoices AND childless PO's into a reporting table. To find the childless PO's:

Go to Layout [ “POs” (POs) ]

Enter Find Mode [ ]

Set Field [ POs::ContractID; Get ( ScriptParameter ) ]

New Record/Request

Set Field [ Invoices::POID; "*" ]

Omit Record

Perform Find [ ]

[LaRetta]

Ya know, I never would have considered this combination of a find (two separate requests, omitting the second, which will act like a constrain) on the child table. Very nice indeed!

I would build the entire report in a global field

I would assume this is faster? There are more fields involved (which I will place on the report from the other tables) and there are actually two additional leading summaries (based upon Acct numbers of the Purchase Orders). And Invoices have no description (which needs to be pulled from the first line of their lineitem).

Still possible? Still faster? You have greatly aroused my curiosity although I do not expect assistance simply to satisfy my curiosity. But still ... I would be interested if you wished to expand on this a bit (or reference other posts in which you've taken advantage of this approach)!

Overall, I am tickled! Thank you soooooo much!!

LaRetta

[comment]

I don't know that it's necessarily faster (or slower). I just find it more convenient, because you can control the contents line by line (and thus can avoid the extra lines you will get under childless PO's using your method). OTOH, if there are more records than can fit a single layout, then there's more work to be done.

[teka]

This is an overly-complex structure, since you have what data modelers call two synonyms, that is two entities that appear similar.

Fundamentally you are assuming Invoice vs Purchase order from a certain point of view. This can cause problems, especially when you wish to handle multiple points of view. Isn't a Purchase Order from the buyer equivalent to the seller's Invoice?

Here is how this has been handled by others...

Remain neutral on the point of view, and have an overriding Super-entity type, Contract, which may be of many types, request for quote, price contract, order, purchase order, or invoice, to name a few.

A Contract entity then has a ContractRole join table to Party, where a party on a contract may be a ContractRoleType buyer or seller. (There are in fact other roles as well, but I am simplifying) In this way, a company can in fact submit and accept orders and invoices to itself, as well as others.

See models by David Hay, Graeme Simsion or Len Silverston. If you cannot find those, please message me and I will post an example.