Dates are always a pain. Javascript wants your dates to be in a particular non-ambiguous format when parsing them.
Personally I’d actually suggest landing it in a normal text field so that you have the original value and then using a script field to parse the value of the field into a date field.
For a parsing script for US style dates, it looks like this:
function Date_Parser() {
var date_import = record.getFieldValue('fld-3e35e177e6174b139d3c4c8c2eea08d0');
var date_parsed = record.getFieldValue('fld-96b53c5f607e4e7286fad5448ceae477');
// if the date_parsed field is set, don't reset it.
if (date_parsed) {
return;
}
var pieces = date_import.split("/");
var parsedDate = new Date(pieces[2], pieces[0] - 1, pieces[1]);
record.setFieldValue('fld-96b53c5f607e4e7286fad5448ceae477', parsedDate);
document.saveAllChanges();
return parsedDate;
}
Date_Parser();
Brendan
Thank you for the response. Apologies in advance, but given my basic (almost non-existent) understanding of JavaScript, how do I incorporate into the script?
// 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';
function Import_Passport() {
let filename = "file:///Users/victor/Desktop/Passport - test.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()
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]: line[3],
[date_of_expiry_id]: line[4],
[issuing_authority_id]: line[5],
});
document.saveAllChanges();
}
}
Import_Passport()
Sam Moffat provided the code to import a CSV file into a Form: https://www.tapforms.com/forums/forum/script-talk/.
I have adapted the script but dates are not being imported. The dates are in the format: “01/01/2020” (day/month/year). The data is imported, but the dates are skipped.
The database / forms are attached and the csv file.
Is it necessary to add something to the Javascript to get the dates to import?
Attachments:
You must be
logged in to view attached files.
Oh good catch, let me update the video description as well. I used Google to grab the link and didn’t realise it was the older version.
Hi Jean-Christophe,
You would just set the all_day parameter to false and set the Date to have a specific time.
https://www.w3schools.com/jsref/jsref_sethours.asp
function Add_And_Update_Event() {
var event_info = {"calendar_name" : "Work",
"title" : "Test Event 1",
"location" : "Big Ben, London, UK",
"notes" : "This is a note",
"url" : "https://www.tapforms.com",
"all_day" : false};
var start_date = new Date();
// Set the time to 15:30:00
start_date.setHours(15, 30, 0);
// Create end Date instance 1 hour later
var end_date = new Date();
end_date.setHours(16, 30, 0);
var identifier = Utils.addToCalendar(event_info, start_date, end_date);
console.log(identifier);
event_info["title"] = "Hello Event 2";
Utils.updateCalendarEvent(identifier, event_info, start_date, end_date);
}
Add_And_Update_Event();
You can copy this code into a new Form Script to test it out.
Hello,
I would like to add/update an event with hour (not all day) from two fields, one for the date, one for the hour.
I’m new in scripting and I can’t find an example with hour included.
Thanks for your help !
On Twitter there was an ask to have a field decrease in value based on the action in another form. I use scripting and model a transaction log style interface to not only keep balances but record the transactions as they happen for accountability (where did my yarn balls go?).
In Part 1 I work through the initial structure and layout of the forms as well as creating our first script to set the balance for a yarn ball. Part 2 covers creating a “Library” script and the work needed to handle adding and removing entries whilst maintaining the transaction log. This then gets applied to the “products” form so that we can see how to use the bill of materials to reduce the balances whilst maintaining a log of what happened.
Attached is an export of the Yarn Balls form template which should contain the forms, fields and scripts covered in the videos.
If there is interest I can go through and do a third part with some quality of life improvements, adding some extra validation and potentially creating another form with produced items automatically linked together.
Attachments:
You must be
logged in to view attached files.
Hi Yvette,
Tap Forms doesn’t have the option to show or hide a field based on some condition. At least not on a per-record basis. A field script could hide or show another field, but it would show or hide for every record, so not very useful in this scenario.
Your best bet is to just have the Other option and then another field below it that asks for the Other Location.
Purchase date to price to person doesn’t quite make sense to me but purchase date to person with price aggregated makes more sense, is that what you mean?
You have two options that you can do, one is to start building a composite key with each of the fields you want as commas, that gives you a flat list but if it’s in CSV outputting it looks somewhat transparent. The other is to continue to add another array but that means a bunch of work to validate it as we’ve got (basically the process we did to add marketplace).
I’ll do the first one because it lets you store an arbitrary number of roll ups at the cost of making it a little harder to extract later. Since you’re not using the roll up form and just the CSV this should be ok for you:
Adding extra fields should be a matter of creating the variable and adding them to the rollupKey line. This should work though I’ve not tested it:
function Aggregate_By_Date() {
// set our date format, see https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date/toLocaleDateString
// remove "day" from it and you'll roll up by month for example.
var dateFormat = { "day": "2-digit", "month": "2-digit", "year": "2-digit"};
// this is where we're going to store the rollups per day.
var rollups = {};
// iterate to all of the records in the form
for (var rec of form.getRecords()) {
var marketplace = rec.getFieldValue('fld-c163aba17ae64c4d93b5a53819a139dc');
var person = rec.getFieldValue('fld-personid');
var purchase_date = rec.getFieldValue('fld-ccbd9a8f51d34246bebfb31aa4e397dd');
var price = parseFloat(rec.getFieldValue('fld-08129d71ab0f4fa4a2749456281fca07'));
// Skip entries that don't have a price or date set.
if (!price || !purchase_date) {
continue;
}
// format the date for use in the rollup.
var formattedDate = purchase_date.toLocaleDateString("en-AU", dateFormat);
var rollupKey = [formattedDate, marketplace, person].join(", ");
// Rollup to this key, add to the existing value or set it if not set.
if (!rollups[rollupKey]) {
rollups[rollupKey] = price;
} else {
rollups[rollupKey] += price;
}
}
// log to console the aggregated values.
for (var rollupKey in rollups) {
console.log(rollupKey + ",$" + rollups[rollupKey]);
}
}
Aggregate_By_Date();
Hey there. Me again. SOrry to bother you. I’ve been using this script since you wrote it.
I would like to add some data to the rollups, in order to manipulate the data in excel and make charts.
I would like to add 2 more variables to the rollups, so it would be like this:
Purchase date – price – person.
I Have declared the variable person, but i am lost with the rollups. Any help?
Thank you very much indeed =)
I don’t think out of the box there is anything that quite does this in Tap Forms. The built in barcode scanner feature works on searching for records so I don’t think that’s a good fit for your use case and there isn’t a scripting/prompter interface to handle this.
For me personally how I work around this is that I have a hand held USB/Bluetooth barcode scanner. That acts like a keyboard except it scans barcodes to “type” the text. I then use form script with a prompter to give me a text box and can scan barcodes to search then popup the answer.
Awesome to hear you got a flow that worked for you! For something like 20 SMS that’s probably the best balance of automation and cost. Also great to hear the videos were helpful to you as well.
@Brendan the API uses a POST so it’d be a Utils.postContentToUrlWithHeaders perhaps (content type is a header so no need to map that). Though maybe a more generic sendHttpRequest(method, url, headers, body) might make more sense? If it returned an object with the headers and body then it’d be easier to handle in Javascript as well. That would mean I could also do DELETE or PUT requests too.
I need some help, I want to make an inventory where I scan a barcode search for the item and return a separate field result in my form. So I have a table of items that has a bar code. I scan the barcode and search for the record. Then in return a field showing a number listed.
Hi Sam & Brendan, thanks for your feedbacks but … I need to send about 20 SMS a month, and I’m a real good newbie in scripting, API, aso … (But, great thanks to Sam for his videos, I’ve transferred values to a child form successfully this morning, so happy !)
So without a simple way to do that SMS stuff, gonna stay with this good ol’ copy-paste !
Have a nice day
You could use a form script to send an SMS using a web API gateway like Twilio. Twilio’s API is accessible via HTTP so I think Tap Forms’ Javascript API should be sufficient to make the requests.
