Self Report Troubles

[Dr. Evil]

My goal is to use a single table and build it so I can sort and report it by Assignment and Product.

There would only be parent table and child table. Parent being Project and child being Item. The challenge is Items can be labeled either as "Assignment" or a "Product" and a "Product" needs to be related to an "Assignment". Confused yet?

I'm having problems making the correct relationships and sub-summary sort set up.

Its hurting my brain!

Here is a outline of how I wish the report to look:

=======================================

PROJECT (parent)

=======================================

__Assignment One (child)

____One Product 1 (child related to Assignment One)

____One Product 2

____One Product 3

__Assignment Two

____Two Product 1

____Two Product 2

__Assignment Three

____Three Product 1

____Three Product 1

=======================================

Let me know if I need to clear anything up.

THANKS FOR ANY HELP!

[comment]

You can produce the above report by finding the items labeled as "Product" and sub-summarizing them by Project and by Assignment. This is assuming that each Product has a "parent" Assignment related to it by a self-join.

But why wouldn't your table structure follow the same hierarchy as the report, i.e.

Projects -< Assignments -< Products

[Dr. Evil]

More info...

I currently have three tables that work together.

Project -> Assignment -> Product

I have successfully made these work great, reporting is good and everything.

Problem is there is NOT always a product, sometimes products are not necessary. BUT sense Products are the last table in hierarchy I have to set up my reports in the product table.

I have worked around this, so user does not see a NULL product. But I don't like the fact that I have allllll these null products just for the sake of reporting. Maybe I just must deal with this?

But my idea was to some how combine the two, Assignments and Products. Set up the relationships somehow and sort it so it look like what I have set up with the original hierarchy.

I started to build a model on this to test out some ideas, but ran into the same problem of needing to use the product table occurrence to report from even tho its the same table.

Does this make more sense then first attempt to explain?

NO null products for sake of reporting is the goal.

THANKS A HEAP!

[comment]

I'm afraid I don't quite follow. What do you mean by "null product"?

The only issue I can see here is that projects and assignments that have no products will not be shown in the report. If this is the problem, I don't see how it can be helped by forcing assignments and products into the same table. Having a dummy product record for each assignment is IMHO a cure worse than the disease.

OTOH, there really are no GOOD solutions for this (if this is indeed your issue). The best workaround would probably be to "compile" the product lines into a calculation field in Assignments, and then do the same for assignments in the Projects table - a "calculated portal", if you will. Then you can run your report from Projects. I may have posted a demo of this at some time, but I don't remember where.

[Dr. Evil]

Cool, if you recall where your demo of this is I would be excited to check it out.

So I will keep the Product table and not try to combine with Assignment. I can hide the products that are null from user. I may be using term incorrectly... but I refer to a null Product that exist but contains no tangible data other then for reporting purposes.

Thanks again for your help and I encourage ya to post any other thoughts you may have.

[comment]

Making the dummy records transparent to the user is not difficult - but they will skew any statistics you might have.

[Dr. Evil]

Is there a way to keep a user from deleting a record if it is the only one with specific relationship?

It is ok for user to delete a product record. But NOT if it is the only one belonging to related Assignment. How can I control this. I can see it now, user will delete it, and report will not work, and they will not know why. I have it set up so report script checks relationship and if it is yes then it continue, if it is no then it give a message why. But u know general users, they will still not get it.

[comment]

You can limit user's privileges to delete records by a calculation, and supplement this by allowing scripted deletes only.

[Dr. Evil]

You can limit user's privileges to delete records by a calculation, and supplement this by allowing scripted deletes only.

Can you go into a little detail on how to set this up please?

What would the calculation look like?

Thank you for your time!

[comment]

See if this helps:

Min1Child.fp7.zip

[Dr. Evil]

Awe, real cool. I do not know a whole lot about accounts and privileges, and don't really know the full power it has.

This tip is a great jump start to learning accounts and privileges.

Thanks, this helps a heap!