Search Results for 'script'

[Kirk Williams]

Greetings,

Apologies in advance if I’m over-complicating things here (it’s something I tend to do often lol)…

I am seeking suggestions for my initial forms structure. I’ve searched the forums extensively, but I’m having difficulty translating my situation into database-related terminology. The file I am creating is an asset inventory database consisting of items in various categories (computers, printers, devices, phones, keys, etc). The data is being migrated from GLPI (a PHP/SQL application), so it resides in a multitude of separate SQL/CSV tables. I’m fine with updating the source data in terms of translating IDs to text and restoring relationships, but I’m also looking to re-organize the inventory for use in tapforms.

Essentially, every item has a unique identifier field, referred to as “ASSET ID”. This field is a simple sequential value ranging from “PSC000001” to “PSC002013”. What I haven’t been able to figure out is how (and if) I can continue using this identifier field while using multiple forms within the file.

To elaborate: a “Computers” form would obviously have many different fields than a “Printers” form. What is the best way for me to use different forms for each, but to have BOTH forms use a single, related “Asset ID” field? I had considered taking a reverse logic approach in which all items were listed in a single form, but I’ve found that “hiding” fields that are not relevant to the device category is either not possible or is far beyond my scripting capabilities. I also feel this would negate many of the organizational benefits of using tapforms.

I’m sure the first question many of you will ask is “why is it so important to retain this Asset ID field”… There are a few reasons I can offer, and I am 100% open to alternative suggestions:

1. “Asset ID” has been used in our office for many years as the primary identifier for these items. It is incorporated into our ticketing and purchasing systems, both of which are outside of my control.

2. The Asset ID (with barcode) is also pre-printed on our inventory labels that are applied to all newly purchased items. These labels are often scanned for use with clinical software, so they’re not going away or changing format any time in the near future.

3. Obviously, it is imperative that the Asset ID is a UNIQUE value without any duplications. Less critically (but still important) is the need to ensure sequential numbering. The next new item added to our inventory should be assigned “PSC002014”, regardless of whether its a computer, a phone, or a television.

I already sense that I’m rambling, so I’ll just leave this here and cross my fingers that what I’m asking makes sense. Thanks in advance for ANY suggestions anyone may have for a way to create this file without having to re-invent the wheel!

Best,

Kirk

[Sam Moffatt]

The easiest way would be to copy the function and replace the mappings. So instead of Import_Entries() and Import_Whom() and you just change the file name, remap the field mapping and instead of form.addNewRecord() you do document.getFormNamed("Other Form").addNewRecord(). That means a bunch of duplicated code but it will work.

A more elegant solution would probably try to enable the header support and then attempt to map those across automatically. First change is to enable headers by updating the Papa.parse line to add a new header option. Then the output.data will change to be keyed by the first row field names. This means assuming the header matches your field name, otherwise you’re hard coding. So assuming the CSV header matches field names, you can then iterate over them, grab the field from TF and then update it’s value.

Here’s the full script:

function Import_Entries(formName, filename) {
	let filename = "file:///Users/victor/Desktop/" + filename;

	let csvFile = Utils.getTextFromUrl(filename);
	
	if (!csvFile) {
		console.log("No CSV file?");
		return
	}
	
	var targetForm = document.getFormNamed(formName);
	
	if (!targetForm) {
		throw new Error("Invalid form name: " + formName);
	}

	var output = Papa.parse(csvFile, { header: true });

	// abort if there are any errors and log to console.
	if (output.errors.length > 0) {
		console.log(errors.join("\n"));
		return;
	}
	
	console.log(JSON.stringify(output));

	// read each line
	for (let line of output.data) {
		var newRecord = form.addNewRecord();
		for (let fieldName in line) {
			console.log(fieldName);
			if (!line[fieldName]) {
				continue;
			}
			
			var field = targetForm.getFieldNamed(fieldName);
			if (!field) {
				console.log("Invalid field name: " + fieldName);
				continue;
			}
			
			newRecord.setFieldValue(field.getId(), line[fieldName]);
		}

		document.saveAllChanges();
	}
}

// Import entries to form "Who" from filename "Whom.csv"
Import_Entries("Who", "Whom.csv");

This is a use case I’d possibly put in my Script Manager form (and I should add Papa Parse to the repo) or some other sort of scripting form that has scripts that are agnostic of their parent form (sort of like a document script).

[Victor Warner]

Sam,

Thank you.

One question: Is it possible to import more than one .csv and put the contents of each .csv into a different form with the same script?

[Sam Moffatt]

There should be an Import_Entries() line at the bottom of the script to actually do the import. Without that line, it won’t run the function to import the data.

[Fernando DS]

I sent the script and the error. The script does nothing in the database.

Attachments:

You must be logged in to view attached files.

[Fernando DS]

No, these are two scripts. I have put it in the same page to compare. The second one works, it’s the one done last days. The first one is a copy of the second, changing the fieldid and the words to be changed. The first does not work.

[Daniel Leu]

If you delete the second function (script), does the first one work?

Cheers, Daniel

---
See https://lab.danielleu.com/tapformspro/ for scripts and tips&tricks

[Fernando DS]

The second script works. The first don’t. But why?.

[Fernando DS]

I send both scripts. They are the same, just change the fieldid and the words to be changed. Where is the mistake?

Attachments:

You must be logged in to view attached files.

[Victor Warner]

Sam,

Thank you very much for the script.

On the first run it worked – it imported the .csv file.

On subsequent runs – nothing was imported – and no error was generated.

The script as I used:

// this imports the Papa Parse script
form.runScriptNamed('PapaParse');

// replace with your field ID's

var first_name_id = 'fld-068c60aaec5047ea93f46988251ae938';
var last_name_id = 'fld-4155c0ec104e4448af17f2776880d205';
var email_id = 'fld-6a9e9e828e294e2ca694e751c7f3faf9';
var date_id = 'fld-9c739c301f614a7c8b21eb427059430e';

function Import_Entries() {
	let filename = "file:///Users/victor/Desktop/Whom.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 = form.addNewRecord();
		newRecord.setFieldValues({
			[first_name_id]: line[0],
			[last_name_id]: line[1],
			[email_id]: line[2],
			[date_id]: line[3],
		});
		document.saveAllChanges();
	}
}

The .csv file contains one line:

Simon,Adams,Noone@AtHome.com,01/02/1888

I tried changing the data but that made no difference.

I attach the database in case there is something in that which shows what the issue is.

Attachments:

You must be logged in to view attached files.

[Fernando DS]

I will look for that video. In the meantime i have been doing some tryings with the script. I want search in any other field. I though it be easy. But not at all. I have changed the fileid, and the words to be changed. The rest of the script exact the same. But I have errors. How is possible?.

[Sam Moffatt]

The first could possibly be achievable using a number of script fields to calculate the values you’re after and then you could display them. It’d be a weird use case because it’d be a form full of script fields that calculate values in other forms/searches but conceivably that could work. It’d be a non-trivial amount of work to setup.

[Sam Moffatt]

So the link in the original post has an example of Utils.getTextFromUrl, the same sort of instructions apply.

For a CSV parser, I just tried Papa Parse and it worked fine. I went to their GitHub repo and copied their uncompressed Javascript code into a new form script called “PapaParse”. I did the following quick test and it worked properly:

form.runScriptNamed('PapaParse');

function Csv_Parse_Test() {

	// Replace with your own code
	var hello_world = '"Hello, World!",Test,123'; 

	var output = Papa.parse(hello_world);

	console.log(JSON.stringify(output, null, '\t'));

}

Csv_Parse_Test();

Outputted the following:

7/10/21, 5:22:19 pm / Script Examples / CSV Parse Test
{
	"data": [
		[
			"Hello, World!",
			"Test",
			"123"
		]
	],
	"errors": [],
	"meta": {
		"delimiter": ",",
		"linebreak": "\n",
		"aborted": false,
		"truncated": false,
		"cursor": 24
	}
}

This seems to be correct interpretation of the input. So let’s put that together with the earlier post:

// this imports the Papa Parse script
form.runScriptNamed('PapaParse');

// replace with your field ID's
var title_id = 'fld-4e8e68e2979643be8417c3469015abff';
var description_id = 'fld-429934cd6ae646b1ac1cf5ad659cb926';
var url_id = 'fld-b46c858779094c9d906f2ce5e5c4a028';
var upload_date_id = 'fld-fc1f8537415c4d6cabb8c0784b64f2a6';
var thumbnail_url_id = 'fld-12b4e040711b4afea624c7c049fdd7ce';

function Import_Entries() {
	let filename = "file:///Users/yourusernamehere/Documents/input.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 = form.addNewRecord();
		newRecord.setFieldValues({
			[title_id]: line[0],
			[description_id]: line[1],
			[url_id]: line[2],
			[upload_date_id]: line[3],
			[thumbnail_url_id]: line[4]
		});
		document.saveAllChanges();
	}
}

Import_Entries();

First thing we do is pull in the Papa Parse script. Then we have the field ID’s for our fields that we’re going to use. We could put this anywhere, I like it being in global scope since they’re immutable anyway.

We enter the function and define the path to the file. If you don’t know what this is, drag the file from Finder into the script editor and it’ll put the full path in for you. Make sure this folder is the same one you’ve given access to in the Script Folder Access in the preferences for this document. The next line imports that file for you.

We then check to see if we got anything from the CSV file, if we didn’t we log an error and return flow control. We then hand the contents of the file to Papa.parse and if it has any errors we log them and return as well.

We then read each line of the data to process it, create a new record, map the field order in the CSV to our Tap Forms field ID’s and then save the changes.

You might get a performance boost moving the document.saveAllChanges() line but there are quirks around creating new records that cause me to leave the save in the loop not outside.

I’ve not tested it but something like that should work.

[Sam Moffatt]

You probably just need to delete line one entirely (the function Buscar_Y_Modificar one) . That looks like the old placeholder which you don’t need since you’ve got a function (Replace_Metal). Assuming that’s the full script then nothing else jumps out at me as wrong.

If that works, you could also change Replace_Metal with Buscar_Y_Modificar on the function line and also at the bottom of the script (e.g. change Replace_Metal(); with Buscar_Y_Modificar();).

[Fernando DS]

The script photo is going on now.

Attachments:

You must be logged in to view attached files.