Problem w/ Dynamic Sorts using a summary field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

icedgar - 26 Aug 2008 19:34 GMT

I was wondering if anyone knew how to do this in FileMaker Pro 9.

Basically I am using 2 tables, Engineer and Task, one to many from
engineer to task.

I created a self join from Task to Task by Engineer_ID_kf.

I have a field that calculates the reaming days for a task to be
completed called 'Remaining_Days'. Then I have a Summary field that
Totals the days by Engineer and breaks by Engineer_ID_kf. I use that
number to calculate the Estimated_Completion_Date of all the Tasks for
that Engineer. The start day is today and if a completion date ends
on a weekend, the calc pushes it to Monday. The output looks like the
following:

Engineer_ID Remining_Days Estimated_Completion_Date

1 2 8/29/2008
1 5 9/5/2008
1 2 9/8/2008
1 12 9/15/2008

This works just great when sorted by Enginner_ID and if I set the
relationship sort to 'Reorder based on summary field' and choose that
summary field based on Engineer_ID, I can have multiple Engineers in a
portal list with the dates calculated correctly. Example:

Engineer_ID Remining_Days Estimated_Completion_Date

1 2 8/29/2008
1 5 9/5/2008
1 2 9/8/2008
1 12 9/15/2008
2 4 9/19/2008
2 5 9/26/2008
2 4 10/2/2008

What I need to be able to do is sort this portal by 'Task_Priority'
and for it to keep the Estimated_Completion_Date intact for each
Engineer. When I resort by anything else but Engineer_ID the summary
field just sums up all the engineers together as one Task, which makes
sense. I would like the data to be able to sort by another field and
keep the estimated_Completion_Date in tack for each engineer. If I
added a task_priority to the mix it should look like this below:

Engineer_ID Task_Priority Remining_Days
Estimated_Completion_Date

2 1
4 9/19/2008
1 2
2 8/29/2008
1 3
5 9/5/2008
2 4
4 10/2/2008
1 5
2 9/8/2008
2 6
5 9/26/2008
1 7
12 9/15/2008

It would be nice to be able to change the Remaing_Days as well and
have the database recalculate the estimated_completion_date
dynamically. I am not sure if I need a script for this or to
calculate these dates independently and store them as a simple date.
I have tried to use the Calculate Value in the field setup to place
the date in there, but it was not working properly.

Does anyone have any ideas of how I might accomplish this.

Thank you in advance.

Reply to this Message

Helpful Harry - 27 Aug 2008 01:48 GMT

In article
<e9ae7e10-3b95-416f-bfa1-4aa8f1ab04a5@i20g2000prf.googlegroups.com>,

> I was wondering if anyone knew how to do this in FileMaker Pro 9.
>
[quoted text clipped - 69 lines]
>
> Thank you in advance.

It's not clear whether you are doing printouts or on-screen displays,
but Summary fields should never be used in calculations or sorting.
With occasional exceptions (using the GetSummary function), Summary
fields are only for use in Preview mode or on printouts, otherwise you
will get strange results. Similarly, Portals should never be used for
printing. They are only really designed for on-screen use in Browse
mode.

If you are printing out reports (or viewing them in Preview mode), then
when you think you need a Portal it is almost always a sign that you
should be printing from the other Table.

For on-screen use you can use normal Calculation fields and functions
in the main Table to summarise the Portal Table's data via the
Relationship itself. For example, to total the number of days remaining
you can use something like:

Total_Remaining_Days Calculation, Number Result, Unstored
= Sum(Relationship::Remaining_Days)

If you want to re-sort the Portal by a different field, then
technically it is possible, but can lead to all sorts of problems. It's
often better to simply define multiple Relationships that are sorted
differently.
eg.
TasksByEngineer sorted by Engineer_ID_kf

TasksByDate sorted by Task_Date

and multiple sets of summarising Calculation fields.
eg.
Total_Remaining_Days_ByEngineer
Calculation, Number Result, Unstored
= Sum(TaksByEngineer::Remaining_Days)

Total_Remaining_Days_ByDate
Calculation, Number Result, Unstored
= Sum(TaksByDate::Remaining_Days)

You can then have separate Layouts to print or view the data. For
on-screen use you can have a small button (or set the columns titles
above the Portal as buttons) to change to another Layout with a
differntly sorted Portal and totals.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Reply to this Message

icedgar - 27 Aug 2008 19:24 GMT

On Aug 26, 5:49 pm, Helpful Harry <helpful_ha...@nom.de.plume.com>
wrote:

> In article
> <e9ae7e10-3b95-416f-bfa1-4aa8f1ab0...@i20g2000prf.googlegroups.com>,
[quoted text clipped - 119 lines]
> Helpful Harry
> Hopefully helping harassed humans happily handle handiwork hardships ;o)

Harry,

Thank you for that information. You are correct, I could do it that
way which would be better but it is not what I am looking for.

To clarify, I want to view this data in a portal for data entry only.
I don't need to print it yet thus am not using Preview mode. Also, I
am trying to accomplish a Running Summary per Engineer. The solution
you have above does a Total Summary for each. The reason I need a
Running summary is to calculate the next Estimated_ Completion_Date
based on the Remaind_Days of the task before it. If it is the first
task for the Engineer, the current date is used as a start date,
unless once is specified. That is why I am using the Summary Function
because it will create a Running Summary by break field. So I get
this by Engineer:

Engineer_ID Remining_Days Estimated_Completion_Date

1 2 8/29/2008
1 5 9/5/2008
1 2 9/8/2008
1 12 9/15/2008

Instead of this when I use a calculation field with just
Sum(TaksByEngineer::Remaining_Days)

Engineer_ID Remining_Days Estimated_Completion_Date

1 12 9/15/2008
1 12 9/15/2008
1 12 9/15/2008
1 12 9/15/2008

Do you know of any other techniques to create a running summary
besides using the function?

Thank you.

Reply to this Message

icedgar - 27 Aug 2008 22:53 GMT

> On Aug 26, 5:49 pm, Helpful Harry <helpful_ha...@nom.de.plume.com>
> wrote:
[quoted text clipped - 160 lines]
>
> Thank you.

I guess I am simply looking for a way to get running totals when
dealing with a non-sorted portal. Anyone have any ideas?

Thank you.

Reply to this Message

Helpful Harry - 28 Aug 2008 01:36 GMT

In article
<4d4848f4-3657-4dfd-a4a9-a0a472c34147@l33g2000pri.googlegroups.com>,

> Harry,
>
[quoted text clipped - 33 lines]
>
> Thank you.

Sorry, I incorrectly thought you were trying to obtain a viewable total
outside of the Portal (ie. TOTAL of remaining days and OVERALL
completion date).

You can not use Summary fields for what you are trying to do. Summary
fields only work properly in Previewed or printed reports - they are
designed to work in Summary Parts of a Layout.

The problem here is that FileMaker is not a spreadsheet - it can't
easily grab values from "previous" records, especially when the records
are changing the sort order and grouping.

It also makes no real sense to have a Portal on one Engineer's record
displaying Tasks of other Engineers. This gives a big hint that you are
using the wrong Table and should be using the Tasks Table itself where
you can Find and display any grouping and sorting of records required.

To obtain a "running summary", you really need to give each NEW Tasks
record a copy of the "previous" (in the appropriate sub-group order)
record's Completion Date so that you can then add its own Remaining
Days value to that. This means using a new "ID" field for each record
and a Relationship that can look back to the previous record within its
sub-grouping ...

BUT,
such a system will get very messy because you want to change which
records are displayed / left out and the Sort Order. This would require
running a Script to renumber the records' "ID" field appropriately so
that they see the correct "previous" record in the new grouping / sort
order, and then the database would have to re-calculate all the fields.

You could use multiple "ID" fields and Relationships so that each
grouping / sort order, which would then need multiple Layouts to
display the different groupings / sort orders.

Depending on the number of records being processed and the speed of
your computer, doing all this "on the fly" for constant on-screen
display could be very slow.

Realistically, to obtain a "running summary" where you can sort records
in various orders and various groupings, it is MUCH MUCH easier to
simply use Summary Fields and Preview / print an appropriate report
Layout using the Tasks Table.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Reply to this Message

icedgar - 28 Aug 2008 18:15 GMT

On Aug 27, 5:37 pm, Helpful Harry <helpful_ha...@nom.de.plume.com>
wrote:

> In article
> <4d4848f4-3657-4dfd-a4a9-a0a472c34...@l33g2000pri.googlegroups.com>,
[quoted text clipped - 83 lines]
> Helpful Harry
> Hopefully helping harassed humans happily handle handiwork hardships ;)

Harry,

Thank you so much for your great response. After your help yesterday,
I realized the same think you just posted. What I have realized is
that I will have to create another numeric Remaind_Days holding field
to store the calculated days. My logic now is that I created a layout
in the Tasks table sorted by Employee_ID and then Priority, execute a
script that will Replace all the data in the holding field and then
use that field to calculate the remaining days. Then I can sort all
the records any way I choose. The only issue is that I will have to
put a script on the main page to check for the Max Modification Date
for the important fields. At least then they would know that a
refresh is needed and run the script to recalc those dates based on
the new Remaining_Days.

Thank you for all your help. Much appreciated.

Reply to this Message