Max of field (per Month, which is selected))

[Hans]

Hello everyone,

I apologize for my long post, in advance ;-)

Situation:

For my Form “Tracks” (Tracks for Hike / Bike), there are two important fields:

1) Creation Date of the Track: The date (typical date field), when the track has been created for the first time. It could be today, or in the past, not in the future. In the past only, when I enter the data today for a track, created in, e.g., 2006.

2) Track number: It is a text field with two-digit numbers 01-99. It is a text field, because of the missing leading zero (“0”) within the number fields for numbers 1-9. It is mandatory, that the track number is always a two-digit number.

2.1) The aim of the field is to have a unique number, starting with one (“01”) and should be entered manually, when a new entry is created. It is like a counter: the number of records that have been created per month. It is mandatory that each number within a month is unique.

2.2) Examples: in January, the first record is 01, the second record is 02 and the third record is 03. In February, the first record is 01, the second record is 02 and so on. March: 01, 02, 03 …

I need these two fields to create a unique six-digit track ID, which consists of the year (YY), the month (MM) and the track number (xx):

YYMMxx, e.g. 250401, 250402, 250403, 250404, 250501, 250502 …

250401 = First record in April 2025
250402 = Second record in April 2025

E.g., it is today, and I would like to enter a track that was created for the first time in June 2016. This “new” record is the third record for June 2016.

The track ID should be 160603 (year = 2016, month = June, and track number = 03).

After I have entered the Creation Date (1st of June 2016), the script should tell me that for this month (June) in this year, the minimum Track Number must be 03.

As already mentioned, the problem is that I also create entries retrospectively.

My question:

Which JavaScript could I use to check which is the highest number for the the month, for which I would like to create the Track Number? Something like:

var MinimumTrackNumber = (MAX(TrackNumber)) within ( (YEAR(of Creation Date) + MONTH(of Creation Date) ) + 1; // +1 => one more, than already available.

RETURN("The minimum Track Number for this track must be: " + MinimumTrackNumber);

Thanks in advance. BR

[Daniel Leu]

That’s an interesting project….

Following function should return the next track number you’re looking for. To keep the function generic, it returns an integer. You need to set the ids of the three different fields (date_id, track_number_id and track_id_id).

function getNextTrackNumber(){
	const date_id = 'fld-xxx';
	const track_number_id = 'fld-xxx';
	const track_id_id = 'fld-xxx';

	// get date and month from current record
	const date = new Date(record.getFieldValue(date_id));
	const month = date.getMonth()+1;
	const year = date.getFullYear();
	
	// filter records to match year/month of current record
	let recs = form.getRecords();
	
	let trackNumbers = [0]; // initialize array 
	recs.forEach((el) => {
		const thisDate = new Date(el.getFieldValue(date_id));
		const thisMonth = thisDate.getMonth()+1;
		const thisYear = thisDate.getFullYear();
	
		if (month == thisMonth && year == thisYear){
			let trackNumber = parseInt(el.getFieldValue(track_number_id));
			if (!isNaN(trackNumber))
				trackNumbers.push(trackNumber);
		}
	})
	
	console.log(JSON.stringify(trackNumbers, "",4))
	
	// get max value
	const maxTrackNumber = Math.max(...trackNumbers);
	const nextTrackNumber = maxTrackNumber + 1;
	console.log("nextTrackNumber: " + nextTrackNumber);

	return nextTrackNumber;
}

Attached is my test form. It contains two scripts, one to set the track number field and the other for the track id field.

The track number is automatically set to “01” when no entry is available, otherwise to the next value. You can overwrite this. The track id is set based on the date and the track number. Whenever you update one or the other, it’s updated as well.

Happy biking and tracking :-)

Attachments:

You must be logged in to view attached files.

[Hans]

Wow, thank you so much for the prompt response. I’m very impressed with how quickly it went. Unfortunately, I’m currently traveling for work and won’t have time until the weekend. When I have completed the implementation of the script in Tap Forms, I will get back to you with the result. Thanks again.

[Hans]

Yes, the project is interesting. I totally forgot to mention it. I would like to have a file name for each track so that I can always find my tours according to different parameters and have the latest tracks at the top of the list for tours. The tracks created by software for recording a tracks or the tracks recorded by the bike computer are then renamed accordingly. 
This will ensure that I can always find planned and recorded tracks under the appropriate names. “Track 01” or “Track recorded on xxx” are not good names, especially if you want to find older tracks or sort them. Maybe I can make the Tap Forms implementation available here.

[Daniel Leu]

How about adding the track as a file attachment? This way, the track is always readily available with the matching track record?

There’s a form exchange forum here as well where you could share your final form template so others can use it as well.

[Author]

Posts