Complete tables by filling null values in tables

[hhajjali]

Hi everyone,

I am trying to complete a project but I have a problem that I need your valuable help to solve.

Let me explain what I am trying to do: I am trying to get cost spent per project for the organization. In my data model I have projects that contains tasks and bugs assigned to team member and they are filling their time spent.

Examples in green and red in the image below, those are straight forward cases, a project have tasks assignee and time.

But I have cases where the team is putting time on tasks that they do not have projects, I have 3 categories:

- Blue: Type is a bug so I need to link it to a Project Bug instead of Null

- Violet: Task done by only Assignee 3 so I need to link it to a Project Support instead of Null

- Yellow: Anything else to a project Other

My logic is as follow:  If project is Null --> If Type = Bug set project - Bug --> Elseif Assignee - Assignee 3 set project - Support  --> Else set project Others.

My Data model is as follow: 

Project table: Project Key and Task Key

Task Table : Task Key and Task Type

Time Table: Task Key, Assignee, and Time

How can update or add to the project table new entries to get a complete picture ? 

Thanks for help

Haidar

[andoryuu]

You can accomplish this a variety of ways  - here are two suggestions:

1. Use concatenate statements to add values for  your projects so that the join resolves. 

2. Use outer joins on your tables and then create a new table at the end that uses IF(Type='Bug','Bug',LEN(Project)=0..... yadda, yadda, yadda to fill in the values that you want as specified in your logic below.  

If you post the QVF of your app I can help give you a better idea of what to do here.