High numbers

July 26, 200124 yr

Hello

Is it possible to create a search criteria to extract the highest value in a field/fields.

To be able to search the most recent records in our database, I have created a field that contains Year number:Week number. This way I can find records created a certain week any year, wether we created one record or any number that week.

But how do I create a link that dynamically finds the highest number? (This week s "number" 2001:30). And preferably how do I find last weeks records too with another link?

TIA

-Anders

July 26, 200124 yr

Some Javascipt can be used to establish the current week, hence you can search for that week in the database.

We use something similar for people filling-out surveys. Firstly we test if a survey exists for this week, if not, we create it. Then at any time during the current week that survey can be accessed and added to my the user. We use week/year/usercode as the key for each survey.

I can publish that Javascript here if you are interested.

Hope this helps.

Garry

July 27, 200124 yr

Author

Please do Garry.

I have been testing a JavaScript of my own. Who knows, maybe yours is better!

Thanks

Anders

July 27, 200124 yr

Here is the form and the Javascript to calculate the current week number. The script is placed in the <head> of the page. I downloaded this code from a Knowledge-base on a Javascript web-site.

function getDays(month, year) {

// create array to hold number of days in each month

var ar = new Array(12);

ar[0] = 31; // January

ar[1] = (year % 4 == 0) ? 29 28; // February

ar[2] = 31; // March

ar[3] = 30; // April

ar[4] = 31; // May

ar[5] = 30; // June

ar[6] = 31; // July

ar[7] = 31; // August

ar[8] = 30; // September

ar[9] = 31; // October

ar[10] = 30; // November

ar[11] = 31; // December

// return number of days in the specified month (parameter)

return ar[month];

}

function getWorkWeek() {

var now = new Date();

var year = now.getFullYear();

var month = now.getMonth();

var date = now.getDate();

now = null; // release memory

var newYearDayInstance = new Date(year, 0, 1); //instance of the date for January 1

var newYearDayOfWeek = newYearDayInstance.getDay(); //day of the week for January 1. 0 is Sunday

var daysUntilToday = newYearDayOfWeek; //days before January 1 in ww01

for (var j = 0; j <= month - 1; j++) { //count days in all previous months

daysUntilToday += getDays(j, year);

}

daysUntilToday += date; //add days in current month

return Math.ceil(daysUntilToday/7);

}

</script>

July 27, 200124 yr

Highest number:

Search with a sortfield of year ascending, then week ascending.

Last week:

You'd need to perform a calculation to find the current week, subtract one, then search.

- John

July 30, 200124 yr

Author

Hello

Thanks both of you. This is my version, it's a bit complicated since we for certain reasons don't want to have higher week numbers than 52 (and we also don't want the last days of the year to belong to week nr 1 next year, which in turn lead to some FM tweaking). Plus this script follow swedish week standard numbering, e.g. a week starts on monday:

<!--

function y2k(number) { return (number < 1000) ? number + 1900 number; }

function getWeek(year,month,day) {

var when = new Date(year,month,day);

var newYear = new Date(year,0,1);

var modDay = newYear.getDay();

if (modDay == 0) modDay=6; else modDay--;

var daynum = ((Date.UTC(y2k(year),when.getMonth(),when.getDate(),0,0,0) - Date.UTC(y2k(year),0,1,0,0,0)) /1000/60/60/24) + 1;

if (modDay < 4 ) {

var weeknum = Math.floor((daynum+modDay-1)/7)+1;

}

else {

var weeknum = Math.floor((daynum+modDay-1)/7);

if (weeknum == 0) { year--;

var prevNewYear = new Date(year,0,1);

var prevmodDay = prevNewYear.getDay();

if (prevmodDay == 0) prevmodDay = 6; else prevmodDay--;

if (prevmodDay < 4) weeknum = 53; else weeknum = 52;

}

return weeknum;

}

var now = new Date();

var veckonr = getWeek(y2k(now.getYear()),now.getMonth(),now.getDate());

var lastweek = getWeek(y2k(now.getYear()),now.getMonth(),now.getDate())-1;

var year = y2k(now.getYear());

var lastyear = y2k(now.getYear())-1;

if (veckonr < 53 && lastweek > 0)

{

document.write('<a href="http://193.10.179.106/lankar/FMPro?-db=lank.fp3&-format=search_results.htm&-error=search_results.htm&-lay=Layout%201&Vecka=' + year + veckonr + '&Filering2=2...3&-max=10&-sortfield=Title2&-SortOrder=ascend&-Find">Nytt i l

July 31, 200124 yr

If you have a field which has the week number and a second field which has the year, you could avoid the use of JavaScript by using an exact search of those two fields through cdml.

August 3, 200124 yr

Author

Keith

My initial question was how to search the highest value in a field, and thereby be able to use the method you suggest. And how do I solve the problem with "last week" or "highest value" -1.

And there is yet another problem with last week. The first week of a year and that particular week only, I need to subtract 1 from both Week and Year to search for the last week last year...

Thanks for your reply

-Anders

I agree with you when it comes to using JavaScript more than necessary, but this script fulfill my needs.

August 3, 200124 yr

Anders,

"Is it possible to create a search criteria to extract the highest value in a field/fields."

I went with the plural. though the same thing can be achieved with a concatenated field. Basically I was suggesting that there would be two fields. "Week" would contain a number from 00-53 (to allow for the partial week at the beginning/end of most years). The second field is just a year (1995, 1999, 2001...). These numbers get related to the appropriate records. It is possible to set a calculation for the week number so each date gets the appropriate number, and it is possible to extract the year from another field. One can search the two fields, or one can concatenate and search a single field or what amounts to a unique id.

How you then structure the format file is quite optional, of course. One might choose to offer pop-up menus or checkboxes, or one could allow for the typed entry of one or two numbers.

I'm sure the JavaScript works well for you. It is a very useful tool. I just like to consider options such as setting various fields within FileMaker database files specifically to be related to and accessed with format files through cdml. Either approach takes work. It is good to have options imo.