Double rating value in all entries

[Andy Rawlins]

Hello

My databases were imported from Bento. I have a rating field in most of my forms which are all 0-5 stars, because that is what Bento provided. I would like to make them 0-10 stars which I can do in Tap Forms but this means changing all the existing values: I have thousands of records.

Is there a way to automatically double the ratings value in all records please? I have looked at scripts and at this forum but can’t find anything obvious that would do it. The closest I could manage was to make a new calculation field that takes the existing rating and doubles it. However, this does not allow me to enter data directly so it would not enable me to have a rating of say 7 because I can’t enter 3.5 in the original.

Many thanks

Andy

[Brendan]

Hi Andy,

You could definitely write a Form Script to do this. Or you could also use the Advanced Find & Replace function to do this. I’d make a backup of your document first whatever you do though.

With the Find & Replace method you could start with searching the Rating field for 5, then set the value to 10. Then search for 4 and set the value to 8, then search for 3 and set it to 6, then 2 setting it to 4, then 1 setting it to 2.

You have to do it in reverse as described above because if you go the other way, you’ll end up changing values you already changed.

Or here’s a Form Script you can use for it:

var rating_id = 'fld-3baff54c8e164de9b9ad3f0fbb040bc6';
var records = form.getRecords();
for (var index = 0, count = records.length; index < count; index++){
	var aRecord = records[index];
	var rating = aRecord.getFieldValue(rating_id);
	aRecord.setFieldValue(rating_id, rating * 2);
}
form.saveAllChanges();

You need to use your own rating field ID though (e.g. ‘fld-…..’). The above is specifically for my test form.

But make sure you run it only 1 time otherwise you’ll double the rating values again.

[Brendan]

By the way, to write the above script I only had to mostly just double-click on the various snippets in the Snippets list. The only thing I had to do was change the default variable name in the snippets. And this line of code I typed in: var aRecord = records[index];

[Andy Rawlins]

Great, thanks Brendan. I couldn’t get the script to work – it didn’t seem to do anything but I was probably doing it wrong. However, the find and replace worked a treat so thanks for that :)

[Brendan]

Did you use your own ID value for the rating_id field? Or did you just copy and paste and try to run the script?

Did you get any error messages in the console log?

I’m glad the other way worked for you at least.

Thanks,

Brendan

[Andy Rawlins]

In the script editor I selected the rating field and copied the ID into your script like this:

var rating_id = 'fld-0f294ce98c3043e0a9873bfe1933276e;;
var records = form.getRecords();
for (var index = 0, count = records.length; index < count; index++){
var aRecord = records[index];
var rating = aRecord.getFieldValue(rating_id);
aRecord.setFieldValue(rating_id, rating * 2);
}
form.saveAllChanges();

I didn’t see anything happen at all. Looking at it now, I left off the single quotes, have some extra characters at the beginning and an extra semicolon at the end. All these things appear in the email version of the script but not here on the website. I guess Mail has garbled it a bit. I have some other databases to do so will try it on them :)

Many thanks

Andy

[Andy Rawlins]

Hi again

When using the format from the web site it worked perfectly so thanks.

One other query, in one of my databases, to get round Bento’s 5 star rating issue, I used a simple text field for rating and input what I needed as stars and 1/2 if I wanted. So if something was 3.5 stars I would enter ***1/2. Would the script work on that and if it did, could I then change it to a proper ratings field? I’m guessing it won’t because a * is not an integer in this context.

Also, is there an undo for this script or would I need to recover from backup if it went wrong. I have about 420 records in this database so could enter them in a new field manually if necessary but if the script would work, that would be great.

Many thanks

[Brendan]

Hi Andy,

From your line var rating_id = 'fld-0f294ce98c3043e0a9873bfe1933276e;; I see that you had 2 semi-colons but no closing single-quote. That would not work.

It would have to be like var rating_id = 'fld-0f294ce98c3043e0a9873bfe1933276e';

No, a * wouldn’t work. But you could write a script to count the asterisk characters and convert that into an integer. But it would get more difficult with your “1/2” values, etc. You’d have to do some string parsing in that case.

There’s no undo. So make a backup first.

[Author]

Posts