Aghh - Relationships again

[Sari]

I've attached my latest (! version millionth) relationship diagram because I just don't seem to be able to get my head around the problems I seem to be having:

1. I want to run a status report based on all live jobs. I have a portal within the report to show all tasks allocated to employee within each job number. I can get my report to show the first task but it won't show any of the others.

2. I want to build Purchase orders based for job numbers. The Supplier will have a Company name related to the Company Code in the Company table and the location in the Company location table. So far, I can enter the Company Code and get the correct Company Name. But when I try to enter the company location based on a value list with conditional values, I get another companies details.

I've got to do the quotes and invoices yet but I can't seem to get past these 2 problem. I didn't have any trouble in Access....

TattooRelationship2301.pdf

[Sari]

Ha! Spent a few more hours fiddling, giving the rest of the family stick, explaining what I want to do (they all could do it in Access) and whilst I was chatting on the phone, fiddling absent mindedly I managed to get the Purchase Orders problem solved. However, I haven't cracked the Status Report yet so any suggestions gratefully received.

[RalphL]

From the look of the graph, I see a many to many relationship between tbl_job_bag and tbl_employee with tbl_time_entry_line as the join table. A portal in tbl_employee to tbl_time_entry_line should be able to see tbl_job_bag fields.

It also looks like you have a many to many relationship between tbl_company and tbl_company_location with tbl_contact as the join table. You also show a one to many relationship between tbl_C ompany and tbl_company_location. My guess is this should one to many relationship between tbl_Company and tbl_company_location and relationship between tbl_company_location and tbl_contact.

Try breaking your graph into TOG's, i.e., groups based on a layout. The graph is not an E-R diagram.

[Sari]

Wow, I wanna be advanced too! That last paragraph was so helpful; there was a blinding flash of light and I suddenly understood where I was going so wrong. I've really struggled to get my head around TOG's so many thanks.

I've managed to get most of what I want to do working but I'm still having trouble getting my Status to Report to run. I've set the relationships (turquoise) as per the attached document and I have set my layout to be based upon records from tbl_job_bag 3 with the portal having its records based on tbl_task_allocation. However, I only get one task allocated to each job rather than many jobs. The strange thing is that I set up a different layout on the same basis but as a form and it works perfectly!

Unfortunately, I need to run the report in the most concise manner I can.

[Sari]

Oop's here's the attachment!

[Ugo DI LUCA]

Try again, no attachment here...

[RalphL]

Where? I am trying to help.

[Sari]

Sorry, I don't know what happened here. I really do appreciate your time and effort.

TTMRelationships2501.pdf

[Sari]

Sorry, I don't know what happened and I'd gone to bed to dream of Filemaker! I really do appreciate your time and assistance.

TTMRelationships2501.pdf

[RalphL]

See the attached. Is this what you want?

[Sari]

Its a bit more complex than that. I have to list all the current jobs by company and by job number. Then within each job I have to list all the tasks relating to that job by each employee. So what I have at the moment is a report with the Company Name in the sub-summary and a record within the body which gives Job No, Status, tasks etc. I have a portal showing the task brief, the employee code which denotes who has been allocated that task and the date the tasks should be comleted by. Having fiddled a bit, I can now get the list of tasks but I can only get the employee code to come up by the first task and not the others.

I thought it might be that one employee had lots of tasks within one job but when I investigated the data further that isn't it.

[Sari]

I've found it! Such a stupid little thing - the field was right on the edge of the portal so was only putting in one entry. Duh

Many thanks to you all for your time.