Help - Report on Child? Intersect?

[Nello Panelli]

I have reports for sets that work well, but today I had need to have a report of ALL the transaction based on a filter. I want to filter for purchasers of Widgets and also see wha else thay bought.
Here is what I want!

Name address
01/01/1999 2 widgets $20
02/03/1999 4 Cats $40
03/01/2000 1 widget $10
04/02/2000 2 Cats 20
01/03/2001 4 Mice $5

What I get in the report is only the Widgets. ( I know the filter is asking Widget so that is what I get.) How do I create a report, or filter, or seires of operations, that gets me a list of everything.
Thanks for any suggestions.
Nello

[Tom Cone Jr]

RE: Help - Report on Child? Intersect?

There are probably several ways of doing this.

I would consider creating an inverted set. Put the purchases table in the parent slot, and the customer table in the child slot, linked one to one.

Group report on customer id found in the parent table.

Put the name and address in the group header.

Put the purchases in the details band of the group.

Order records based on customer id + purchase date.

this ought to get you going... if you need to subtotal like items you may need to use a new calc/summary field...

-- tom

[Nello Panelli]

RE: Help - Report on Child? Intersect?


Thanks for the reply. The report layout you descibe is what I have. If I run the report on the IdNum or Customer name I get the report I want. But when I filter on Widgets all that reports is the widgets they bought. What I want is to filter on widgets and get a report of All PURCHASES for anyone who bought Widgets.
What am i missing?
Nello

[whanigsberg]

RE: Help - Report on Child? Intersect?

Nello:

What are your tables and how do you have them assembled into sets?

Bill

[Nello Panelli]

RE: Help - Report on Child? Intersect?

The tables are Customer, Transactions. I have a set CustomerTransaction and a set TransactionCustomer. I have run reports in both directions. When I filter for Widget I get purchases of widgets.

I do not feel there is a problem with the report structure or the set. The result I am getting is normal and logical. I just can't figure out how to get what I want, which is a list of ALL THE PURCHASES of everyone who bought Widgets. In other words if they bought widgets what else did they buy.

Is this an OPERATION procedure?
Thanks
Nello

[Tom Cone Jr]

RE: Help - Report on Child? Intersect?

Sorry, your goal was not understood from your original question.

I'm still not sure I understand what you are striving for, but while I'm often wrong, I'm never in doubt!

If you want a report that lists only customers who bought widgets, but shows all their purchases (not just widgets), consider changing the filter in the report to 'marked' customers.

Then,
1) unmark all customers
2) mark just customers who bought widgets
3) run the report

[Nello Panelli]

RE: Help - Report on Child? Intersect?

Tom,
There is no doubt, you decoded my message correctly! That is exactly what I want.

Now, Unravel this for me, if you will.

How do I mark the customer record of a set when filtering on the transaction data?

I am baffled! I've made several attempts.
Starting from the inverted set I "mark" the records using the "widget" filter. If I run a reoport on "marked" I only get the Widgets customers with out other purchases.

So I think to myself,"do something" to mark the parent record with corresponding IDNUMBER. Then run the report on the marked records. BUT I do not now how to "do Something" -- that is mark the parents.
Any suggestions are appreciated.
Nello

[Tom Cone Jr]

RE: Help - Report on Child? Intersect?

Nello, the attached database illustrates two ways to solve the puzzle.

The database has two tables, customers and purchases. It's based on a hypothetical sports shop, where customers buy various kinds of sporting goods.

Customers are linked to purchases by their id number.

The global script, MarkBatsCustomers shows one approach to marking just those customers who bought bats.

The operation 'MarkBatsCustomers' does the same thing, but would require you to un the operation 'UnmarkAllCustomers' first.

In each case the customers who bought bats are found by using the exist() method. The trick is to figure out how to identify both customers and their products. I solved it by using a compound index key, in the Purchases table, based on their id and the product.

I think this will be directly applicable to your situation.

[Tom Cone Jr]

RE: Help - Report on Child? Intersect?

Nello, here's a revised version, which now includes a more general script, 'MarkCustomersByProduct'.

Ui_get_text() is used to get product name from user.
Product Name is stored in variable.
Variable is used to find and mark Customers.

This will be of more general use to you, I would imagine.

-- tom

[Tom Cone Jr]

RE: Help - Report on Child? Intersect?

Oops. Too quick on the Send Message. Here's the attachment with the revised version.

[whanigsberg]

RE: Help - Report on Child? Intersect?

Hi Tom,

I just worked through your sample. I must say it's a useful one. It really shows what you can do if you think about how to index a child table. And of course it defines another approach to sorting a set's parent records on the properties of the child records (in which you know I have an interest).

So we all owe you for this one.


And.....you wrote:
"I'm often wrong, I'm never in doubt!"

Why didn't you look in on me when you visited Dawson College. You've obviouly met a few of my colleagues.

All the best,
Bill

[JohnZaleski]

RE: Help - Report on Child? Intersect?

Tom's excellent solution also gives a hint at something else. In a relational database one could argue that the customer ID should only be on an invoice header. In theory this is true, however, disk space is cheap, and throwing a customer ID on a line item which also has the item information, makes a number of things much easier later on. Another way to do what Tom did would be to create a saved summary operation that asks for item (bat etc) and summarizes the line items by cust id. A set linking the line items "one to one" to the summarized table could be created and the report would be filtered on something like SummaryCustomer->id's that are not blank.

[whanigsberg]

RE: Help - Report on Child? Intersect?

John:

I take your point about normalization (and cheap storage) but in Nello's specific case isn't customer_id necessary in the items table as a linking key?

Or have I once again missed something?

Bill

[JohnZaleski]

RE: Help - Report on Child? Intersect?

Bill,
You did not miss anything. I was basing my thoughts on the traditional layout of an invoicing program. Invoice Header table with an invoice # and customer ID which is usually linked "one to one" to a customer table. Invoice line items are usually linked "one to many" to the invoice header via an invoice number. Normalization would suggest carrying the customer ID on the header only and not on the line items. My thinking was that certain Alpha functions (indexing for one) can only be done on fields from a single table , and that carrying information in several places can sometimes make future tasks less complicated. An example would be a browse for all invoice line item sales history with a "find by key" for customers. You could certainly make a set linking the line items "one to one" to the invoice header and do a query on the customer ID residing there, but a "find by key" would probably be considerably quicker.
John

[whanigsberg]

RE: Help - Report on Child? Intersect?

John,

I agree with everything that you say.

In our MIS department we have some very experienced people who manage our student records system. They sometimes overload a file with extra field(s) containing concatenated key values which they index. They do this to increase the speed of searches and sorts performed on this enormous archive.

It's not that they do not understand normalization and the reasons for it. They just optimize a few processes they have to run all the time. I try to learn by watching them.

Thanks for sharing your knowledge.

Bill