Difference in Value from one record to the next

[Michael Tucker]

Trying to get a “running difference” between records. The difference for the first record would always be null or 0 as there is no prior amount to it to sum.

Sort the fields oldest to newest, so your differences make sense.

Take the first “value” in first record, look at second “value” in second record, insert the difference in the “difference” field. Continue until all the found records are processed.

Like a running balance, but instead of adding the last two records you are comparing the last two records to get a running difference.

First record input $32,160.74. Difference is 0.
Second record input $31,834.11. Difference is -326.63
Third record input $31,730.30. Difference is -103.81

Value Field ‘fld-ee301521ec6549aa9a657a0b457a3e5e’
Difference Field ‘fld-816a0c97ff40447d9e13fb14d3dfc912’

Here is what I tried but does not work (I tried others but this one seems closer to right)…

Are these the backticks (\\) to keep it formatted easier to view?

\\
function Calc_Differences_DJI() {
	let difference = 0000;
	var value_id = 'fld-ee301521ec6549aa9a657a0b457a3e5e';
    var difference_id = 'fld-816a0c97ff40447d9e13fb14d3dfc912';
    var num1, num2;
	//need a saved find 'Symbl: DJI (oldest first)'
	//sort needs to have oldest first to caculate differences correctly
	//value_id is field 'Value'
	//difference_id is field 'Difference'
	
	var DJI_records = form.getSearchNamed('Symbl: DJI (oldest first)').getRecords();
{	
	// set the first record value of difference_id field (1st record is always 0000)
	rec.setFieldValue(difference_id, difference);
	
}	
	console.log("First Difference = "+(difference));
	
    
    // index starts at 1 so this is actually the 2nd record. Arrays are 0 indexed, so first record is 0
    
    // get the first record's value of the value_id field
    	

	for (var index = 1, count = DJI_records.length; index < count; index++){
	// get the record
		
	var rec = DJI_records[index];
	num1 = DJI_records[0].getFieldValue(value_id);
	// set the first value into the num1 field
}
	console.log("First Value = "+(num1));
	

	
	// now increment the value from the current value field. this will be the balance stored in the next record in the next iteration of the loop in the above line.
{
	num2 = rec.getFieldValue(value_id);
	console.log("Second Value = "+(num2));
}
				
	var difference = function (num1, num2){
            return Math.abs(num1 - num2);
            
    rec.setFieldValue(difference_id, difference);

	console.log("Difference = "+(difference));
	}
		
	document.saveAllChanges();
}

Calc_Differences_DJI();
\\

• This topic was modified 1 year, 11 months ago by Brendan.

Attachments:

You must be logged in to view attached files.

[Brendan]

Hi Michael,

FYI, if you put back-ticks around your code, the forum will format it. I’ll edit it for you.

Thanks,

Brendan

[Brendan]

A back-tick character is like an angled left apostrophe. It’s below the tilde ~ character

[Brendan]

Can you please post your form template? What you’ve got above definitely doesn’t work. Your screenshot shows a relationship, but your code is looping through records of a Saved Search, not of the records in the relationship.

[Michael Tucker]

I’ve uploaded the template.
Thanks for taking a look.

Attachments:

You must be logged in to view attached files.

[Brendan]

What’s the purpose of making this a reciprocal relationship to itself?

[Brendan]

Try this:

function Diff_Calc() {

	let records = form.getRecords();
	let value_id = 'fld-ee301521ec6549aa9a657a0b457a3e5e';
	let difference_id = 'fld-816a0c97ff40447d9e13fb14d3dfc912';

	let first_rec = records[0];
	var prev_value = first_rec.getFieldValue(value_id);
	
	if (prev_value == undefined) {
		prev_value = 0;
	}
	
	first_rec.setFieldValue(difference_id, 0);
	
	for (var index = 1, count = records.length; index < count; index++){
	
		let rec = records[index];
		var value = rec.getFieldValue(value_id);
		
		var diff = value - prev_value;
		rec.setFieldValue(difference_id, diff);
		
		prev_value = value;

	}
	form.saveAllChanges();
	
}

Diff_Calc();

[Michael Tucker]

Thanks **so much** that works just as expected.

I don’t really need to link these into another form, but the (self-link) table view is easy to just glance at & see the trend, if the stock is trending up or not. Could use a trading chart, but for me the daily changes in amounts (differences) mean more to me.

I realize there are view options, but for me on the iPad this just works better, I can enter the info & open the triangle (section heading) to see the table right where I’m working. Close it if not needed.

Rarely would I need a form like this, but currently we are thought to be heading into a market crash. Thinking is it will get down to ~21000 or lower.

I’d prefer to pull out an IRA we have before it dips down near that far.

Short market weeks (weeks with a holiday) usually do well, so I’m hoping the up trend continues into Memorial Day Weekend (to get closer to the ~35000 we were topping out at) — but a couple of slipping days I can “see” with the help of this template & maybe then decide to cut loses.

Rather than keep notes or try to remember, or try to find a website that shows daily numbers — whipped up this “module” in actually just a few moments.

Thanks again for taking the time to respond & carry thru with help.

Attachments:

You must be logged in to view attached files.

[Author]

Posts