Find nearest date before/after today in table/child form

Tap Forms – Organizer Database App for Mac, iPhone, and iPad Forums Script Talk Find nearest date before/after today in table/child form

Viewing 4 reply threads
  • Author
    Posts
  • February 13, 2021 at 6:28 AM #43437

    Chris Ju
    Participant

    Hi,

    has anyone ever tried to read the last date / next date (from the point of view of the current date) from a table / child form? Unfortunately, I have no idea and I don’t have the necessary programming skills. Maybe someone has a code snippet to achieve that.

    The best alternative would be to get the nearest from the array with Math.min/Math.max, but i don’t know how to do that.

    I tried the following, but can’t filter the max/min values:

    
    function findBeforeAfter() {
    
    	var buchungen_id = 'fld-fae5113e45f44af090693ccc0dd65ffe';
    	var datum_id = 'fld-98e4dff9f7d74050a6580f44bebf4e9a';
    	var buchungen = record.getFieldValue(buchungen_id);
    	var now = new Date();
    	var before = [];
        var after = [];
    	
    	console.log(now);
    	
    	for (var i = 0, count = buchungen.length; i < count; i++){
         	var tar = buchungen[i].getFieldValue(datum_id);
           	var diff = (tar - now) / (3600 * 24 * 1000);
           	
         	console.log(tar);
         	
           	if (diff > 0) {
    			before.push({diff: diff, index: i});
    			console.log(i);
    			console.log(diff); 
            } else {
                after.push({diff: diff, index: i});
                console.log(i);
                console.log(diff);           
            }
      	}
        before.sort(function(a, b) {
            if(a.diff < b.diff) {
                return -1;
            }
            if(a.diff > b.diff) {
                return 1;
            }
            return 0;
        });
    
        after.sort(function(a, b) {
            if(a.diff > b.diff) {
                return -1;
            }
            if(a.diff < b.diff) {
                return 1;
            }
            return 0;
        });
        return {buchungenBefore: before, buchungenAfter: after};
    }
    
    findBeforeAfter();
    

    Thanks in advance.
    Chris

    February 14, 2021 at 8:37 PM #43457

    Sam Moffatt
    Participant

    You’re already iterating through everything, I’d just keep track of the lowest value and just update as you find better candidates. Here’s a quick example with artisan crafted random numbers to show what I mean:

    var candidates = [ -32, 8, 21, 34, -3, 34, -10, -11, -12, 48, 42, 2, 3 ];
    
    function findBeforeAfter() {
    	var beforeValue = -Number.MAX_VALUE;
    	var afterValue = Number.MAX_VALUE;
    	for (var i = 0, count = candidates.length; i < count; i++) {
    		var diff = candidates[i];
    		if (diff > 0) {
    			if (diff < afterValue) {
    				afterValue = diff;
    			}
    		} else {
    			if (diff > beforeValue) {
    				beforeValue = diff;
    			}
    		}
    	}
    	return {"before": beforeValue, "after": afterValue};
    }
    
    JSON.stringify(findBeforeAfter());
    

    The trick we’re using here is to initialise the before value to be the largest negative number and the after value to be the largest positive number. Then as we iterate through the array we update the value if it’s lower than the last value we saw. The first positive or negative value we see will naturally make the if condition pass (they’ll generally be closer to zero) and this also gives you a sentinel value to validate against, for example if beforeValue == -Number.INT_MAX is true then you didn’t find a before value. Eventually we traverse the full array and we’re left with the two values nearest to the zero point. Output for my artisan crafted random numbers:

    {"before":-3,"after":2}
    

    Linear traversal, constant memory usage and you can use extra variables to track other items you might care about like a reference to the record in a similar way (e.g. beforeRecord = buchungen[i] or afterRecord = buchungen[i] in the relevant if statement).

    February 14, 2021 at 9:54 PM #43458

    Chris Ju
    Participant

    That’s it, what i was searching for. I’ll implement it and publish my results here … when I get it working ;-/ …

    Thanks again for your great support, Sam!

    February 15, 2021 at 2:13 AM #43463

    Brendan
    Keymaster

    So, I haven’t worked out how this would be done exactly in JavaScript, but in general what I would do is first insert the current date value into my array, then sort the array. Then find the index of the current date (in objective-c it would be something like NSInteger currentDateIndex = [array indexOfObject:currentDate]), then to get the before date, just index into the array with array[currentDateIndex - 1] and the after date array[currentDateIndex + 1]. Doing appropriate bounds checks first of course.

    February 15, 2021 at 11:00 PM #43474

    Chris Ju
    Participant

    Thanks, Brendan. That is also a similar but probably “simpler” and thus interesting approach… I’ll post my results!

Viewing 4 reply threads

You must be logged in to reply to this topic.