Thanks for this, Sam. Somehow I never saw your response until just now. Yes – it did need a script field. Brendan helped me a ton with that and just as you suggested, used the child records loop. It worked perfectly. I have a steep learning curve with this scripting! Thanks again for your help.
Are the entries in NewSLC mapped to a single Township or is a single Township linked to multiple NewSLC?
The challenge with “many” side relationships (1:M from the parent, M:M or JOIN) is that by definition there is the potential for multiple records to be linked. If more than one Township is linked, does it matter if only one record is ticked or do all records need to be linked?
If you can use a Link to Form 1:M field instead of the JOIN and there is only ever a single township linked to a NewSLC record then you can solve this using a calculation field to copy the value to make it available for a saved search.
Otherwise you need to use a script field to iterate over all of the potential records. This is actually the same pattern as the previous form post: we’d use the child records loop with the “ManyOaks” field selected. Then inside the if statement, we change // do something to be return "X";. Saving that and updating the records should include an “X” if any of the records linked have that value set.
If you use the multicolumn list view, you can enable a summary row at the bottom of it which will let you aggregate numeric columns by total (sum), min, max, average and all columns by count (count is the number of records where that field has a value IIRC).
If you want to sum by a particular field, you can create a saved search to only include those matching records and the summary will be updated.
If you’re after a report use case where you want to print out the summaries grouped by a field value, if you use a Link to Form field set to “JOIN” it’ll let you pick a form and two fields that are in common between that form and the current form. That’ll automatically pull in those records that share the common field. The table that the Link to Form field creates allows you to select the aggregation you want and then you can print those records.
If you just want the value printed and not the rest of the records, you’ll still want to create your “JOIN” field above, but you’ll have to use either a calc field or a script. To be honest the calculation field is the easiest path. Create a new calculation field and then inside of it find the “Cost” field from your “Parts” form. If you double click on it, it should automatically create a “total” or sum aggregation. Just hit save and it’ll automatically calculate the total cost for the linked fields. You’ll probably want to make sure you go to the field settings and set the number format to “currency style” and set “decimal places” to at least 2.
The field script pathway is similar except you need to select the form name in the script editor and click “ID” to insert the Link to Form field ID, you’ll need to select the “cost” field and also click “ID” to insert it’s ID and then you can use the getTotalOfLinkedFieldForField function to get the sum of that field from the link (I told you the calc field is easier). For this one though, calculation field works well.
Another newbie question.
I have created a LINKTOFORM field in a form called NewSLC to a form called township. Joined them so value in a field called Number (NO) matches in each.
What I want to do is a search for all the records that have an X (tick mark) in a field (called ManyOaks) – (screenshot is from form Township) and be able to see those records in SLC and output them in a SLC layout. I assume this needs to be done by a script in a new script field, but I need help here. But maybe there is a way do it that doesn’t use a script.
Attachments:
You must be
logged in to view attached files.
From iOS yes. From MacOS no I believe. You can copy and paste it with scripts as discussed above.
Thank you for the script examples
To clarify:
User A creates a new record that they want to share with user B via email. User A runs the first script [copy record to clipboard] and then pastes the result in an email to User B. User B then copies the output sent in the email and runs the third script [import record from clipboard]. Is this correct? Not quite sure I understand what the second script [update record from clipboard] is used for [and if user A or B is executing it].
Thanks
Ok, three form scripts: Copy_Record_To_Clipboard, Update_Record_From_Clipboard and Import_Record_From_Clipboard.
Limitations: this will likely only work for plain text note fields (no rich text) and it won’t support attachments.
Copy record to clipboard takes a record and puts it on your clipboard. Simple stuff:
function Copy_Record_To_Clipboard() {
Utils.copyTextToClipboard(JSON.stringify(record.values));
}
Copy_Record_To_Clipboard();
Update record from clipboard attempts to parse the clipboard and set the field values from the clipboard. It won’t reset fields that aren’t set in the source record so you’ll merge those fields into the current record values:
function Update_Record_From_Clipboard() {
record.setFieldValues(JSON.parse(Utils.copyTextFromClipboard()));
document.saveAllChanges();
}
Update_Record_From_Clipboard();
Final form script creates a new record and sets its data to be that of the clipboard:
function Import_Record_From_Clipboard() {
var newRecord = form.addNewRecord();
newRecord.setFieldValues(JSON.parse(Utils.copyTextFromClipboard()));
document.saveAllChanges();
}
Import_Record_From_Clipboard();
Very simple stuff, it’ll copy the JSON fields into your clipboard which you can then paste to someone else and they just run the form script to import or update on the other side. Again, caveats being plain text notes supported only and no attachment/photo stuff/signature. Just plain text.
Give that a spin and see if it sort of works for you.
You should put the files you want to import in the “Script folder access” folder (see preferences for the document) to make sure Tap Forms can get to it otherwise MacOS may block read access. If you imported that CSV via the normal TF import process previously (File > Import) then MacOS will have remembered that you selected the CSV file and give TF access to it. This is part of the security sandboxing that prevents apps from accessing files that you haven’t explicitly granted the application access. There are also some special folders that MacOS also adds extra security to which might require you to give it access via System Preferences > Security & Privacy > Privacy.
There are two types of scripts: field scripts and form scripts.
When you create a new field via the “Fields” tab and set the type to “script”, then that’s a field script. Field scripts are evaluated when ever a field they reference (via record.getFieldValue) changes and should automatically be re-evaluated. Field scripts are good for this sort of use case where you have related data that you need to store. These results of the scripts are cached with the records so that once they are evaluated, they don’t get re-evaluated unless something they depend on changes, you refresh the record (including all records) or you hit the “update records when saving” in the script editor for the field script. Ideally field scripts refer to information primarily in the record in question.
Form scripts are in the “Scripts” tab and are designed to be executed on demand. These can work on a single record to do an on demand action or can be used to work on multiple records. I use form scripts to do one of imports of data either through web services or parsing fields in a record, to do bulk imports and creation of records from external systems or to do one off data clean up work. The key is that form scripts have to be explicitly executed whilst field scripts will be implicitly updated by Tap Forms for new records or when referenced fields update.
Good to hear it’s finally working for you though and hopefully this explanation makes sense.
I just found the “update records” checkbox. It is in the Field Script Editor – I was working in the script editor under scripts, if you follow me. That checkbox is not in that script editor where I was working, as you can see in the last screenshot.
So now I’m still getting no results, but I wonder which script editor I should be using? Does it matter? I’ve tried it in each one and still getting zero results.
I don’t understand this. Now the same script is giving no results. At first it had the same result (4 names for one record) – now zero (attached). I have tried quitting out, and starting from scratch and still nada.
Is this funkiness of TF? Or maybe I did something but I don’t think so.
Also – I did try refreshing from the bottom of the list when I did have the working script. It said it was going through all the records but nothing changed.
And for the life of me I cannot find the “update records when saving” checkbox in the script editor. Aargh!
Attachments:
You must be
logged in to view attached files.
There is probably a record that doesn’t have a string value in it which is causing the split error. Updated the full script to shuffle the parseDate function, add a quick guard to return undefined when the dateString isn’t truthy and fix the expiry/issue mismatch.
// this imports the Papa Parse script
form.runScriptNamed('PapaParse');
// replace with your field ID's
// Passport
var passport_number_id = 'fld-9fe227057cdf44bfa62ad8a97cc6a62a';
var nationality_id = 'fld-0039b290b2054881ac8f004c01903c6f';
var country_id = 'fld-6e861fab76b9457bb625953cece54c96';
var date_of_issue_id = 'fld-0154d8f9ce384e708502fdd775d7bfb1';
var date_of_expiry_id = 'fld-df90736c929549cf8b863666077937fe';
var issuing_authority_id = 'fld-d03c8c1e5fe64e4dada673cb4a6ed322';
var place_passport_seen_id = 'fld-a23576f1e56b48539434de96f5afda23';
function parseDate(dateString) {
if (!dateString) {
return undefined;
}
let pieces = dateString.split("/");
return new Date(pieces[2], pieces[1] - 1, pieces[0]);
}
function Import_Passport() {
let filename = "file:///Users/victor/Desktop/Passport.csv";
let csvFile = Utils.getTextFromUrl(filename);
if (!csvFile) {
console.log("No CSV file?");
return
}
var output = Papa.parse(csvFile);
// abort if there are any errors and log to console.
if (output.errors.length > 0) {
console.log(errors.join("\n"));
return;
}
// read each line
for (let line of output.data) {
//var newRecord = document.getFormNamed("Passport").addNewRecord();
//document.getFormNamed("Other Form").addNewRecord()
let date_of_issue_string = line[3];
let date_of_expiry_string = line[4];
let date_of_issue = parseDate(date_of_issue_string); // using Sam's date parse function
let date_of_expiry = parseDate(date_of_expiry_string); // using Sam's date parse function
var newRecord = document.getFormNamed("Passport").addNewRecord();
newRecord.setFieldValues({
[passport_number_id]: line[0],
[nationality_id]: line[1],
[country_id]: line[2],
[date_of_issue_id]: date_of_issue,
[date_of_expiry_id]: date_of_expiry,
[issuing_authority_id]: line[5],
[place_passport_seen_id]: line[6],
});
document.saveAllChanges();
}
}
Import_Passport();
-
This reply was modified 3 years, 11 months ago by
Sam Moffatt. Reason: forgot to close the backtick
Yup! If you don’t want to override the values then the first script if it’s working for you will do the trick and you don’t need to worry about the other one.
There is a check box in the script editor for “Update records when saving” which will update all records in your form. The reason to keep it deselected is so it doesn’t update all records because you might save during development, it might not be the right result so you don’t want to have to wait for it to update every record before finishing. The other way is to use the refresh button at the bottom of either the record to refresh just the record or at the bottom of the list to refresh all of the records in the form. It’ll recalculate the calculation and script fields for you automatically. Once you’ve done this once, Tap Forms should keep things up to date each time you make a change but if it doesn’t for what ever reason you can just click the record refresh to force an update the to record in question (also Command+R).
Brendan, Sam,
Thank you both for the suggestions. I have incorporated what you have both suggested but I am obviously still doing something wrong. The csv file is imported and:
1. the first date (date_of_issue) is imported and formatted correctly, but
2. not the second (date_of_epxiry), which is ignored; and
3. the first date is used (date_of_issue) in the field for (date_of_epxiry).
The error message is “Date import: TypeError: undefined is not an object (evaluating ‘dateString.split’), line:(null)”
The complete script is:
// this imports the Papa Parse script
form.runScriptNamed('PapaParse');
// replace with your field ID's
// Passport
var passport_number_id = 'fld-9fe227057cdf44bfa62ad8a97cc6a62a';
var nationality_id = 'fld-0039b290b2054881ac8f004c01903c6f';
var country_id = 'fld-6e861fab76b9457bb625953cece54c96';
var date_of_issue_id = 'fld-0154d8f9ce384e708502fdd775d7bfb1';
var date_of_expiry_id = 'fld-df90736c929549cf8b863666077937fe';
var issuing_authority_id = 'fld-d03c8c1e5fe64e4dada673cb4a6ed322';
var place_passport_seen_id = 'fld-a23576f1e56b48539434de96f5afda23';
function Import_Passport() {
let filename = "file:///Users/victor/Desktop/Passport.csv";
let csvFile = Utils.getTextFromUrl(filename);
if (!csvFile) {
console.log("No CSV file?");
return
}
var output = Papa.parse(csvFile);
// abort if there are any errors and log to console.
if (output.errors.length > 0) {
console.log(errors.join("\n"));
return;
}
// read each line
for (let line of output.data) {
//var newRecord = document.getFormNamed("Passport").addNewRecord();
//document.getFormNamed("Other Form").addNewRecord()
let date_of_issue_string = line[3];
let date_of_expiry_string = line[4];
let date_of_issue = parseDate(date_of_issue_string); // using Sam's date parse function
function parseDate(dateString) {
let pieces = dateString.split("/");
return new Date(pieces[2], pieces[1] - 1, pieces[0]);
}
let date_of_expiry = parseDate(date_of_issue_string); // using Sam's date parse function
function parseDate(dateString) {
let pieces = dateString.split("/");
return new Date(pieces[2], pieces[1] - 1, pieces[0]);
}
var newRecord = document.getFormNamed("Passport").addNewRecord();
newRecord.setFieldValues({
[passport_number_id]: line[0],
[nationality_id]: line[1],
[country_id]: line[2],
[date_of_issue_id]: date_of_issue,
[date_of_expiry_id]: date_of_expiry,
[issuing_authority_id]: line[5],
[place_passport_seen_id]: line[6],
});
document.saveAllChanges();
}
}
Import_Passport()
I would very grateful for any further help.
Victor
Sorry – I’m a little dense. Yes – since I don’t think I’ll be overriding the values – just using the synonyms in the Atlas Synonyms file. So in that case I’ll just use the first script (keeping Atlas synonyms as a script field) you suggested that did return 4 values in one record? Is that Correct? Ignore the script for text field that you wrote above?
So, since the first script one is close and does return 4 results in one record – what to change next to update all the records in the entire file? I don’t understand why it only updated the single record.
