Whether to use a script or a calculation?

Tap Forms – Organizer Database App for Mac, iPhone, and iPad Forums Script Talk Whether to use a script or a calculation?

Viewing 9 reply threads
  • Author
    Posts
  • April 27, 2020 at 9:13 AM #40377

    Victor Warner
    Participant

    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.

    May 4, 2020 at 10:52 AM #40451

    Sam Moffatt
    Participant

    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.

    May 26, 2020 at 2:24 AM #40702

    Victor Warner
    Participant

    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?

    May 26, 2020 at 7:39 AM #40703

    Sam Moffatt
    Participant

    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.

    May 27, 2020 at 2:23 AM #40707

    Victor Warner
    Participant

    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.

    May 27, 2020 at 7:22 PM #40711

    Sam Moffatt
    Participant

    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.

    May 28, 2020 at 7:49 AM #40712

    Daniel Leu
    Participant

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

    May 28, 2020 at 5:07 PM #40718

    Sam Moffatt
    Participant

    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).

    May 28, 2020 at 8:30 PM #40721

    Daniel Leu
    Participant

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

    May 28, 2020 at 9:12 PM #40722

    Sam Moffatt
    Participant

    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.

Viewing 9 reply threads

You must be logged in to reply to this topic.