Portal Calculation Field & Dates

[Kingme]

I'm a fairly intermediate filemaker user but for some reason I cannot figure this "simple" problem out:

I have 2 Files: (Week) (Days)

The "Week" file has two fields "StartDate" & "EndDate" --- the "Days" file has two fields "Date" and "Amount".

The "Week" file creates records in the "Days" file for however many days are in between "StartDate" & "EndDate" and assigns the "Date" field in the "Days" file to the corresponding date. (I can explain why I do it this way, but it's a long one)

Anyways I'm trying to create a calcuation field in the "Week" file that takes the "Sum" the "Amount" field of 3 days (Friday, Saturday, Sunday) from the "Days" file.

Unfortunately the Start and End Dates are different for every record, so I can't just take the same portal record (i.e. 3,4,5) and sum it up.

This seems so basic, yet I'm stumped.

[comment]

What is your question? Are you asking how to sum 3 days over a single relationship, or how to determine the date of Friday?

[Kingme]

Sorry If I wasn't clear.

I have a record in the "Weeks" file that has 7 related "date fields" in the "Days" file. Each related record in the "Days" file has another field called "Amount".

I need a calculation field in the "Weeks" file to "figure" out which of the 7 related date fields are "Friday, Saturday, & Sunday", then take the number from the "amount" field and add them together.

I've attached a sample. (Open Weeks file first)

[Søren Dyhr]

Although this approach the issue differently ...does it however contain the basic ideas of how to make such a relation work, investigate the uploaded template.

--sd

SumWeekends.zip

[Søren Dyhr]

I've looked at your upload, and dates correspond to week in a way that hardly justify two files/tables but a more strictly coherance than your structure suggests.

One thing I forgot to make in my previous upload was the range you wished for, somthing you can't get inspired to do right away by investigating it. This mean you need to make a slight change in the file. So that you only sum the day which are in the 3 weekdays. This can be done by summing this calc's result instead of all the values in the portal:

Case ( Position ( "671" ; DayOfWeek ( theDate ); 1 ; 1 ) ; theValue)

--sd

test.zip

[comment]

I am sorry, but I am still confused.

It would be helpful to know the purpose here, especially how - and why - are the weeks boundaries determined. In your sample, there is an overlap between the two weeks.

So it seems there could be a week starting on Saturday and ending on Friday. What then: are the amounts of Saturday and Sunday to be combined with the amount of the FOLLOWING Friday? Or perhaps with the preceding, UNRELATED, Friday?

Perhaps if you'd elaborate a bit more on the overall purpose here, a better way may be found.

[Søren Dyhr]

Doesn't it look like a SmartRange system with the date as foreign key, where the date dictates when a date is overlaid to a field used in to portal sum if being one of the 3 weekdays?:

--sd

[Kingme]

So it seems there could be a week starting on Saturday and ending on Friday.

Yes, you are almost right. (My weeks would never start on a saturday, mostly the weeks start either on a Wed, Thurs, or Fri. but your point is still right) And the database still needs to sum the amounts only for Friday, Saturday, & Sunday.

I'll take a look at S

[comment]

The simplest solution then, I think, would be this:

1. In the Days file, create a calculation field cWeekendID (result is number) =

Case ( DayOfWeek ( date - 1 ) > 4 ; WeekID )

2. In the Weeks file, define a relationship 'Weekend' to Days:

Weeks::WeekID = Days::cWeekendID

3. In the Weeks file, define a calculation field cWeekendGross (result is number) =

Sum ( Weekend::Amount )

[Kingme]

That worked.. Man I had that originally but decided on a different route. I've been spending countless hours redesigning this database for my company and the guy who built it before me has made it really difficult to figure anything out... I'll probably be posting other questions to this forum regarding this database.

I have changed my status to Novice.. ...I'm humbled...

[comment]

The structure does seem a bit convoluted. Like S

[Kingme]

Yes I have thought of that.. and might test that out.. The only issue is that there is specfic information for a film that is figured out on a weekly basis, like Distributor and Theatre information.

Plus it would probably take me awhile to move data around to the days file, since this old school database was orginally designed a couple years ago, so there alot of backlogged weeks, engagements and days.

Thanks again for the help.