Need an example code to SUM checkboxes

[Karl M. Rowe]

Hi, Folks!

In this very first database I’m trying to create in the app, I have 15 checkboxes in the database I’ve created (so far), but I thought I would use the “Calculation” field type to use SUM to total the checked boxes, so that when all 15 were checked a hidden image would become visible and a reset all checkboxes (y/n) control would become visible so that only checkboxes in the record would be reset to “unchecked” and the image rehidden. It would also be great if the database could only show checked checkboxes and hide all unchecked checkboxes, except one (e.g., when #1 is checked, checked checkbox #1 and unchecked #2 becomes visible; when #2 is checked, then checked checkboxes #1 and #2, and unchecked #3 becomes visible; when #3 is checked, then checked checkboxes #1 and #2 and #3, and unchecked #4 becomes visible; etc.), but I have no idea how I would accomplish this either. I thought that if someone had constructed some example code that provides such functionality for the first and maybe for the second, I might be able to glean from it what I want to have in my database. Thanks.

Peace

[Brendan]

Hi Karl,

I haven’t exposed the Hide Field function to the Script engine yet.

It wouldn’t work with the Calculation field type anyway as a Calculation field cannot modify anything outside of returning a result of evaluating the formula.

So at the moment there’s no function to do this. But I’m working on exposing more field level properties to the Script engine as I receive customer requests for them.

Thanks,

Brendan

[Brendan]

Having said that, you can certainly sum the checkboxes.

Just do this in your formula:

[CB Field 1] + [CB Field 2] + [CB Field 3]

and so on…

If a Checkbox is not set, the result is 0. If it is set, the result is 1.

[Karl M. Rowe]

Thanks, Brenden!

I’ll try this when I get back to my iPad. I was thinking to use SUM since I have 15 checkboxes and I thought this would make for more compact code, but I didn’t have an example to examine.

I’m wondering: Is it possible to code a “Reset” to reset all 15 checkboxes?

Peace

[Sam Moffatt]

You could make a form script and just reset the field values when it runs:

record.setFieldValue(cbo1_id, 0);
record.setFieldValue(cbo2_id, 0);
record.setFieldValue(cbo3_id, 0);

Replace cbo1_id, cbo2_id and cbo3_id with the field ID’s of each of the checkboxes up to your 15, the script editor will insert some boilerplate for you if you double click the field names.

You can bind a shortcut to the form script to make it easier to execute. It’s also available via the “Scripts” menu item and of course via the Scripts tab to the right of Fields on the Forms panel on the right hand side.

You could also create a field script based on the value of another checkbox but form script is a little more implicit in my mind. You can also put an “are you sure” prompt into your form script for extra safety, check out the prompter section of the JavaScript API.

[Karl M. Rowe]

Hi, Sam!

I appreciate your pointing out to me how I might use a script with my database to reset a set of checkboxes. I’d like to see a few examples of how such might be used. An example of an “Are you sure (y/n)?” prompt using Javascript is something I’d also be interested in seeing. Thanks for your reply.

Peace

[Karl M. Rowe]

Hi, Brenden!

Thanks for posting replies to my post. I’ve learned a little more about what this database is capable of doing.

Attached to this post is a .CSV file (Loyalty15.csv), which is just a copy of my database, which contains three scratch records. In my original are two sets of checkboxes, the first set of 15 and the second set of 10. What you will see when it is imported are 15 text fields and 10 text fields, respectively. I don’t know if the “Checkbox” field type can be retained after the exporting and importing of the database, but I had to change the field type after importing it afresh into the app.

I used your idea to make a Calculation field to sum the first set of checkboxes; if the first Calculation field equals 15, then the second Calculation field becomes “YES.” If not, the second Calculation field remains “NO.”

I used an additional Calculation field to sum the second set of checkboxes; “Over-15” contains the total number of checked checkboxes.

To explain, customers are rewarded with a free item for every 15 items purchased. If anyone purchases 19 items, for example, there will be a “Yes” and “4.” A search reveals the number or records in which all of the first set of checkboxes have been checked; these records are recorded elsewhere, and all 15 of these checkboxes would then be reset. The four “Over-15” checked checkboxes would be noted and the first four of the first set of checkboxes would then be checked. I would love to be able to automate these two functions with a script.

There’s room for improvement and any suggestions that you and others here might have to improve this database would really be appreciated. I did wonder if it was possible to line up both sets of checkboxes horizontally, instead of as they are now, vertically; if this is possible, I would appreciate your pointing out to me a web page containing the steps required to do this.

Lastly, I wondered if there was a way to secure the database, to make it impossible for users without a password (or permissions) to delete records; they could add records, run searches, but not delete any of them. How might I accomplish this using my iPad? Thanks.

Peace

[Karl M. Rowe]

I attempted to attach the database file to my post, which returned an error, so I uploaded the zip file to my website instead:

http://wpconsulting.com/Loyalty15.zip

[Brendan]

Hi Karl,

When you export records, if you use the Tap Forms Archive format, Tap Forms will export the form template along with all the records. That’s a more complete way of sending someone your database.

It’s also handy for copying a form and all its records from one document to another or to give to another user.

So in this case, your template with any Calculation or Script fields did not come across.

I’m working on some access controls code for the next update to Tap Forms which would allow you to specify for a specific form what a user of that form could do. E.g. edit form, add records, delete records, update records, etc. It’s not ready yet and so far it’s just on macOS, but I will bring it over to iOS once I’ve got the Mac version complete.

[Karl M. Rowe]

This is a quick post to provide the link to the archive for the database I just uploaded to my website, which can be found here:

http://wpconsulting.com/Loyalty15.zip

[Brendan]

Hi Karl,

Thanks for the file. I’ve just had a chance to look at it with your formulas.

However, I’m not sure what you mean by this:

The four “Over-15” checked checkboxes would be noted and the first four of the first set of checkboxes would then be checked. I would love to be able to automate these two functions with a script.

Here’s a script that’ll reset the checkmarks for the first 15:

function Reset_Checkmarks_1_15() {

   var f1_id = 'fld-66cc9d4ad3c149c496cd3be90ea0f88c';
   var f2_id = 'fld-0dca226557ba47d2b806dd2f20d955ec';
   var f3_id = 'fld-e084b0d50e104044ab8baf2df11ae1e4';
   var f4_id = 'fld-6ee4490027334c8ea99f9e0365fef5f7';
   var f5_id = 'fld-b31757d2bda64dbfadf5fa3569857c33';
   var f6_id = 'fld-fbb171c0d7c14d90b5a248ea6baab1a9';
   var f7_id = 'fld-43a5c0ff16694eb19325b9c24dcaa2de';
   var f8_id = 'fld-4f1cbec2e2bd475b8a58badd20e66826';
   var f9_id = 'fld-9985de5176ac4861890a245c729e818a';
   var f10_id = 'fld-c5cf49dd9e954f18952e0eb7354f93aa';
   var f11_id = 'fld-49c502ea885342738df3ce1958d0542e';
   var f12_id = 'fld-22395b52e8a344e8b23499df65def764';
   var f13_id = 'fld-07b05a9ad759465abd4ffc436e74b11e';
   var f14_id = 'fld-6be47e6efbee473b9a725e67167dd90c';
   var f15_id = 'fld-d507eb4e113d4380b1ab5f2f20518e6c';

   record.setFieldValue(f1_id, 0);
   record.setFieldValue(f2_id, 0);
   record.setFieldValue(f3_id, 0);
   record.setFieldValue(f4_id, 0);
   record.setFieldValue(f5_id, 0);
   record.setFieldValue(f6_id, 0);
   record.setFieldValue(f7_id, 0);
   record.setFieldValue(f8_id, 0);
   record.setFieldValue(f9_id, 0);
   record.setFieldValue(f10_id, 0);
   record.setFieldValue(f11_id, 0);
   record.setFieldValue(f12_id, 0);
   record.setFieldValue(f13_id, 0);
   record.setFieldValue(f14_id, 0);
   record.setFieldValue(f15_id, 0);

   form.saveAllChanges();

}

Reset_Checkmarks_1_15();

[Karl M. Rowe]

“The four ‘Over-15’ checked checkboxes would be noted and the first four of the first set of checkboxes would then be checked. I would love to be able to automate these two functions with a script.”

I’m sorry; the items that correspond to the first 15 checkboxes as well as four checkboxes in the “Over-15” checkboxes are the number of items purchased during a calendar month by the customer. I had tossed out 19 items as an example without explaining why 19. If the customer were to have purchased 19 items during the month, this would be recorded in the database by making sure that all 15 of the checkboxes in the first set are checked, and making sure that four of the ten “Over-15” checkboxes in the second set would be checked.

A search of the database for all records containing a “Yes” at the end of a month would include this record, indicating that this customer is entitled to a free item, but the 15 checkboxes of this record and all other “Yes” records would then need to be reset (so I would definitely have a use for the script you provided to reset these checkboxes!). This means that for each “Yes” record, I would do such a reset and then use the number in the database that indicates the total number of “Over-15” checkboxes checked—in the case of this record that number is “4”—to set four of the 15 checkboxes just reset and then reset the “Over-15” checkboxes (using a similar script to reset all ten checkboxes).

The database is ready to record subsequent purchases made, which would entitle this same customer to another free item should she purchase an additional 11 items during the following month. I realize this explanation was needlessly verbose, but I rushed this post. I’ll read it layer, but I hope you should get the drift.

Peace

[Brendan]

I guess I’m confused by would be noted. Is that something that needs to be automated too? What do you mean by noted?

Is it that when all 15 are checked, the first of the X checkboxes must be then clicked, then the next time all 15 are checked, then the 2nd of the X checkboxes must then be clicked?

If maybe you could explain it in terms of the steps you take when you do it manually, using explicit terms. When you say noted, I think that you want that recorded within the form somewhere, but then I don’t know specifically what you mean.

Maybe I’m just being daft. Sorry.

[Sam Moffatt]

Why not record the purchase events and calculate how many purchases from there?

[Karl M. Rowe]

Hi, Sam!

“Why not record the purchase events and calculate how many purchases from there?”

This database is being built for non-computer literate people, who would only be responsible for nothing purchases made and by whom; nothing more.

The “admin” would note the purchases made daily and note the free items that would be awarded to eligible customers, snd reset the two sets of checkboxes in the database as required. Now I was given some sample code to use in a script, but I’ve no idea how to identify each of the checkboxes using the app, but this database isn’t something I would build for my use.

Pesce

[Karl M. Rowe]

Hi, Brenden!

“I guess I’m confused by would be noted. Is that something that needs to be automated too? What do you mean by noted?”

The person I am calling the “admin” would use pen and paper to make a “note” of the customers that have reached the 15-unit purchase threshold, entitled them to a free unit. The admin would also make a “note” of how many checkboxes in the second set have been checked (there is a “Calculation” field that indicates how many checkboxes have been checked so that after both sets of checkboxes have been reset, this number of checkboxes corresponded to the previously checked checkboxes in the second set of checkboxes would be checked in the first set of checkboxes. I would love to create a script that would be run on all records containing 15 or more checked checkboxes to reset both sets of checkboxes and then fill whatever number of checkboxes there were in the second set of checkboxes in the first set of checkboxes. But at present I have no idea how to designate which checkboxes should checked or unchecked, as the case may be, or how an FOR/NEXT loop can be employed in such a script to set within the first set of checkboxes the equivalent number of checkboxes that had been set in the second set of checkboxes.

“Is it that when all 15 are checked, the first of the X checkboxes must be then clicked, then the next time all 15 are checked, then the 2nd of the X checkboxes must then be clicked?”

No; my previous explanation here was confusingly awful, and I hope what I’ve said above clears up the ambiguity I caused you and others here trying to comprehend my explanation.

“Maybe I’m just being daft. Sorry.’

I’m sure you aren’t daft. I just wasn’t clear and I’m sorry.

Peace

[Sam Moffatt]

Let’s take a step back, my guess here is that you’re trying to port to TapForms a workflow that is based on maybe stamps or some other process today. Each purchase you get a stamp on a card and after 15 stamps, you get a voucher/coupon for a free item (or you simply hand in the completed card).

If that’s the case then I’d model that with a “card” form that has 15 checkboxes on it and then those get ticked off. If you purchased 19, a new card record would need to be created with those four items ticked. You could have a customer field in here though I’d really suggest modelling that as a form because then you can lookup by customer and see their loyalty cards. You could in theory do some form scripts in the “customer” form which just accepted how many items were purchased and would encapsulate the logic of the cards.

One item that would work here is in the “customer” form is setting up a JOIN field on a calculation field that found all of the “cards” that are completed and let them be redeemed. If there was a “redeemed” checkbox on the form that could be used as a part of a toggle to filter records.

To recap:

– “customer” form with all of the customers with 1:M to “card” form
– “card” form with each of the purchases checkboxes and a “redeemed” checkbox
– form script in “customer” to check the boxes and create new card records
– JOIN field in in “customer” to find fully checked cards that aren’t redeemed

This is one way of tackling the problem that I think is a little closer to what you might have started with but this is a bit of a stab in the dark.

—-

Let’s take a different approach that would be modelled more off a more integrated system. For data entry, it’s adding a record for each purchase that is made. Ideally that’d be automated with your POS or similar system. If you start with a customer form, then you pull up the customer record first and add their purchases to it defaulting to the current date. You could also model this as a quantity count which would be easier to enter but that makes it a little harder to do the next part.

I’d also model it as another form setup with Link to Form 1:M for the purchases that are made. You could automate creating new “coupon” records that link to the purchases automatically and have a “redeemed” flag on the coupon. This is in a sense replicating the card aspect to have each of the purchases be a “stamp” in the record. Each has 15 items linked and that’s could be automated by a script.

Similar to the above the cards could be linked together and you could use a JOIN field to have it show up.

Do either of these sound like something closer to helping solve your problem?

[Karl M. Rowe]

Hi, Sam and Brenden! i’m

I appreciate your pointing out to me how I might use a script with my database to reset a set of checkboxes. I’d like to see a few examples of how such might be used. An example of an “Are you sure (y/n)?” prompt using Javascript is something I’d also be interested in seeing. Thanks for your reply.

I wrote the following script that is designed to do a checkbox reset of the first set of checkboxes *only* when the “reward” field is equal to “YES,” and to use the number in the “over-15” field that represents the number of checked checkboxes in the second set of checkboxes to reset all of these checkboxes, but repopulate the reset first set of checkboxes with the number of checkboxes in the “Over-15” field, and afterward display a “Done!” prompt If the “reward” field is equal “no,’ the script should end without a checkbox reset at all.

For example, if there are three checked checkboxes in the second set of checkboxes, the script would end with three checked checkboxes in the first set of checkboxes; if there is one checked checkbox in the second set of checkboxes, the script would end with one checked checkbox in the first set of checkboxes. Thanks for any help “you guys” might be able to provide me.

The script, which doesn’t work, and may need to be discarded for a better one, is s follows:
function Test2() {

var f1_id = ‘fld-66cc9d4ad3c149c496cd3be90ea0f88c’;
var f2_id = ‘fld-0dca226557ba47d2b806dd2f20d955ec’;
var f3_id = ‘fld-e084b0d50e104044ab8baf2df11ae1e4’;
var f4_id = ‘fld-6ee4490027334c8ea99f9e0365fef5f7’;
var f5_id = ‘fld-b31757d2bda64dbfadf5fa3569857c33’;
var f6_id = ‘fld-fbb171c0d7c14d90b5a248ea6baab1a9’;
var f7_id = ‘fld-43a5c0ff16694eb19325b9c24dcaa2de’;
var f8_id = ‘fld-4f1cbec2e2bd475b8a58badd20e66826’;
var f9_id = ‘fld-9985de5176ac4861890a245c729e818a’;
var f10_id = ‘fld-c5cf49dd9e954f18952e0eb7354f93aa’;
var f11_id = ‘fld-49c502ea885342738df3ce1958d0542e’;
var f12_id = ‘fld-22395b52e8a344e8b23499df65def764’;
var f13_id = ‘fld-07b05a9ad759465abd4ffc436e74b11e’;
var f14_id = ‘fld-6be47e6efbee473b9a725e67167dd90c’;
var f15_id = ‘fld-d507eb4e113d4380b1ab5f2f20518e6c’;

var fx1_id = ‘fld-e3cd7e31ec7541538b424b7dfbd51ae0’;
var fx2_id = ‘fld-c98c294dffbc4f19a02dea33473df2c8’;
var fx3_id = ‘fld-98c491b1011c430f9cdae91937c6c6d5’;
var fx4_id = ‘fld-2fbadd9308b04c7ab0de74eeee1d5ef6’;
var fx5_id = ‘fld-af079845162241559254c094021892d5’;
var fx6_id = ‘fld-d864cc7f08154fc2bad5762f4d086f4f’;
var fx7_id = ‘fld-e4797c4cbab44dc0be98495b18978c79’;
var fx8_id = ‘fld-ba105d19a44e4cc59670df9c538b8992’;
var fx9_id = ‘fld-488d0a66e8b04e62b71288305b00dff5’;
var fx10_id = ‘fld-f96f1a34300b4a2ab2d7cd30f7801e69’;

var over_15 = record.getFieldValue(‘fld-83370febd23045f6b839a9401cf598fd’);
var reward = record.getFieldValue(‘fld-61bd01ed639d4166949dd3404d10036e’);

record.setFieldValue(f1_id, 0);
record.setFieldValue(f2_id, 0);
record.setFieldValue(f3_id, 0);
record.setFieldValue(f4_id, 0);
record.setFieldValue(f5_id, 0);
record.setFieldValue(f6_id, 0);
record.setFieldValue(f7_id, 0);
record.setFieldValue(f8_id, 0);
record.setFieldValue(f9_id, 0);
record.setFieldValue(f10_id, 0);
record.setFieldValue(f11_id, 0);
record.setFieldValue(f12_id, 0);
record.setFieldValue(f13_id, 0);
record.setFieldValue(f14_id, 0);
record.setFieldValue(f15_id, 0);
//record.setFieldValue(fx1_id, 1);
//record.setFieldValue(fx2_id, 1);
//record.setFieldValue(fx3_id, 1);
//record.setFieldValue(fx4_id, 1);
//record.setFieldValue(fx5_id, 1);
//record.setFieldValue(fx6_id, 1);
//record.setFieldValue(fx7_id, 1);
//record.setFieldValue(fx8_id, 1);
//record.setFieldValue(fx9_id, 1);
//record.setFieldValue(fx10_id, 1);

if (reward == ‘YES’ && over_15 == 1) {
record.setFieldValue(f1_id, 1);
record.setFieldValue(fx1_id, 0);
} else

if (reward == ‘YES’ && over_15 == 2) {
record.setFieldValue(f2_id, 1);
record.setFieldValue(fx2_id, 0);
} else

if (reward == ‘YES’ && over_15 == 3) {
record.setFieldValue(f3_id, 1);
record.setFieldValue(fx3_id, 0);
} else

if (reward == ‘YES’ && over_15 == 4) {
record.setFieldValue(f4_id, 1);
record.setFieldValue(fx4_id, 0);
} else

if (reward == ‘YES’ && over_15 == 5) {
record.setFieldValue(f5_id, 1);
record.setFieldValue(fx5_id, 0);
} else

if (reward == ‘YES’ && over_15 = 6) {
record.setFieldValue(f6_id, 1);
record.setFieldValue(fx6_id, 0);
} else

if (reward == ‘YES’ && over_15 == 7) {
record.setFieldValue(f7_id, 1);
record.setFieldValue(fx7_id, 0);
} else

if (reward == ‘YES’ && over_15 == 8) {
record.setFieldValue(f8_id, 1);
record.setFieldValue(fx8_id, 0);
} else

if (reward == ‘YES’ && over_15 == 9) {
record.setFieldValue(f9_id, 1);
record.setFieldValue(fx9_id, 0);
} else

if (reward == ‘YES’ && over_15 == 10) {
record.setFieldValue(f10_id, 1);
record.setFieldValue(fx10_id, 0);
}
}

// Replace with your own code
var msg = “Done!”;
return msg + ‘ ‘ + over_15;

}

Test2();

[Karl M. Rowe]

[This post was in my iPhone’s browser with an error since last week that I only just noticed, but for some reason it was never posted]

Hi, Sam!

“Why not record the purchase events and calculate how many purchases from there?”

This database is being built for non-computer literate people, who would only be responsible for nothing purchases made and by whom; nothing more.

The “admin” would note the purchases made daily and note the free items that would be awarded to eligible customers, snd reset the two sets of checkboxes in the database as required. Now I was given some sample code to use in a script, but I’ve no idea how to identify each of the checkboxes using the app, but this database isn’t something I would build for my use.

Pesce

[Karl M. Rowe]

Hi, Sam!

“Do either of these sound like something closer to helping solve your problem?”

I liked reading what you proposed as a way to go forward. I will be re-reading your post to make sure I didn’t miss anything, but on my first read-through, I wanted to see the code, to see how this would work.

whatever reason I have been posting responses to the posts here much later than I would have otherwise, and the result is that I’m never sure when my posts have actually posted here. (One of my posts were stuck in the browser with an error message from your web server.)

Let me ask you a simpler script question:

Let’s say in a form you had the Checkboxes A!..A3 snd Checkboxes B1..B3, and when CBB1 and/or CBB2 and/or CBB3 is/are set to “1” (checked), then the CBA1, CBA2 and CBA3 checkboxes would be reset to “0” (unchecked), and only if CBB1 was set to “1” would the corresponding CBA1 would to set to “1” and CBB1 would be set to “0”; only if CBB2 was set to “1” would the corresponding CBA2 would to set to “1” and CBB2 would be set to “0”; and only if CBB3 was set to “1” would the corresponding CBA3 would to set to “1” and CBB3 would be set to “0.”

What would such code look like? Would you use a “IF/ELSE” or a “FOR” loop to do this or something else altogether?

I’m also interested in seeing your proposed script. Thanks.

Peace

[Brendan]

Did you get any syntax errors when running your script in the Script Editor?

Make sure you don’t use curly quotes or curly apostrophes. That can cause things to fail.

if (reward == ‘YES’ && over_15 == 10) {

From what I can see, you’ve got curly apostrophes around YES. It should be:

if (reward == 'YES' && over_15 == 10) {

[Sam Moffatt]

Ok so I decided to do a quick POC for this that turned out to be not so quick. I’m going to share this quickly since it’s 2am and I need to sleep but I think it is a step in the right direction. It doesn’t deal with checkboxes because that logic is a little hard for me to follow.

The attached document has three forms: Purchases and Coupons and Customers. I’m going to do this a little backwards so bear with me.

The “Purchases” form just records the individual purchases that were made, think of these like your checkboxes but with one record per purchase. This has advantages when it comes to sync as you are less likely to have sync conflicts with two people changing the same record. It’s a pretty useless record in general tracking a created date & time as well as a unique ID. It has links back to Customer and Coupon.

The “Coupons” form has a link to the Customer, a Coupon ID, a couple of counters, a flag for if this coupon has been claimed, a JOIN key field and then a Link to Form for the “Purchases”. The “Coupon Key” calculation takes the unique ID of the Customer, adds the purchase counter field and adds the claimed flag. This is done so that the Customer form can JOIN to get unclaimed coupons.

The “Customers” form has a bunch of stuff in it. I added a photo and title field for fun then we get into the more interesting business. Probably easiest to just list each of the fields out:

– Unclaimed Coupon Count: A script to count the JOIN field for unclaimed coupons.
– Unclaimed Coupons: A Link to Form JOIN field for unclaimed coupons; uses the Coupon Key to find coupons belonging to this customer that have 15 purchases and aren’t claimed.
– Purchases: A Link to Form 1:M field for all of the purchases the customer has made.
– Coupons: A Link to Form 1:m field for all of the coupons the customer has.
– Coupon Key: This is a calc field with the customer ID, the number 15 (number of purchases) and the number 0 (the coupon claimed flag)
– Customer ID: This is a single time generated unique identifier.

For “Customers”, there’s a “Customer View” layout that is your front end view for your data entry folk. It’s ugly right now but has two buttons that do something interesting: add purchase and claim coupon. Add purchase prompts to add a number of purchases and “claim coupon” prompts for a coupon code to redeem.

There are two other form scripts: “Claim Coupon (Any)” and “Create Coupons”. The first is a variant of “claim coupon” that finds any valid coupons and claims them. If you’re handing out things to customers to return, that’s not so useful but if you are letting customers claim when they have credits then you can use this instead. The second looks through the database to process all of the customers, all of their purchases and creates “coupon” records for them. This “Create Coupons” script would be your daily admin task. In theory it could be included with the “Add Product” flow to be done automatically.

Some quirks I found when playing with this is that not everything is being recalculated properly. After running some of the scripts, you might need to go to each form and click the “refresh records list” button. If something doesn’t look right, go through each of the three forms and click on this button. That should clear out any weird caching behaviour and cause the calculation fields to update properly. Something about adding records via scripting doesn’t trigger the updates properly for calculation and script fields which then means the JOIN fields are broken.

This could be simplified further by hiding the fields in places but I think this captures some of the simplicity you’re after in a model that I think maps better to how TapForms internally operates. This is also much safer when handling multiple data entry points and potential conflicts that having a single form with a lot of checkboxes wouldn’t. Downside of this is that it only works on the Mac.

Purchases: (frm-1ef8c6949a634690b1a8c0ba04d4acda)
	'Customer' from_form (fld-8d14f6b4d1f548969179465843edeaa0) toOne 'Customers' (frm-74813cf4d5e9466786d94835bb631e89)
	'Date & Time' date_time (fld-04cf1e8e78c44bbbbdbb73de551c9ff7)
	'Purchase ID' calc (fld-b9e4ce6cc4924a8abf4303a520c9c1eb)
	'Coupon' from_form (fld-9747e0950c184972a24ec957365ef4de) toOne 'Coupons' (frm-75b20fd711814723aeec5d0b82654885)

Customers: (frm-74813cf4d5e9466786d94835bb631e89)
	'Photo' photo (fld-a713d3becf424a03a9874e4ef58f1cac)
	'Name' text (fld-09c06e6fb4ac45f396d34f37f1eb5134)
	'Unclaimed Coupons Count' script (fld-5df1a4a31f26465eb92300f2e5dbef20)
	'Unclaimed Coupons' form (fld-ceed38ab41c3491e87701c9ed8afd9e1) join 'Coupons' (frm-75b20fd711814723aeec5d0b82654885)
			 ON Customers.Coupon Key == Coupons.Coupon Key
	'Purchases' form (fld-473360977fa94a32a5ff7ac7bc936abc) toMany 'Purchases' (frm-1ef8c6949a634690b1a8c0ba04d4acda)
	'Coupons' form (fld-acaa893ee387440ea894a42f6aaf93db) toMany 'Coupons' (frm-75b20fd711814723aeec5d0b82654885)
	'Coupon Key' calc (fld-8a0b920043ef457b840bc7359331dcb6)
		Referenced Fields: 
		 - Customer ID (fld-d22a0cd4e0e848b7a0215b8c358b9c4b)
	'Customer ID' calc (fld-d22a0cd4e0e848b7a0215b8c358b9c4b)

Coupons: (frm-75b20fd711814723aeec5d0b82654885)
	'Customer' from_form (fld-d8ae855a86c04fc9a5fcea5e9271fb45) toOne 'Customers' (frm-74813cf4d5e9466786d94835bb631e89)
	'Coupon ID' calc (fld-6f8298c572b34b9a970b08f5144e8052)
	'Purchases Counter' script (fld-53d2deb246c44b53b88445777c89a4d4)
	'Purchase Count' calc (fld-933cd52286e3446ea2c88d0845c60852) - calculation field missing formula!
	'Claimed' check_mark (fld-1b9172f2b462431fa75e17a38199f017)
	'Coupon Key' calc (fld-926d6814fc3a428e878acaf0305dbaf9)
		Referenced Fields: 
		 - Customers::Customer ID (frm-74813cf4d5e9466786d94835bb631e89::fld-d22a0cd4e0e848b7a0215b8c358b9c4b via fld-d8ae855a86c04fc9a5fcea5e9271fb45)
		 - Purchase Count (fld-933cd52286e3446ea2c88d0845c60852)
		 - Claimed (fld-1b9172f2b462431fa75e17a38199f017)
	'Purchases' form (fld-328c4efe13d8420198034eccca4e4841) toMany 'Purchases' (frm-1ef8c6949a634690b1a8c0ba04d4acda)

Attachments:

You must be logged in to view attached files.

[Karl M. Rowe]

Hi, Brenden!

“Did you get any syntax errors when running your script in the Script Editor?

“Make sure you don’t use curly quotes or curly apostrophes. That can cause things to fail.”

Ok. Thanks; I will be careful to use straight quotes and check that within that which I may paste into one of my test scripts do not contain curly quotes.

Peace

[Karl M. Rowe]

Hi, Sam!

Thanks for working up and posting this script for me. I especially liked what you did here:

“The ‘Customers’ form has a bunch of stuff in it. I added a photo and title field for fun then we get into the more interesting business.”

Having a photo field would be great!

“Downside of this is that it only works on the Mac.“

So it’s possible that I may encounter one or more issues since I using the Tap Forms app on the iPad, but there is much here in the code you posted to study. Thanks for attaching the link to a zip file. As long as the files don’t require a Mac, I’ll probably be able to make use them.

I would really be interested in seeing some code that deals with checkboxes. Here’s that example I had in mind again:

Let’s say in a form you had the Checkboxes A!..A3 snd Checkboxes B1..B3, and when CBB1 and/or CBB2 and/or CBB3 is/are set to “1” (checked), then the CBA1, CBA2 and CBA3 checkboxes would be reset to “0” (unchecked), and only if CBB1 was set to “1” would the corresponding CBA1 would to set to “1” and CBB1 would be set to “0”; only if CBB2 was set to “1” would the corresponding CBA2 would to set to “1” and CBB2 would be set to “0”; and only if CBB3 was set to “1” would the corresponding CBA3 would to set to “1” and CBB3 would be set to “0.”

What would such code look like? Would you use a “IF/ELSE” or a “FOR” loop to do this or something else altogether?

Thanks again, Sam.

Peace

[Sam Moffatt]

A straight forward implementation would likely be if/else but I’ve been thinking about the checkbox use case with some sort of array to do a for loop. I think it might be achievable with some introspection but it hasn’t quite come to me yet.

Everything will work on iOS except for the custom layouts. Running the scripts on iOS is a few more taps.

[Sam Moffatt]

Ok, I’m going to attach a form template for this as well but the gist is naming a bunch of checkboxes the same way and then managing their state.

Two sets of checkboxes, one named “CBA” then a number and another set named “CBB” then a number. I created 15 of the CBA and 4 of the CBB. Too easy.

The biggest challenge in this is managing all of the checkbox ID’s because clicking all of those is a pain. Step 1: Form Script to generate Javascript for later (I called this “Generate Checkbox Script” but you could call it something else):

var fields  = form.getFields();

console.log(`var cbaList = [];`);
console.log(`var cbaValues = [];`);
console.log(`var cbbList = [];`);	

for (var index = 0; index < fields.length; index++)
{
	if (fields[index].name.match(/CBA/))
	{
		console.log(`cbaList.push('${fields[index].getID()}');`);
		console.log(`cbaValues.push(record.getFieldValue('${fields[index].getID()}'));`);
	}
	
	if (fields[index].name.match(/CBB/))
	{
		console.log(`cbbList.push('${fields[index].getID()}');`);
	}
}

This is an abuse of console.log but essentially I’m using it to write Javascript code. What we’re doing is asking the form for all of it’s fields (form.getFields()) and then looking to see which ones match CBA and which ones match CBB. The CBA ones we want to track their state, so we put the field ID into a list and then also grab their value at the same time. For CBB, we don’t necessarily care about it’s values until we know that all of CBA has been ticked, so we don’t bother with the extra lines. That for my template generates this:

var cbaList = [];
var cbaValues = [];
var cbbList = [];
cbaList.push('fld-955441d1635b4ce399a4d6008c7ee37d');
cbaValues.push(record.getFieldValue('fld-955441d1635b4ce399a4d6008c7ee37d'));
cbaList.push('fld-b22653c0a9904dcd976c31e8ba0388fe');
cbaValues.push(record.getFieldValue('fld-b22653c0a9904dcd976c31e8ba0388fe'));
cbaList.push('fld-49d779446f6b42a897a11d137e1c2817');
cbaValues.push(record.getFieldValue('fld-49d779446f6b42a897a11d137e1c2817'));
cbaList.push('fld-308e12249c64487fae2237f325fbc21d');
cbaValues.push(record.getFieldValue('fld-308e12249c64487fae2237f325fbc21d'));
cbaList.push('fld-99ade950b34c4d53bd98c218dce8d8ef');
cbaValues.push(record.getFieldValue('fld-99ade950b34c4d53bd98c218dce8d8ef'));
cbaList.push('fld-617fcf4b482e44cf92eac93d4719b2c9');
cbaValues.push(record.getFieldValue('fld-617fcf4b482e44cf92eac93d4719b2c9'));
cbaList.push('fld-3c97916b19f049bbb7edf6a2c68d0e3a');
cbaValues.push(record.getFieldValue('fld-3c97916b19f049bbb7edf6a2c68d0e3a'));
cbaList.push('fld-fc1d9f241ac745119901b4e1cabaab7b');
cbaValues.push(record.getFieldValue('fld-fc1d9f241ac745119901b4e1cabaab7b'));
cbaList.push('fld-2beae294572d40178c5f31b39d9d2f3e');
cbaValues.push(record.getFieldValue('fld-2beae294572d40178c5f31b39d9d2f3e'));
cbaList.push('fld-53b3172d013343eaa510169e71cc1a15');
cbaValues.push(record.getFieldValue('fld-53b3172d013343eaa510169e71cc1a15'));
cbaList.push('fld-880186ebe46e49cea5e2fa487e0112dc');
cbaValues.push(record.getFieldValue('fld-880186ebe46e49cea5e2fa487e0112dc'));
cbaList.push('fld-3c644b46b5b64f5e80575c601ef86938');
cbaValues.push(record.getFieldValue('fld-3c644b46b5b64f5e80575c601ef86938'));
cbaList.push('fld-1c85ce9ada1643ac89eb3304fffdb730');
cbaValues.push(record.getFieldValue('fld-1c85ce9ada1643ac89eb3304fffdb730'));
cbaList.push('fld-614d0018122d4a8eab464f96a3a4a623');
cbaValues.push(record.getFieldValue('fld-614d0018122d4a8eab464f96a3a4a623'));
cbaList.push('fld-16d97ccc160b4d26b572e38c89633908');
cbaValues.push(record.getFieldValue('fld-16d97ccc160b4d26b572e38c89633908'));
cbbList.push('fld-2437b06a3d3c4646a11c093e260fe860');
cbbList.push('fld-b465ee9958c44acdab05ceb042bc582e');
cbbList.push('fld-5fa2c5b939354661b706ba35a55fabc8');
cbbList.push('fld-1748eee5b1eb42f19fab7e74431a2710');

The three lists (cbaList, cbaValues and cbbList) end up forming the basis of the next piece. I pasted that into a new field script replacing it’s contents (I called mine “Checkbox Watcher” but really you can name it what you want).

I then wrote some stuff! Let’s take it apart, piece by piece:

var setFlag = 0;
var result = 1;

Setting up our variables: setFlag is used to know if I set a field and result is used to see if all of the checkboxes have been checked.

for (var cba = 0; cba < cbaValues.length; cba++)
{
	result = result & cbaValues[cba];
}

This is a simple loop over all of the CBA field values and it does a logical AND operation on each of them against result we created before. If any of them are zero/false, then result will be false and zero out for the rest of the runs. If they’re all set to one, then they’ll all come together. This means that the script will work on an arbitrary number of checkboxes without having to know precisely how many checkboxes there are.

if (result)
{
	for (var cbb = 0; cbb < cbbList.length; cbb++)
	{
		console.log(`${cbbList[cbb]}: ${record.getFieldValue(cbbList[cbb])}`);
		if (!record.getFieldValue(cbbList[cbb]))
		{
			setFlag = 1;
			record.setFieldValue(cbbList[cbb], 1);
			document.saveAllChanges();
			break;
		}
	}
}

First bit of fun, if we have all of the checkboxes checked, look for a checkbox from CBB that hasn’t been checked and check it. When it’s checked, setFlag is toggled for the next chunk. document.saveAllChanges() is sprinkled in to make sure to tell TapForms to persist our changes.

if (setFlag)
{
	for (var cba = 0; cba < cbaList.length; cba++)
	{
		record.setFieldValue(cbaList[cba], 0, false);
	}
	document.saveAllChanges();
}

If setFlag was set, then this loops through all of the CBA checkboxes and sets them to zero. There’s a third flag to setFieldValue here which is set to false. This is a special flag that tells TapForms to *not* trigger script changes which would call this script again and cause it to loop. More saving of changes too!

document.saveAllChanges();

console.log(new Date());

console.log(`All checked? ${result}`);

console.log(`Did we set overflow? ${setFlag}`);

`${result}/${setFlag} @ ${new Date()}`;

For good measure I have a final saveAllChanges that really is there from when I first started the script. I log out some debugging messages starting with a date, if all of the checkboxes were checked, if this invocation of the script set the overflow and then it sets the script field value to a slightly formatted form for easy reviewing.

Gotchas

I didn’t test this on iOS but it should work fine there as well.

When you import the template, open up the “field script” and save it. This should force Tap Forms to set up the watching behaviour properly. For me when I did a quick test import, that didn’t work properly however editing the field and saving it worked properly.

The last gotcha I found with this was that on the Mac, the UI didn’t seem to update properly after the last checkbox was checked. Since I’m uploading this template, hopefully the keymaster can have a look and see what’s up there. If I navigate away from the record to another one and then return, the checkbox state is correct. If I click on “recalculate formulas”, it refreshes the UI properly.

Why the form script and the field script?

A fine question is why I even write Javascript to write Javascript at all, couldn’t I just run the same Javascript directly? And the answer to that question is actually yes! I could have a field script that handled the same logic as the form script and it would mostly work. The only requirement would be to press the “recalculate formulas” to trigger the field script to run. For TapForms to trigger updates to a field script automatically, it needs to know which fields to ‘watch’. TapForms has some heuristics it runs to figure that out but the simplest is looking for getFieldValue('fld-id'). The point of the form script is to write the Javascript we need to get TapForms to watch the fields (in this case the 15 checkboxes) we care about. It’s also another reason why we don’t get the values of the CBB checkboxes that way: we don’t want to get updates for these fields, so we avoid triggering that behaviour.

This particular behaviour is on the edge of magic for me. I’ve made my peace with it because it mostly works and it is easy enough to work with or around once you understand it.

Attachments:

You must be logged in to view attached files.

[Karl M. Rowe]

Hi, Sam!

I’m posting this message, not only to thank you, but to let you know how grateful I am to have read your post. I am happy to read such a post. I plan to review your post over the next few days and may be able to post a follow-up response this weekend. Thanks.

Peace

[Sam Moffatt]

No worries, hopefully it solves your problem or at least gives you some inspiration in a direction you can take.

[Author]

Posts