Find through 1-n relationships and display in column

[Eloi]

Hello everyone,

I have a problem I can't solve on my own.

I have the following relationships

- a one to many relationship between occurence "Job" and occurence "ContractType_1" ;

- a one to many relationship between occurence "Job" and occurence "ContractType_2" ;

I am trying to set up a layout to display all of the ContractType_1 & ContractType_2 that have not already been charged to the customer.

I have already succeeded in displaying these details but I had to display them on the same row as follows :

Job::ID #1 ContractType_1 ContractType_2

Job::ID #2 ContractType_1 ContractType_2

...

What I would like is to display them on one column as follows :

Job::ID #1 ContractType_1

Job::ID #1 ContractType_2

Job::ID #2 ContractType_1

Job::ID #2 ContractType_2

I hope the explanation of what I am trying to do is clear.

Thanks in advance for any suggestions and hints you may give me.

Eloi

[comment]

Since you are using version 11, why don't you use ONE relationship and three filtered portals?

[bruceR]

Any use of numbered fields like that (Contract_Type1, Contract_Type2, SomeField1, SomeField2) is quite often an indication of a data structure problem which then leads to the reporting problems you describe. Why are the contract types not in a join table?

[Eloi]

Greetings Comment and BruceR,

Thanks for your replies.

As for the data structure issue BruceR pointed out, I did make two different Tables for two very different kind of contracts.

For instance, I have a Job named "GMotors". That is to say, I'll make an advertising model available for a GMotors advertising.

I'll get a shooting contract for the worked days with Ogilvy (which is the Production company that will arrange the shooting) and one contract with the model.

This contract will be stored in a Table that will store as many lines as the contracts requires. If the shooting held on two days, i'll have two records in the Shooting_Contract_Line Table linked to the same Job ID in the Job Table.

I'll also get a buyout contract with TBWA (which will be the company managing the buyout) and one with the same model.

This often leads to several contract lines as there might be a lot of different countries in which the client wish to broadcast the advertising as well as a lot of different medias (TV, Internet, papers, etc). Each of these possibilities will be charged on its own.

So, my problem with making a big Table with all of these contract lines is there will necessarily be over 50% of the records that will be empty. It seemed to be too much space loss. Maybe I didn't get it right. If so, I welcome any help.

As of Comment's answer, I'm not sure I fully understand it. As I take it, I could display all ContractType_1 found set in one Portal, then all ContractType_2 found set in another.

My problem is that I would like to give the user the possibility to sort these results. For instance, I would like to display the invoice amount, invoice number, invoice Type (1 or 2) customer. The result would be displayed by number in first place but the user may display it by customer or Type of invoice or amount.

Thanks again for your time and help,

Eloi

[comment]

I am afraid I am more confused now than before. Why is it that all contracts cannot be in the same table, with a type field telling them apart? This part especially is not clear:

my problem with making a big Table with all of these contract lines is there will necessarily be over 50% of the records that will be empty

Why would there be ANY empty records? I didn't quite get the thing with contract lines, but if one contract has many lines (and one job has many contracts), then the obvious structure would be:

Jobs -< Contracts -< ContractLines

[Eloi]

You are right, the schema is Jobs -< Contracts -< ContractLines

Though the Shooting Contracts Table is in now way similar to the Buyout Contracts Table. Their respective fields are as follows :

Shooting Table

- Day worked

- Number of hour

- Type of shooting (ValueList : video, photo, catwalk, etc)

- Price charged

Buyout Table

- Date start

- Buyout length

- Countries

- Type of Media (ValueList : TV, Internet, Press, Flyers, etc)

- Price charged

I attach an image of the example at the bottom of the post (cause I don't know how to attach it in the body).

The result is that each record is more or less half empty. This is because I mix tow kinds of data that are not of the same kind.

Once again, having two separated tables seemed to me to be the way to rationalized the DB weight. This example is quite simple and I have slashed down half of my table fields because they are relevant here

I'm confused too as I really thought my schema was OK.

Thanks again for any help.

Eloi

[Eloi]

Greetings again,

Because I couldn't do what I want to, I have let this side of the developpement aside for the moment.

Now, I am trying to display all of the Contracts that are related to a specific month.

To make it trickier, a single Job may have several Contracts related to different months as for instance :

May would display :

JOB:ID#1 Contract_1

JOB:ID#2 Contract_1 Contract_2

June would display :

JOB:ID#1 Contract_2

JOB:ID#3 Contract_2

July would display :

JOB:ID#3 Contract_1

It now seems that joining both contracts in the same table including a Type fiedl would ease my struggle but I still am not sure.

Given my last two posts, do you still think it would be the good solution ?

Thanks in advance for your help.

Eloi

[comment]

If you want to summarize all contracts together (regardless of type), then you must keep them in the same table. That doesn't necessarily mean that ALL the contract fields need to be in that table (though they can, and often that is the most convenient solution).