Calculated Field Summary via Relationship

[mav]

I was hoping someone could shed some light on my little issue.

I have two related tables from the job table, milestones + status.

Milestones can have multiple records depending on the job, although the value list defines the standard three entries.

Status generally has one record (but can have two) and has it's own value list.

I'm wanting to show Artwork Approval + Contact Delivery Milestones when in the Status screen. (I know I can see these values in the Jobs screen via the portals)

I have set up two fields (ArtworkApproval + ClientDelivery ) in milestones which populate if the MilestoneType meets a set value.

I have tried different methods of table occurrences, without any luck. The closest I have got is to create two more fields in the Milestones table which are the Summary (Average) of the ArtworkApproval + ClientDelivery fields, although I need to display the dates as they are, not as an average (these fields are not in the attached field).

I know I can use a portal to show all the Milestones for that Job in the Status screen, but I need to show just the two dates for these two fields.

I've attached my file showing where I am at.

I understand that the related fields in the Status Screen are only returning the first record entries from the related Milestones records, not the set values from the entries, as I wish.

Thanks in Advance.

Milestones.zip

[SlimJim]

Hi Mav

Can I suggest you do it with relationships as opposed to calculated fields. I have modified your sample to show you what I mean - you will see a couple of extra global text fields in Status which pick out particular milestones so that you can access the dates.

Milestones.zip

[mav]

Thanks SlimJim

I had tried using extra table occurances, but not by filtering through a global that had the set calculation for each of the milestone types . . .

I was actually wondering if I should incorporate the status into the milestone records, and add a field to define if a milestone is 'active' with a descritpion field, to not only get the same result, but to also allow for future adding of extra date type functions to the milestone records, and to allow for all date related records to appear in the one portal or report . . .

[SlimJim]

On the basis of the sample the status table bears a strong resemblance to the milestones table.

There are quesrions which need to be answered before you make any changes.

Whay did you have separate status and milestones tables in the first place? Is it because status represents a current situation wheras the milestones represent a history? It is not clear from the sample whether you will introduce more records for the status or simply change the status description and keep one record. (and change the date?)to show the latest status.

My guess, on incomplete information, would be that you should make the status into a milestone record. Secondly in the job layout when you display the milestones you might consider sorting by date descending so that the first portal row always shows the most recent milestone - this also has the advantage that if you need to introduce a scroll bar into the portal you will only have to scroll down to the history and not to read the latest.

If you want the status type records to stand out you could consider coloring the text via an auto-enter calculation.

I'm not sure what active means in this context so I'll refrain from comment.

[mav]

Sorry for the incomplete information, I'm trying to be brief with my posts, although the outcome can mean you only get a reduced explanation of the full situation . . .

My terminology can also be an issue, as I’m not 100% sure ‘Milestones’ may be the correct definition, as each Milestone entry is defined at the start of each job from a planning perspective, and then Tasks assigned to the 'Active' Milestone.

I've reworked my file to allow for the Milestone to have a Status of Complete, Active or Planned and there are now Tasks for each Milestone, which used to the Status records.

The Jobs window shows the top portal for all the Job's Milestones with the ‘Active’ Milestone’s background a darker colour to highlight it, and the second portal shows only the 'Active' Milestones and the Task for that Milestone. This second Portal will live on a layout that is filtered by who the Task is assigned to in the future. This is where I will use the method SlimJim has shown me to display the other main Milestone Dates in the ‘Active’ Milestone.

I’ve also added a ‘WeekView’, which filters the Milestones into each Date, and also highlights the Active Milestone with the darker background.

More future features will be to set the text colour of Milestone Dates have expired, or when a Milestone Task needs to be started, based on how long a Task is to take.

I’d be interested if anyone can comment on my relationship graph, and the method I have used to filter the Milestones into the date portals on the ‘WeekView’ layout, and the calculations I have used to set the Monday to Sunday globals to filter the portals.

Thanks again . . .

Jobs.zip