Relationships

[Anonymous]

Heya All,

I cant figure out how to create correct relationships for the feature i'd like to create.

My current relationships look like this:

The tables All Items - Workitems, WorkItems in Progress and WorkItems Completed all have the same column called WorkItemID.

I'd like to make sure that when i click a WorkItemID from the All items - Workitems the WorkItems in Progress and Completed wil both be affected. And vice versa. 

This is because i created a page of a report based on the All Items - Workitems and a page with In Progress and Completed. And i'd like to be able to drill down from the In Progress/Completed page to the All Items - Workitems page.

When i try to just create the links between the 3 i get relationships errors and im unsure how to correctly fix this.

Thanks for taking the time to help me once again! 🙂

Milky

[Anonymous]

Hi there,

my recommendation is to create a 'reference table' that links to all the tables without having to try and join them individually.

This can be done with a number of reference tables if necessary, but I think you'll just need one; WorkItems


few ways to create, but the way I do it is in Edit Queries to duplicate one of the tables that has the most data (looks like All Items - Work Items) and remove all columns apart from your WorkItem variable. Then remove the relationships you have already and join them all to this table with a both ways relationship.

I hope this helps.

[Anonymous]

Hi, Thanks for your reply.

I just tried this but i can only make one of the 3 relationships go both ways, and i'd like to make all of themlike that. Do you maybe have a solution for this? Thankyou.

[Anonymous]

have you got other relationships between those tables?

to start with - remove all relationships then join to the reference table.

[Anonymous]

Yeh, if i do that, it might work. But i cant just remove all other relationships, my reports will break..

[Anonymous]

when you join them back up (using the reference table) the reports will work again.

[Anonymous]

Im not sure i understand correctly. Most of the relationships are not able to connect to workitemid

[Anonymous]

can you do another screenshot with how far you have got.

highlight the tables you want to join to the reference table and I'll have a look.

[Anonymous]

This is my current full relationship table

And these 3 i try to connect to the new WorkItemsIds.

keep in mind that for example WorkItems in Progress and WorkItems Completed are connected to Dates using InProgressDate and Completed Date. and they are both connected to Areas using AreaSK.
Areas is then connected to Projects with ProjectSK

Projects to Teams with ProjectSK

Projects to All Items - WorkItems with ProjectSK

All Items - Workitems to All items - Tasks with WorkItemId > Parent Work Item Id

And the All Items - Workitems to WorkItemsSort with State

Also, if this may help, i want this relationship because i have a bar graph showing the count of days a WorkItem is in progress. When i click one of the bars i'd like to drill down to page i created based on All Items - Workitems and automatically select the correct WorkitemID and show all info about that workitem.

[Anonymous]

Is this dashboard something you can share? or does it have secure data in it?

[Anonymous]

Unfortunately i cannot share the whole dashboard. 

[Anonymous]

thought so - but always worth asking.

quick thought - change the direction between Areas and projects, see if this enables you to complete your requirements.

Otherwise I need to try and draw it out.

[Anonymous]

Doesnt work. I am able to add ProjectSK to the new WorkItemIds.

I believe the biggest problem is the Dates. Because it has to be connected to both WorkItems Completend and WorkItems In Progress.

[Anonymous]

Besides that i tried the drilltrough im looking for with the WorkItems connected, but it still doesnt do what im looking for..