search value from database

[lenartpawel]

Hello

i'm sorry if this is in wrong topic, but i don't know how classify it.

So... i have got a problem. I have got database with dates..

fields:

week ID(autoenter)

start date(date)

end date (start date +6)

payment date (start date - 1)

and i want to get current date and find in which week id current date is and display it in 1 field (and autochange it every time when for example current date passed end date then change week id +1

[Tim W]

Hi,

How about this as a calculated field?

CalculatedWeekID = WeekofYear (Get(CurrentDate))

HTH,

Tim

[lenartpawel]

but it's a little bit different because my week id start ar begining of april (start of tax year in england), but i'm sure i use your suggestions. can you tell me if i use your calculation how can i get in this start (monday) and end date (sunday) for example if every day date is change

[Tim W]

O.K.

I worked out a calculation like this a year or two ago. But I don't have access to it at the moment. I will look this evening and see if I can lay my hands on it. The WeekofYearFiscal will allow for a non calendar year, but to change the week start day to Monday is a twist. Out of the box it uses Sunday as the week start day.

I will post my calculation if you don't get an answer before then.

Tim

[lenartpawel]

ok, thank you very much

[Tim W]

O.K. here it is.

CurrentWkOfFiscalYr = Floor ((Get ( CurrentDate ) - ( Date ( 4 ; 1 ; Year ( Get ( CurrentDate ) - ( Month ( Get ( CurrentDate ) ) < 4 ))) - DayOfWeek ( Date ( 4 ; 1 ; Year ( Get ( CurrentDate ) - ( Month ( Get ( CurrentDate ) ) < 4 ))) ) +2 ) ) /7)

It could be broken up into two separate calcs as follows:

CurrentWeekofFiscalYR = Floor ((Get ( CurrentDate ) - ( FiscalYrStartDate - DayOfWeek ( FiscalYrStartDate) +2 )) /7)

and

FiscalYrStartDate = Date ( 4 ; 1 ; Year ( Get ( CurrentDate ) - ( Month ( Get ( CurrentDate ) ) < 4 )))

I prefer the two separate calcs, because I read it easier, since it establishes the Fiscal yr start separately. Also the FiscalYrStartDate is easily referenced if you need to use it elsewhere.

Hope this helps,

Tim

Edited December 6, 2006 by Guest
A closing ) go omitted from the calculation.

[lenartpawel]

thx, it's work great, can you help me olso with counting date for example now is week 36 so it's check in what date was monday and in what date it will be sunday? or it will be better when i use this dates on separate database?

[Tim W]

The first day & last day of the current fiscal week? as follows:

Week 36 ; Monday December 4, 2006 thru Sunday December 10, 2006

Field ........... Calculation

Monday = Get(CurrentDate)- DayofWeek(Get (CurrentDate))+2

Sunday = Get(CurrentDate)- DayofWeek(Get (CurrentDate))+8

WeekString = "Week "& GetAsText(CurrentWeekofFiscalYR)&" ; "&"Monday "&GetAsText(Monday)&" thru "&"Sunday "&GetAsText(Sunday)

Monday & Sunday are a Date result.

WeekString is a Text result.

Is this what you desire to achive?

Tim

[lenartpawel]

Thank You very very much!!!

[comment]

I am somewhat puzzled regarding what is being calculated, and what is given. I wouldn't know how to calculate the current fiscal week, unless someone defined when does the count begin.

For example, Filemaker's own WeekOfYearFiscal() function considers the first week that contains four or more days of the year as the first week of that year.

In any case, your first calculation - CurrentWkOfFiscalYr - returns 39 when today is 1/1/2006, but on the next day the result is -12. I am guessing this is because your parentheses are messed up, but since I don't know the purpose of the calc, I can't be sure.

[Tim W]

Hey,

Read the third post. The fiscal year begins on April 1st. Each week starts on Monday.

For example, Filemaker's own WeekOfYearFiscal() function considers the first week that contains four or more days of the year as the first week of that year.

The FM function always starts the fiscal YR in January, only variable is the starting day of each week.

In any case, your first calculation - CurrentWkOfFiscalYr - returns 39 when today is 1/1/2006, but on the next day the result is -12.

Jan 2, 2006 is 12 mondays before the current fiscal year. But why does jan 1, 2006 equal 39? I fixed a ")" in the original formula above, but for the life of me don't know how you got that result. Are you sure you didn't test with 1/1/2007? Which should yield 39, or actually 40 now that I modified the formula??

Maybe that will help clear up any confusion.

Tim

[comment]

I am afraid you are missing my point:

The fiscal year begins on April 1st. Each week starts on Monday.

That still doesn't tell us WHICH Monday is the FIRST Monday of a fiscal year. Suppose April 1st is Sunday. What then? Is the first week of the year the week of the Monday of the same week (i.e. March 26), or is it the week starting the following Monday (April 2)?

The same question repeats for any other day of week - except Monday.

Regarding your formula, I replaced every occurence of "Get(CurrentDate)" with "TestDate".

When TestDate is Jan 1, 2006, the result is 39. That seems like a reasonable result, given that app. 39 weeks have elapsed since Apr 1, 2005.

When TestDate is Jan 2, 2006, the result is -12. That does not seem quite reasonable.

[Tim W]

O.K. I see where the calc breaks. In the fiscal year start calculation it should be corrected to the following:

FiscalYrStartDate = Date ( 4 ; 1 ; Case ( Month ( Get ( CurrentDate ) ) < 4; Year (Get ( CurrentDate )) - 1 ; Year (Get ( CurrentDate ) )))

As for which Monday is the first Monday of the year my assumption is the first monday after the start of the year. Most tax years start on a date regardless of which day of the week it may be. But most business have an established work week, which may or may not coinside with the tax year precisely. For example our work week begins on Monday and ends on Sunday, however December 31, 2005 was the end of our tax year and our last workweek didn't end until January 1st, 2006.

The problem with the calculation earlier was that it incorrectly calculated the year portion of the start date. The above replacement resolves the problem.

Thanks Comment for pointing that out.

Tim

Edited December 6, 2006 by Guest
Better version of calc