Whether to use a script or a calculation?

[Victor Warner]

In a form I have the following fields:

1. Invoice amount [the amount I invoice for a piece of work]
2. Amount paid [the amount a client pays]
3. Amount due [which is a calculation field – 1. – 2.]
4. iZettle charge [if the client pays by iZettle then iZettle charge]
5. Amount paid into bank account after iZettle charge [a calculation field – 3. – 4.]
6. Bank charge [if my bank makes a charge the bank charge]
7. Amount paid into bank account after bank charge [a calculation field – 3. – 6.]
8. Final amount received [which will be either 3., 5. or 7. 8. will then be use in various reports.]

I would like to know whether it is possible to use a calculation or rather if I should use a script
to achieve.

To put into 8.

1. if 5. and 7. are both zero than 3.
2. if 5. is not equal to zero then 3 – 5
3. if 7. is not equal to zero then 3 – 7

For either I would be grateful with how I can construct the appropriate calculation or script.

[Sam Moffatt]

I think you can do it in either a calc or a script but I feel a script would be easier to manage and debug.

Create a new script field, open up the editor and you should get something like this:

function Final_Amount() {

	// Replace with your own code
	var hello_world = "Hello World!"; 
	return hello_world;

}

Final_Amount();

Wipe out the middle bits so it looks a little closer to this:

function Final_Amount() {

}

Final_Amount();

Put your cursor in the middle and use the editor to import all of the fields you’re interested in (3,5 and 7; 8 will be your script field). It should look something like this:

function Final_Amount() {
	var amount_due = record.getFieldValue('fld-1234');
	var amount_paid_minus_izettle = record.getFieldValue('fld-1337');
	var amount_paid_minus_bank_charge = record.getFieldValue('fld-4321');
}

Final_Amount();

The exact names and ID’s will be specific to you. Then you just need to convert the logic you wrote into Javascript which is easy enough:

if (amount_paid_minus_izettle == 0 && amount_paid_minus_bank_charge == 0)
{
	return amount_due;
}
if (amount_paid_minus_izettle > 0)
{
	return amount_due - amount_paid_minus_izettle;
}
if (amount_paid_minus_bank_charge > 0)
{
	return amount_due - amount_paid_minus_bank_charge;
}
return 0;

This needs to be embedded into the function, so your final script looks a little like this:

function Final_Amount() {
	var amount_due = record.getFieldValue('fld-1234');
	var amount_paid_minus_izettle = record.getFieldValue('fld-1337');
	var amount_paid_minus_bank_charge = record.getFieldValue('fld-4321');

	if (amount_paid_minus_izettle == 0 && amount_paid_minus_bank_charge == 0)
	{
		return amount_due;
	}
	if (amount_paid_minus_izettle > 0)
	{
		return amount_due - amount_paid_minus_izettle;
	}
	if (amount_paid_minus_bank_charge > 0)
	{
	return amount_due - amount_paid_minus_bank_charge;
	}
	return 0;
}

Final_Amount();

That should do mostly what you want, I’d go for a script because it’s a little easier to debug and build up plus easier to format out than the calculation fields.

[Victor Warner]

Sam,

Belated thanks for this detailed and very helpful explanation (although what I wish to do has changed since your post).

Just a few of follow-up questions: I assume there is no functional difference, for the purposes of Tap Forms, in using or not using a function? For example there is no difference between;

function Final_Amount() {

	var payment_amount = record.getFieldValue('fld-bbf051ce1505486cbfc0c924b5b41029');

var izettle_charge_id = record.getFieldValue('fld-3c44182f03494f53bdb0d253d3058589');

	var izettle_net_amount_received = record.getFieldValue('fld-1e49468e17dc4ff1822860d16e893418');

		
	if (izettle_charge_id > 0)
	{
		return "Amount paid: £" + payment_amount + "\r" + "iZettle charge: £" + izettle_charge_id +  "\r" + "Net amount received: £" + izettle_net_amount_received;
	}
	
	return '';
}

Final_Amount();

And:

var izettle_charge_id = 'fld-3c44182f03494f53bdb0d253d3058589';

var izettle_charge = record.getFieldValue('fld-3c44182f03494f53bdb0d253d3058589');

var payment_amount_id = 'fld-bbf051ce1505486cbfc0c924b5b41029';
var payment_amount = record.getFieldValue('fld-bbf051ce1505486cbfc0c924b5b41029');

var izettle_net_amount_received_id = 'fld-1e49468e17dc4ff1822860d16e893418';
var izettle_net_amount_received = record.getFieldValue('fld-1e49468e17dc4ff1822860d16e893418');

if (izettle_charge > 0) {

result = "Amount paid: £" + payment_amount + "\r" + "iZettle charge: £" + izettle_charge +  "\r"  + "Net amount received: £" + izettle_net_amount_received;

}

else {

result = '';

}

result;

The second question concerns formatting of numbers. The fields used in the above example are set to numbers or a calculation, and formatted for currency. For example: “12.50”, but in using the script it appears as “12.5”. Is there a way to format the number with two decimals etc?

[Sam Moffatt]

You don’t need to use a function but having the function allows you to use the return statement to curtail execution flow. Without it you have to make sure the last value you set in your script is the value you want to set your script field to and using a function makes this neater for more complicated scripts. As you get down the track, using functions allows you an extra layer of encapsulation that can be useful for embedding scripts. I have a form script that I embed in other form scripts to do bulk creation. That works in part due to the use of a function to encapsulate the Javascript variables in a way that doesn’t interfere.

You need to change the format of your script field to be a number and the formatting settings should be identical to your calculation field. By default calculation fields return numbers and by default scripts return text. Just change the return type in the script editor and then the number formatting options for the field should work to get to two decimal places.

[Victor Warner]

Sam,

Thank for the explanation of the use of a function. I guess it is similar to a sub-routine in AppleScript speak.

Concerning formatting. Do you mean in the Edit Field Script window changing the Result Type from Text to Number. If so, in this case – there is no output as the ‘return’ contains a mixture of quoted text and variables.

[Sam Moffatt]

Yeah, function is another word for subroutine. I think the C folk went with function and that filtered down through that branch of the language tree. Etymology of language features is fun, subroutine, procedure, function, all roughly the same.

Ok, so welcome to the not so fun part of Javascript which is the amazing dearth of simple string formatting tools. This one has bothered me on and off for a while within Tap Forms but I’ve never had a good reason to figure it out. However I had another crack at something that might work but it’s a little awkward.

Coming from a C background there is a function called “sprintf” that formats a string. I found on GitHub a Javascript sprintf implementation and gave it a spin to see if it’d work. I did make one minor change and you can check it out on my own fork.

Copy that forked JavaScript file and create a new form script called “sprintf” and replace the placeholder text with the sprintf.js text (the link should take you to the raw version to easily copy).

Now for your use case where you want to embed, you should be able to use something like this:

form.runScriptNamed('sprintf');
payment_amount = 99.5;
izettle_charge = 12.54;
izettle_net_amount_received = payment_amount - izettle_charge;
result = sprintf("Amount paid: £%0.2f\riZettle charge: £%0.2f\rNet amount received: £%0.2f", payment_amount, izettle_charge, izettle_net_amount_received);

The form.runScriptNamed('sprintf') pulls in the library and defines sprintf (and also vsprintf for good measure). You’ll need to update your script to add that line. Then you’ll have to change the formatting to use the sprintf style formatting. I put in a relatively simple use case of sprintf which I think should give you the currency style formatting you’re after. sprintf uses percent signs as format markers so %0.2f says format a floating point string with two decimal places. It’ll pad to two decimal places for you as well and expand on the integral side appropriately. If you need a literal percent sign, put in %%.

I think that should get you further along with where you’re going.

[Daniel Leu]

Wow, that’s cool! Nice feature to have for formatted strings. Thanks for sharing, Sam!

[Sam Moffatt]

No worries, it’s been bothering me for a while and now it’s solved two things (one being the formatting and the other being finally figuring out that globalThis is the global in JSC).

[Daniel Leu]

I looked at the diff and noticed globalThis. Now I really have to look this up…

[Sam Moffatt]

Check out the following: https://github.com/tc39/proposal-global

Took it a while to be a standard, they tried to use global but apparently that broke pieces of the web. I was looking at Chrome and Firefox’s bug trackers for that. globalThis reads kinda dorky but you can’t have it all.

[T.L. Ford]

globalThis is undefined, which makes sprintf undefined. What am I doing incorrectly?

Attachments:

You must be logged in to view attached files.

[Victor Warner]

Based on the above, I entered the form script as Sam managed and then in the script where I wish to use sprint I added the following code:

form.runScriptNamed(‘sprintf’);
var s_total_for_invoice = sprintf(“%0.2f”,total_for_invoice);
var s_payment_amount = sprintf(“%0.2f”,payment_amount);
console.log(s_total_for_invoice + ” ” + s_payment_amount);

The field total_for_invoice is a calculation field and payment_amount is a number field.

The first record I ran the script (recalculate formulas) it worked, the second and subsequent times I am getting the following error:

Wording for invoice: TypeError: [sprintf] expecting number but found undefined, line:(null)

However, occasionally it does work and then a further run it will stop working.

Using the example database that T.L. Ford provided has not resulted in the error at all.

Any help would be gratefully received.

[Sam Moffatt]

It’s saying the value of the variable is undefined and not a valid value. A quick trick would be to rewrite to insert an empty value:

form.runScriptNamed(‘sprintf’);
var s_total_for_invoice = sprintf(“%0.2f”,total_for_invoice ? total_for_invoice : 0.0);
var s_payment_amount = sprintf(“%0.2f”,payment_amount ? payment_amount : 0.0);
console.log(s_total_for_invoice + ” ” + s_payment_amount ? s_payment_amount : 0.0);

It should capture the undefined and turn it into a zero value. That should at least cause the script not to fail.

Looks like I missed @cattailnu’s post, I don’t know why globalThis wouldn’t be defined, it seems to work for me and it should exist as it’s supposed to be a JSC provided variable so it not being set seems odd.

[Victor Warner]

Sam,

Thank you. It seems, from initial testing, to solve the issue.

Would you mind explaining why the variable is repeated twice, separated by a ‘?’:

total_for_invoice ? total_for_invoice

and why only one of the variables is repeated twice in the console.log? (adding a second s_total_for_invoice with a ? or removing the first s_payment_amount and the ? in the console.log causes an error message “SyntaxError: Unexpected token ‘:’. Expected ‘)’ to end an argument list.”

[T.L. Ford]

condition?true:false is a shortcut way of writing:
if (condition) then (true value) else (false value)

So this line:
console.log(s_total_for_invoice + ” ” + s_payment_amount ? s_payment_amount : 0.0);

Means:
print to log
value in s_total_for_invoice
” ”
if s_payment_amount has a value, print s_payment_amount otherwise print 0.0

– T

[Sam Moffatt]

As @cattailnu said, it’s a short hand conditional known as a ternery operator. I feel it makes things a little more concise instead of having to fit in an if/then/else clause, we can collapse it down to a single line.

If you look at the MDN link the use case we’re referring to is the “handling null values” one where if the variable is undefined (the error you reported), we use the alternate value. If it’s a “truthy” value (basically anything not falsy, aka false, 0, 0.0, an empty string like "", null, NaN or undefined), then we use the same value.

So the reason why it’s repeated is because if the value is truthy, we want to use it but if it is falsy we want to return the 0.0 value as a fallback. There might be some data that this breaks on but so long as it’s an actual number it should do what you need.

[Author]

Posts