Script to Delete Records and Import Records

[David Oxtoby]

I have a Form for which I need to delete all its records and then import them afresh from a CSV file (I use defined presets when importing Records in TapForms). The CSV file gets generated outside of TapForms, and I’m just wondering if there’s a semi-automated way of doing this via a script. Any pointers are much appreciated.

[Daniel Leu]

This script will delete all records in the current form. So that’s a bit dangerous! Use with care!

function deleteAllRecords(){

for (rec of form.getRecords()){

form.deleteRecord(rec);

}

document.saveAllChanges();

}

deleteAllRecords();

[David Oxtoby]

Thank you, that works a treat.

Can Importing being scripted do we know?

 

Thx

[Brendan]

Hi David,

You can import data from a file into Tap Forms. But you’re responsible for parsing the data and assigning it to fields yourself.

var product_info = Utils.getTextFromUrl(url);

You could use a local URL such as file:///Users/username/Desktop/products.csv

You need to set the Script Folder access setting on the General Settings screen to a folder you want to read the file from.

But it’s certainly possible.

Thanks,

Brendan

[Daniel Leu]

There’s var data = Utils.copyTextFromClipboard(); as well.

PapaParse was already used to parse csv files (see forum). So yeah, this is something that could be automated.

[David Oxtoby]

I’ve got the following script doing what I hoped, i.e. importing from a CSV file, however…. once it looks like the script has run (i.e. i can see record numbers increasing) TapForms just hangs using 99% CPU, and only thing I can do is force quit.  When I re-open the records look ok, so not sure what’s causing the hang, any pointers/ideas anyone?

 

// this imports the Papa Parse script

form.runScriptNamed(‘PapaParse’);

// replace with your field ID’s

var panelid_id = ‘fld-36efa15d8bd6410b8fe7f19d6e0b53bd’;

var panelname_id = ‘fld-2b9958a1f5e145fe929993d960ca2bd2’;

var paneldate_id = ‘fld-1c958e3560f54bb4a2069679437ffff2’;

var paneltime_id = ‘fld-5b8a1a7f74884f2c8889cf2e607b93ab’;

var paneltype_id = ‘fld-94a01f8bac044807a92665787ce31e84’;

var forenames_id = ‘fld-a7a75f6b28734d79a1b9f9f7dfd9b62e’;

var surname_id = ‘fld-761d46b9635a47c691077c0e421babe2’;

var email_id = ‘fld-39b7278d93a140738bd4d43c398200af’;

var attendeetype_id = ‘fld-3243fd4febb8442388b94ef448d51cdb’;

var diocesename_id = ‘fld-59d821b7348046fa8558a88a2eb0e04f’;

var portalref_id = ‘fld-3f7992d2c715418eb4876742c0f42cac’;

var hometel_id = ‘fld-16675cd8390a4360beb8f48a8a61cf63’;

var worktel_id = ‘fld-200cc33874da4a2caa022b245f94c874’;

var panelid2_id = ‘fld-9aef36638f2d47918ba8e4ef0ebf02d5’;

var sponsoredministry_id = ‘fld-07fe8cc64e2d49e1907358d1412676c7’;

var uniquefield_id = ‘fld-e10dc8a981164a689c1396432fe827fe’;

var season_id = ‘fld-7e019716a16a439786b5f18f4ee77e90’;

var p_lay_id = ‘fld-f33a9860109c4e2a9e337413de2f436f’;

var p_ordained_id = ‘fld-0a3fd09705a849148741e7375be67ca9’;

var mfaorpfa_id = ‘fld-6eb4defe3b0943f1ba84526dece268c4’;

var mp1or2_id = ‘fld-3cd37ee306234fc7b9fd924327889849’;

var attendeeteam_id = ‘fld-086d714f95884357b0fbcf214abb316c’;

var teamkey_id = ‘fld-800f79fc21f84ec0ac978053afe829ed’;

var name_id = ‘fld-14547469b33f4620b2196218dd29e322’;

var carousel_id = ‘fld-c97e4fc2aaac4d16a1d43dd1bf41f16d’;

var panel_id = ‘fld-f595e901f0cd4a7fbc8d6a752f048c78’;

var portalid_id = ‘fld-c1fc7ac3bf8e49be8a483d90bb1942a7’;

var panelsecsurname_id = ‘fld-fef1fa3cae61404384647ec54c9dd6a4’;

// Function to split date and ressemble (to be called later)

function parseDate(dateString) {

if (!dateString) {

return undefined;

}

let pieces = dateString.split(“/”);

return new Date(pieces[2], pieces[1] – 1, pieces[0]);

}

function Import_Entries() {

let filename = “file:///Users/davidoxtoby/Library/CloudStorage/OneDrive-ChurchofEnglandCentralServices/Documents/In progress/Reports/qryPanelData (TapForms).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) {

// date of event parsing

let paneldate_id_string = line[2];

let paneldate_new = parseDate(paneldate_id_string);

var newRecord = form.addNewRecord();

newRecord.setFieldValues({

[panelid_id]: line[0],

[panelname_id]: line[1],

[paneldate_id]: paneldate_new,

[paneltime_id]: line[3],

[paneltype_id]: line[4],

[forenames_id]: line[5],

[surname_id]: line[6],

[email_id]: line[7],

[attendeetype_id]: line[8],

[diocesename_id]: line[9],

[portalref_id]: line[10],

[hometel_id]: line[11],

[worktel_id]: line[12],

[panelid2_id]: line[13],

[sponsoredministry_id]: line[14],

[uniquefield_id]: line[15],

[season_id]: line[16],

[p_lay_id]: line[17],

[p_ordained_id]: line[18],

[mfaorpfa_id]: line[19],

[mp1or2_id]: line[20],

[attendeeteam_id]: line[21],

[teamkey_id]: line[22],

[name_id]: line[23],

[carousel_id]: line[24],

[panel_id]: line[25],

[portalid_id]: line[26],

[panelsecsurname_id]: line[27]

});

document.saveAllChanges();

}

}

Import_Entries();

[David Oxtoby]

Just to add it’s the line

document.saveAllChanges();

that seems to case TapForms to hang.

[David Oxtoby]

think i’ve fixed it by moving the saveallchanges to outside the loop

 

// Read each line

for (let line of output.data) {

// Date and time parsing

let paneldate_new = parseDate(line[2]);

let paneltime_new = parseTime(line[3]);

var newRecord = form.addNewRecord();

newRecord.setFieldValues({

[panelid_id]: line[0],

[panelname_id]: line[1],

[paneldate_id]: paneldate_new,

[paneltime_id]: paneltime_new,

[paneltype_id]: line[4],

[forenames_id]: line[5],

[surname_id]: line[6],

[email_id]: line[7],

[attendeetype_id]: line[8],

[diocesename_id]: line[9],

[portalref_id]: line[10],

[hometel_id]: line[11],

[worktel_id]: line[12],

[panelid2_id]: line[13],

[sponsoredministry_id]: line[14],

[uniquefield_id]: line[15],

[season_id]: line[16],

[p_lay_id]: line[17],

[p_ordained_id]: line[18],

[mfaorpfa_id]: line[19],

[mp1or2_id]: line[20],

[attendeeteam_id]: line[21],

[teamkey_id]: line[22],

[name_id]: line[23],

[carousel_id]: line[24],

[panel_id]: line[25],

[portalid_id]: line[26],

[panelsecsurname_id]: line[27]

});

}

document.saveAllChanges();

[Daniel Leu]

Great that you got it working! It seems to be easy to use Papa Parse!

One thing to note is the error message. console.log(errors.join(“\n”)); should be console.log(output.errors.join(“\n”));.

[Sam Moffatt]

Having the save all changes outside of the loop commits all of the changes at once and makes it more efficient. Inside the loop slows things down because it commits everything for each row.

There is also a progress bar control you can use to communicate where you’re at with things which is useful for an import style use case like this.

[David Oxtoby]

Daniel Leu wrote:

Great that you got it working! It seems to be easy to use Papa Parse! One thing to note is the error message. console.log(errors.join(“\n”)); should be console.log(output.errors.join(“\n”));.

Ah, thx for spotting that

[David Oxtoby]

Sam Moffatt wrote:

Having the save all changes outside of the loop commits all of the changes at once and makes it more efficient. Inside the loop slows things down because it commits everything for each row. There is also a progress bar control you can use to communicate where you’re at with things which is useful for an import style use case like this.

Thx for the idea, I found some posts about the progress bar and have now implemented also, I’ll include full code below in case helpful for others searching….

 

// Import the Papa Parse script

form.runScriptNamed(‘PapaParse’);

// Replace with field IDs

var panelid_id = ‘fld-36efa15d8bd6410b8fe7f19d6e0b53bd’;

var panelname_id = ‘fld-2b9958a1f5e145fe929993d960ca2bd2’;

var paneldate_id = ‘fld-1c958e3560f54bb4a2069679437ffff2’;

var paneltime_id = ‘fld-5b8a1a7f74884f2c8889cf2e607b93ab’;

var paneltype_id = ‘fld-94a01f8bac044807a92665787ce31e84’;

var forenames_id = ‘fld-a7a75f6b28734d79a1b9f9f7dfd9b62e’;

var surname_id = ‘fld-761d46b9635a47c691077c0e421babe2’;

var email_id = ‘fld-39b7278d93a140738bd4d43c398200af’;

var attendeetype_id = ‘fld-3243fd4febb8442388b94ef448d51cdb’;

var diocesename_id = ‘fld-59d821b7348046fa8558a88a2eb0e04f’;

var portalref_id = ‘fld-3f7992d2c715418eb4876742c0f42cac’;

var hometel_id = ‘fld-16675cd8390a4360beb8f48a8a61cf63’;

var worktel_id = ‘fld-200cc33874da4a2caa022b245f94c874’;

var panelid2_id = ‘fld-9aef36638f2d47918ba8e4ef0ebf02d5’;

var sponsoredministry_id = ‘fld-07fe8cc64e2d49e1907358d1412676c7’;

var uniquefield_id = ‘fld-e10dc8a981164a689c1396432fe827fe’;

var season_id = ‘fld-7e019716a16a439786b5f18f4ee77e90’;

var p_lay_id = ‘fld-f33a9860109c4e2a9e337413de2f436f’;

var p_ordained_id = ‘fld-0a3fd09705a849148741e7375be67ca9’;

var mfaorpfa_id = ‘fld-6eb4defe3b0943f1ba84526dece268c4’;

var mp1or2_id = ‘fld-3cd37ee306234fc7b9fd924327889849’;

var attendeeteam_id = ‘fld-086d714f95884357b0fbcf214abb316c’;

var teamkey_id = ‘fld-800f79fc21f84ec0ac978053afe829ed’;

var name_id = ‘fld-14547469b33f4620b2196218dd29e322’;

var carousel_id = ‘fld-c97e4fc2aaac4d16a1d43dd1bf41f16d’;

var panel_id = ‘fld-f595e901f0cd4a7fbc8d6a752f048c78’;

var portalid_id = ‘fld-c1fc7ac3bf8e49be8a483d90bb1942a7’;

var panelsecsurname_id = ‘fld-fef1fa3cae61404384647ec54c9dd6a4’;

// Function to delete all records

function deleteAllRecords(){

for (rec of form.getRecords()){

form.deleteRecord(rec);

}

document.saveAllChanges();

}

// Function to split date and parse it

function parseDate(dateString) {

if (!dateString) {

return undefined;

}

let pieces = dateString.split(“/”);

return new Date(pieces[2], pieces[1] – 1, pieces[0]);

}

// Function to parse time in the format hh:mm

function parseTime(timeString) {

if (!timeString) {

return undefined;

}

let pieces = timeString.split(“:”);

let date = new Date();

date.setHours(pieces[0], pieces[1], 0, 0);

return date;

}

// Function to import entries

function Import_Entries() {

let filename = “file:///Users/davidoxtoby/Library/CloudStorage/OneDrive-ChurchofEnglandCentralServices/Documents/In progress/Reports/qryPanelData (TapForms).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(output.errors.join(“\n”));

return;

}

// Initialize progress indicator

var progress = Progress.new();

progress.totalCount = output.data.length;

progress.currentCount = 1;

progress.show(‘Importing Data…’);

// Read each line

for (let index in output.data) {

let line = output.data[index];

if (progress.cancelled) {

console.log(‘Cancelled operation’);

break;

}

// Date and time parsing

let paneldate_new = parseDate(line[2]);

let paneltime_new = parseTime(line[3]);

var newRecord = form.addNewRecord();

newRecord.setFieldValues({

[panelid_id]: line[0],

[panelname_id]: line[1],

[paneldate_id]: paneldate_new,

[paneltime_id]: paneltime_new,

[paneltype_id]: line[4],

[forenames_id]: line[5],

[surname_id]: line[6],

[email_id]: line[7],

[attendeetype_id]: line[8],

[diocesename_id]: line[9],

[portalref_id]: line[10],

[hometel_id]: line[11],

[worktel_id]: line[12],

[panelid2_id]: line[13],

[sponsoredministry_id]: line[14],

[uniquefield_id]: line[15],

[season_id]: line[16],

[p_lay_id]: line[17],

[p_ordained_id]: line[18],

[mfaorpfa_id]: line[19],

[mp1or2_id]: line[20],

[attendeeteam_id]: line[21],

[teamkey_id]: line[22],

[name_id]: line[23],

[carousel_id]: line[24],

[panel_id]: line[25],

[portalid_id]: line[26],

[panelsecsurname_id]: line[27]

});

// Update the progress indicator

progress.updateProgress(index);

}

// Dismiss the progress indicator

progress.dismissProgress();

// Save changes

document.saveAllChanges();

Utils.alertWithMessage(“Import Successful”, “The data delete & import has been completed successfully.”, “info”);

}

// Delete all records before importing

deleteAllRecords();

// Start the import process

Import_Entries();

[JScottA]

Nice thread and useful, too! Out of curiosity, I ran the script through the TF5 Assistant and got the following response. Any script-pros out there want to comment on the good and bad for the query I had for suggestions on making the above script (great work, btw) “better”?

Improving the script can be approached from several angles: code organization, error handling, efficiency, and flexibility. Here are some suggestions to enhance the script:

### 1. **Error Handling Improvements**:
– **Enhanced Error Logging**: Extend the error handling to log which line caused the error and possibly skip the faulty line rather than aborting the entire import.
`javascript
if (output.errors.length > 0) {
output.errors.forEach(error => {
console.log(Error at line ${error.row}: ${error.message});
});
return;
}
`
– **Field Existence Check**: Before setting field values, check if the field exists in the form to prevent runtime errors if the field is missing.
`javascript
if (form.getFieldWithId(panelid_id)) {
newRecord.setFieldValue(panelid_id, line[0]);
} else {
console.log(Field with ID ${panelid_id} does not exist.);
}
`

### 2. **Code Modularity**:
– **Modular Functions**: Break down the script into smaller, reusable functions to handle tasks like creating a new record, setting field values, and parsing different data types. This will make the script easier to maintain and extend.
`javascript
function setFieldValue(record, fieldId, value) {
if (form.getFieldWithId(fieldId)) {
record.setFieldValue(fieldId, value);
} else {
console.log(Field with ID ${fieldId} not found.);
}
}

function createNewRecord(data) {
let record = form.addNewRecord();
setFieldValue(record, panelid_id, data[0]);
setFieldValue(record, panelname_id, data[1]);
// Continue for other fields…
return record;
}
`
– **Parameterization**: Consider passing parameters to functions rather than relying on global variables. This makes the functions more reusable.

### 3. **Configuration and Flexibility**:
– **Configurable Field Mappings**: Instead of hardcoding the field IDs, store them in a configuration object. This allows for easier updates and the possibility of reusing the script with different forms.
`javascript
const fieldMappings = {
panelid: ‘fld-36efa15d8bd6410b8fe7f19d6e0b53bd’,
panelname: ‘fld-2b9958a1f5e145fe929993d960ca2bd2’,
// Add other mappings here…
};
`
– **Dynamic File Path**: Use a file picker dialog to select the CSV file dynamically, making the script more flexible and user-friendly.
`javascript
let filename = Utils.getFilePathFromDialog();
if (!filename) {
console.log(“No file selected.”);
return;
}
`

### 4. **Progress Indicator Enhancements**:
– **More Informative Progress**: Instead of just updating the progress indicator, display which record is currently being processed, especially useful for large imports.
`javascript
progress.show(Importing Data... (${index + 1}/${output.data.length}));
`

### 5. **Improving Efficiency**:
– **Batch Record Updates**: If Tap Forms supports it, consider updating records in batches rather than one at a time. This could improve performance, especially with large datasets.
– **Early Exit on Cancel**: The script currently checks progress.cancelled in the loop. Consider exiting the loop immediately if the operation is canceled to save processing time.

### 6. **User Feedback**:
– **User Confirmation Before Deletion**: Ask for user confirmation before deleting all records, to prevent accidental data loss.
`javascript
if (!Utils.confirmWithMessage(“Are you sure you want to delete all records?”, “Confirmation”)) {
return;
}
`
– **Detailed Completion Message**: Include the number of records processed in the completion message.
`javascript
Utils.alertWithMessage(“Import Successful”, The data delete & import has been completed successfully. ${output.data.length} records processed., “info”);
`

### 7. **Documentation and Comments**:
– **Add Comments**: Enhance the script with detailed comments explaining each section of the code, which will be helpful for anyone who needs to maintain or update the script in the future.
`javascript
// Parse date in dd/mm/yyyy format to JavaScript Date object
function parseDate(dateString) {
if (!dateString) {
return undefined;
}
let pieces = dateString.split(“/”);
return new Date(pieces[2], pieces[1] – 1, pieces[0]);
}
`

Implementing these improvements can make the script more robust, flexible, and easier to maintain, providing a better user experience while reducing the likelihood of errors.

[David Oxtoby]

lots of really helpful suggestions in the code, but I only needed something easy and quick for my own use, but agree if I was making this more widely available to a wider team i’d need to work the code better, but sadly I’ve always been a cable-tie and gaffa-tape person, than an true engineer!!

 

[David Oxtoby]

BTW what is the TF5 Assistant? i’m guessing a ChatGPT type thing?

 

[JScottA]

Hi, David! I also use TF5…obviously. I do a lot of work with various “AI” technologies. So I created a custom GPT (on the GPT Store, if you have a paid GPT account) to assist myself and hopefully others in using TF5 better. I’ve found it very useful and has dramatically cut down on my looking for help on this forum and other places. I sometimes will select TF5 user’s posts here to run through the GPT because it helps me to understand TF5 better and it helps train (very small, but it helps) the GPT to better answer future questions. Plus, sometimes I have to tune the prompting so that I can continue to get good-quality output from the chatbot. The *official* name of the custom GPT is “Tap Forms 5 Assistant”. There is no additional cost and I can’t see any conversations other than my own.

[Author]

Posts