Design Table for WBS

Hi Rafael -

I am unsure where we are right now.

Generally, when moving from Excel (a flat spreadsheet) to a relational database it is good to try to forget how Excel does it and allow your mind to learn a new way of doing things.

I am having trouble wrapping my head around your workflow. Is the DB you uploaded setup like you described on your March 26 9:13am post? If not, can you upload a copy that has the data setup in a way that I can follow your question.

Hi Rafael

Ok, well it looks to me (maybe someone else who is following along can come up with a better solution), that you don't track enough data to be able to figure out work done by week.

1) I think you want to in Curva Semanal to be able to sum the Horas Ganadas (Plan and Real) for all records in Documentos by Semana.

2) Let us start with the Horas Ganadas Plan. Since this number is set at the beginning of the project and the project is 8 weeks, it seems like to me that you would need to have either:

a) 8 Ganado Plan fields, one for each week or

b) you can create a plan subtable that tracks this data (useful if documents can have different number of planned milestones (hito)) . It looks like you have already thought of this route as I see a subtable called GanadaPlan.

Looking at GanadaPlan, you don't need Flecha Corte here. This is just raw data of when you plan to have certain horas completed. You still point Semana back to datadate from Flecha Corte. I see you have Cutoff Date, so Semana should be point to Cutoff Date not datadate.

3) Now to Horas Ganada Real, I still think you need to use a subtable that tracks the actual hours spent by date. Then you can find the weeks that match and sum the hours. So you have actual hours.

Once we figure this out we can talk about dashboards and move the Totales tab out. For me it doesn't make sense to have a totals page in the Documentos table since it appears on every record, but no rush on this since it works for you.

Rafael Sanchis said:
I send you again the DB, I create a new table avances with the 8 weeks the field % Acumulado Plan I take now manually from Table Documentos, Form Avances, field Avance Plan I cut off every friday and place the value manually  that is the problem I require automatic because is small project is large project is a problem.

You are correct, you are creating more work for yourself. You need to create tables that capture the raw data and then you can manipulate it to fit your needs.

In the Disciplinas table, are the horas hombre numbers the planned horas hombre for the project by discipline? So all electricidad documents are to equal 1,116 horas and so on?

I have changed the ganadasplan table to ganadasreal and entered in some data to show how it could work. I've updated the avances.horas ganadas reales field to figure out the horas spent on the project.

Does this work for you?

if you start tracking horas in ganadasReal then you can

1) create a new formula field in the Disciplinas table:

sum(Actividades.Documentos.GanadasReal.horas)

This will find all related records in Documentos to the record in Disciplinas and add up all horas related to those documents.

2) create a new formula field in Avances table:

let t := this;
sum((select GanadasReal)[Semana <= t.Semana].horas)

This will find all records in the GanadasReal table that match the same week then add up all the horas spent during that week and before.

I think you have to rethink how you use FlechaCorte.datadate. It sounds like to me it should be a dashboard page. You want to know what the status of your project is at a certain date. So you do to NOT need copy this data into your raw data but use it as a comparison.

What I mean is that you want to know how many horas are planned and consumed by a certain date. That means you have to have raw date data that you can manipulate. If you look at the example above for the Avances table you can now see the totals of horas per week.

Does this make sense?

Rafael Sanchis said:
I have 3 kind of hours 
The hours plan,  each hours is related with FechasPlan and Milestones, what the document need earned at the DataDate
The Hours Real, each hours is related with FechasReal and Milestones what the document earned at DataDate
Consumed Hours (Horas Consumidas) The people need to finish the Document. Hear need a table with Documents, People(s) Responsable of the finish Document, and DataDate I can have a maybe 3 o 4 people for each Discipline depend the project and hours 

Sorry, learning spanish as I go. :) Yes, ganadasreal should be horas comsumidas. To make is simpler you can just call it horas.

I think you only have two types of raw data horas planned and horas cosumidas. Your horas real is just a formula of horas consumidas.

I see you have planned horas by Disciplinas. I see in Documentos you have a field Horas Hombre. Is this supposed to be the total horas planned for the individual document? I don't think it can do both jobs of tracking horas planned and real.