Search Results for 'script'

[Daniel Leu]

RE #2, if you implement your formula using a field script, then you have more control on the return value. There you can return an empty string instead of a 0.

Cheers, Daniel

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

[Eric de Mildt]

Just new to Tap Forms, after using Filemaker for 25 years for small-business-accounting (self-employed free-lancer).
As Filemaker is getting too expensive, and its iCloud-sync a real mess, I found Tap Forms just as good for my needs. And a really clear and flexible app to work with. Quite astonishing considering the price-tag and the fact it is written by (am I right?) just one person.

Recreating (and improving) my old “system” was quite easy, I’d nearly say “a pleasure” ;-)
I did have to spend a couple of days figuring out new ways for some things, and when and how to use the scripts, but after that importing 20+ years of Filemaker-data went nearly flawless.

That’s why (now I start nagging ;-) I’m a bit disappointed by the lack of several features (and a little mistake) :

– Custom Lay-outs on iPhone version ( As I’m on the road all the time, lack of this makes Tap Form a bit “unusable” for me as I can’t print and send on the go the most important thing: invoices and reminders ;-)
I realize it’s not easy to implement but really would make Tap Forms perfect (for me at least ;-)

– You have to set a default value for empty fields in a formula in order to get a result for a Calculation field. You can’t leave a field empty.
Could there be a solution for this? (at least a possibility to Not Print Empty Fields for example? I had a look at script, did I miss something ?

– And a little “mistake”: the Euro-symbol gets placed behind the number if Field/Currency setting is to Euro, and if OS system’s Region is set to, for example France or Belgium. (This OS Region setting needs to be correct for other apps).
But in real life, we also put € before the number. So it’d be great to have that possibility.

Anyway, keep up the good work!
Eric

[Daniel Leu]

Brent, great to hear that it worked, finally!

Is there a way you can select the 1200 file records with the advanced search? If that’s the case you can select the saved search and just run the script with a little change:

Before:
for (contract of contractsForm.getRecords()){

After:
for (contract of search.getRecords()){

It is strange to get the ’empty records’ notice although there should be a file associated with every record. That’s something I would look into.

Fetching the file record might be what takes a bit of time. If you run this script frequently, it might be worth a try to save the PDF filename in a field and then just use this field in the comparison.

Oh, don’t forget to backup your database, specially with so many entries!

I might add a little sanity check as well to verify that a record was really found:

		var success = 0;
		// Loop over all contract records
		for (contract of contractsForm.getRecords()){
			if (!contract.getFieldValue(pdf_id) || contract.getFieldValue(pdf_id).length == 0) {
				console.log('Record is missing PDF: ' + contract.getUrl());
				continue;
			}

			// this assumes that there is only one PDF per contract!
			var pdfName = contract.getFieldValue(pdf_id)[0].filename;
			
			if (csvPdfName == pdfName){
				console.log("Found match: " + pdfName);
				success = 1;
				
				// Update contract record
				addKeyword(contract, keyword_id, entry.getFieldValue(csvKeyword_id));
			
				break;
			}
		
		}
		if (!success) {
			console.log("No match found: " + csvPdfName, "#FF0000");
		}

Cheers, Daniel

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

[Brent S]

Daniel and Sam,

Thank you! Thank you! Thank you!

Success!!

Like Daniel mentioned in his original response, the process is a bit tedious. Indeed this is true, took my very fast computer (new iMac with 64G RAM) about 6 hours to complete this task. I was expecting about 6 minutes. So, I am going to need to find a more efficient way to do this. It might involve putting the full text of the PDF documents into a text field so that Tap Forms can just use its own search capability to pluck out keywords associated with a document within a record. (I also have full versions of all the contracts in HTML format which may be easier).

It may also be there is a more efficient way the code could be written because I think this went though all 35,747 files in the main database rather than looking at the approximately 1200 files I wanted to tag. This was because I noticed I was getting many “empty record” notices in the console when I know for a fact each entry in the database has a PDF file in it.

In any case, I am LOVING Tap Forms and looking forward to learning how to use these built in snippets along with customized scripts. Thank you Tap Forms community.

[Detlev Bonkamp]

Hello Sam,
thank you for your details information. Next weekend, it think, there will be enough time for me to create the forms an scripts like you showed it.

Greeting Detlev.

By the way, you pronounced my name in your video absolut correctly :-)

[Brent S]

Hi Sam,

Thanks. Almost there. So close I can taste it. See error message attached.

I thought perhaps I am missing one line that starts with something like:

var csvPdfName = csv.getFieldValue(csvPdf_id)[0].filename;

However, I tried this and it did not work and just gave me another message “Category Script: ReferenceError: Can’t find variable: csv, line:(null)

With respect to your question, there are 35,747 records. Each has one PDF associated with it.

Cheers,
Brent

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

I’d also to check that there is a file attachment and skip ones that don’t:

		// Loop over all contract records
		for (contract of contractsForm.getRecords()){
			// this assumes that there is only one PDF per recipe!
			var pdfName = contract.getFieldValue(pdf_id)[0].filename;

to:

		// Loop over all contract records
		for (contract of contractsForm.getRecords()){
			if (!contract.getFieldValue(pdf_id) || contract.getFieldValue(pdf_id).length == 0) {
				console.log('Record is missing PDF: ' + contract.getUrl());
				continue;
			}
			// this assumes that there is only one PDF per recipe!
			var pdfName = contract.getFieldValue(pdf_id)[0].filename;

That should check there is an attachment and validate it’s not set plus log a message with a clickable link in the console. If that ends up being everything then that’s not great but it wouldn’t bomb part way through.

There are 35,747 entries (PDF’s), so it is only one field, but there are many entries.

Many records or a single record with multiple PDFs?

Re feedback: The last few videos hit half an hour and I need to work on getting the content back in the 10 minute range. I ended up throwing a bunch of stuff in one of the videos which when I went back should have probably ended up broken out into a few videos (ended up putting in chapter links for at least one of them). I’m trying to keep them as continuous takes to make sure I don’t miss any steps along the way though sometimes that leads to mild confusion as it’s also not scripted/practiced. Sometimes seeing something small and seemingly unimportant is the hook you need to understand. Thanks for the feedback!

[Daniel Leu]

In hindsight, it would have been better if you had described your problem as is and not made up this hypothetical recipe case….

As you might have noticed, all CSV form related fields use the a csv prefix to make it clear where this field resided. And yes, my script is a from script that is part of the contracts form. My assumption was that the CSV form is deleted after use and recreated with a new tagging set. So it makes sense that the script has a more permanent location.

Here is the updated script. Hope this works.

function addKeyword(rec, kw_id, kw){
	var keywords = rec.getFieldValue(kw_id);
	
	if (keywords == undefined || keywords == ""){
		rec.setFieldValue(kw_id, kw);
	} else {
		rec.setFieldValue(kw_id, keywords + ',' + kw);
	}
}

function Update_Records() {

	var csvForm = document.getFormNamed("csv");
	var contractsForm = document.getFormNamed("Contracts");
	
	// recipes
	var keyword_id = 'fld-a2126cbe512646e9ba144fb5a90b06dc';
	var pdf_id = 'fld-3ea77ccc18604174b722ece105965c44';

	// get csv field Ids
//	var csvPdf_id = csvForm.getFieldNamed('PDF').getId();
//	var csvKeyword_id = cvsForm.getFieldNamed('Keyword').getId();

	// get use fixed field Ids!!!!!!!!!!!!
	var csvPdf_id = 'fld-0cbd22a473cd4c58aa1dc47341a7157b';
	var csvKeyword_id = 'fld-53de1c4c633a4da6a4d0213158b5ef0a';

	// Loop over all csv entries
	for (entry of cvsForm.getRecords()){
		// get pdf file name of CVS record
		var cvsPdfName = entry.getFieldValue(csvPdf_id);

		// replace spaces with underscores
		cvsPdfName = cvsPdfName.replace('/ /g', '_');
		console.log("Processing: " + cvsPdfName);
		
		// Loop over all contract records
		for (contract of contractsForm.getRecords()){
			// this assumes that there is only one PDF per recipe!
			var pdfName = contract.getFieldValue(pdf_id)[0].filename;
			
			if (cvsPdfName == pdfName){
				console.log("Found match: " + pdfName);
				
				// Update contract record
				addKeyword(contract, keyword_id, entry.getFieldValue(csvKeyword_id));
			
				break;
			}
		
		}
		document.saveAllChanges();
	
	}
	
	return 'done';
}

Update_Records();

Cheers, Daniel

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

[Brent S]

Hi Sam,

Thank you for the help and feedback. I am getting close to getting it to work, but still a bit shaky in terms of my understanding of things. But first things first, let me answer your questions:

Curious for feedback, were any of them helpful?

Yes. Absolutely! Thank you for making them. The original “Creating a Licensing Database” was great. One of the other videos on Scripting (Deep Dive) helped me make sense of the fld-hash codes, as I had no idea how Daniel had come up with that until I watched your video.

What was not helpful and what would improve it?
I like when videos can compress content in 5-10 minutes. Better to break up videos into smaller digestible chunks and not to try to cover too many topics (IMHO)

What was helpful and how was it helpful?

I really like how you went through the Javascript Code line by line to explain how the computer is interpreting the code.

I had thought about doing another simple intro one on the next few snippets which includes functions but haven’t done it. I tried another video on some other topics but wasn’t happy with how it turned out.

This would be very helpful. For me, an example where you had two very simple forms and you wanted to use a particular Snippet like “Set Field Value”. Ideally the video would explain what you are trying to do, why you are trying to do it, write the code and run the Snippet, show the effect that it had. Ideally the length of the video would be approximate 5 min. For a beginner like me I need to see a very basic version of something working so that I can alter it for my own use. Once I get the simple version working, hopefully then I can build on that for things that are more complex.

There is your feedback : ). And thanks again for making the videos.

Now, a small request for additional help…..

I have not been able to debug the script that I have to get it to work. I am wondering if you can help me vastly simplify the script just to doing one or two things instead of about 4 things at the same time. Basically I would like to start over. So, as I said, I only have two forms. One is my main database called “Contracts” which has thousands of unique PDF’s attached in a file attachment field. The second form (which I named csv) is a sub-list of those PDFs (about 1200 of 36000) that I want to tag with the the word “vaccine”.

Therefore, I need a script that will take my small list of 1200 pdfs, find the corresponding/matching file in the larger Contracts database, and insert an entry “vaccine” into the field “Agreement Keywords”. Seems like this should be simple.

If I can just accomplish that then I can change the code later to take action in other fields.

Key information

The location of the field I need to access in the contracts database:
var pdffilepath_7_id = ‘fld-3ea77ccc18604174b722ece105965c44’;
var agreement_keywords_id = ‘fld-a2126cbe512646e9ba144fb5a90b06dc’;

The location of the data in the csv database:
var keyword_id = ‘fld-53de1c4c633a4da6a4d0213158b5ef0a’;
var pdf_id = ‘fld-0cbd22a473cd4c58aa1dc47341a7157b’;

I think this ability to have scripts auto-populate fields is critically important to anyone like me that has a massive database of documents that are unstructured and need to be tagged and categorized within Tapforms.

Thanks,
Brent

[Brendan]

Hello Winfried,

The Table field is only available to the form that you put it on.

If what you need is a Shopping List form that links to ingredients, then you’ll have to create a separate Ingredients form that you link to from your Shopping List form AND from your Recipes form.

Or alternatively, skip the Shopping List form and just have your Recipes form and an Ingredients form. Then just visit the Ingredients form whenever you want to go shopping. Put your Checkmark field as your first field in the form and when you do that, on iOS, you’ll see a checkmark button to the left of the ingredient name. Tapping on it will check it on or off. Also, if you sort by the Checkmark field then you could have all the unchecked items at the top and all the checked items at the bottom. So as soon as you’ve bought an ingredient, tap on the checkmark and it will move down to the list of checked items.

But then you’d want to have a way of resetting all of the checkmark fields back to unchecked before you go on your next shopping excursion. The only way to do this is to either manually uncheck the items you want to buy, or write a Form Script that un-checks them all for you in one go.

Thanks!

Brendan

[Sam Moffatt]

A few more questions if I may. I have tried to figure things out by watching some of Sam’s videos etc., but I am a little stuck.

Curious for feedback, were any of them helpful? What was not helpful and what would improve it? What was helpful and how was it helpful? I had thought about doing another simple intro one on the next few snippets which includes functions but haven’t done it. I tried another video on some other topics but wasn’t happy with how it turned out.

So first, from my reading it looks like I need to use a “Form Level Script” as opposed to a “Field Level Script” as I am updating existing records.

I think my rule of thumb is that field scripts are meant to act on the contents of the record as those contents change whilst form scripts are more one off or bulk action changes. You can use them somewhat interchangeably and the question is do you want this thing to run all of the time or is it something you want to control when it runs?

I only have two forms, my main database and the cvs data I want to input. Is it correct that I must place the Form Level Script in my main database and not in the cvs form? (That seems to be the only way I can get the correct fld-hash numbers that I need into the script).

While it might not be intuitive you can access any form and any field from scripts (fields or forms) in any form within a document. The editor won’t show you the fields that aren’t directly linked to the form that you’re in but you can use the editor to go to other forms and get their field ID’s. You can also get the field ID from below the description box in the field editor UI.

You can use the code button to wrap your code with backticks (e.g. `code here`) to make it a little easier to read (also ran it through a formatter since the post didn’t have any indentation):

function addKeyword(rec, kw_id, kw) {

	var keywords = rec.getFieldValue(kw_id);

	if (keywords == undefined || keywords == "") {
		rec.setFieldValue(kw_id, kw);
	} else {
		rec.setFieldValue(kw_id, keywords + ',' + kw);
	}
}

function Update_Records() {

	var csvForm = document.getFormNamed("csv");
	var ContractsForm = document.getFormNamed("Contracts");

	// Contracts
	var pdffilepath_7_id = 'fld-3ea77ccc18604174b722ece105965c44';
	var license_category_id = 'fld-d63ab1856e554efebe62b9f1826c388d';
	var license_subcategory_id = 'fld-f0c47c7e6ad244fe955e2c24cb448590';
	var agreement_keywords_id = 'fld-a2126cbe512646e9ba144fb5a90b06dc';

	// get csv field Ids
	var pdffilepath_7 = cvsForm.getFieldNamed('PDF').getId();
	var license_category = cvsForm.getFieldNamed('Category').getId(); var license_subcategory = cvsForm.getFieldNamed('Subcategory').getId();
	var agreement_keywords = cvsForm.getFieldNamed('Keyword').getId();

	// Loop over all csv entries
	// So this tells my Contracts database to look at the cvs form?
	for (entry of cvsForm.getRecords()) {

		// get pdf file name
		// is it looking for another fld-hash here?
		var pdfName = entry.getFieldValue(csvPdf_id);

		// replace spaces with underscores
		pdfName = pdfName.replace('/ /g', '_');
		console.log("Processing: " + pdfName);

		// Loop over all LICENSEdb records
		for (Contracts of Contracts.getRecords()) {

			// this assumes that there is only one PDF per Contract
			// where have I defined (pdf_id) before? Do I need to ?
			var filename = Contract.getFieldValue(pdf_id)[0].filename;

			if (pdfName == filename) {
				console.log("Found match: " + filename);

				// Update Contracts record
				addsubcategory(Contracts, subcategory_id, entry.getFieldValue(csvKeyword_id));

				// Update Contracts record
				addKeyword(Contracts, keyword_id, entry.getFieldValue(csvSubcategory_id));

				// Set category
				Contracts.setFieldValue(category_id, entry.getFieldValue(csvCategory_id))

				break;
			}
		}
	}
	document.saveAllChanges();

	return 'done';
}

Update_Records();

First thing I noticed when I put it through a formatter was that there seemed to be a missing bracket, I’ve added an extra one before the document.saveAllChanges() line and everything balanced out. I used Visual Studio Code to reformat it but I also found an online JavaScript beautifier as well.

Next, much of the script makes sense to me, and other parts seem somewhat circular. Could you look what I have done and see where I messed up? …because I think I did a good job on that : )

I need the script to look up each record that aligns with the PDF file name in the cvs file, and then update the records in the main database form called “Contracts”

Now I see you added an extra call:

addsubcategory (Contracts, subcategory_id, entry.getFieldValue(csvKeyword_id));

What this change is doing is trying to call a function here called addsubcategory but that function doesn’t exist. I think you’re trying to copy the addKeyword line for a new field and if you look at the very top of the script there is a function addKeyword which defines or creates the function. If what you’re trying to do is create a comma separated list of sub categories but in a different field, then I don’t think you need to do a new function, I think you can just call addKeyword with the sub category field ID:

addKeyword (Contracts, subcategory_id, entry.getFieldValue(csvKeyword_id));

Try fixing the curly braces and changing addsubcategory over to addkeyword as above and see if that gets you a little closer.

[Max Heart]

Sam, you are amazing. Just watched your video on the Script Manager. Thank you so much for all your work.
Best, Max

[Brent S]

Hi Daniel,

A few more questions if I may. I have tried to figure things out by watching some of Sam’s videos etc., but I am a little stuck.

So first, from my reading it looks like I need to use a “Form Level Script” as opposed to a “Field Level Script” as I am updating existing records.

I only have two forms, my main database and the cvs data I want to input. Is it correct that I must place the Form Level Script in my main database and not in the cvs form? (That seems to be the only way I can get the correct fld-hash numbers that I need into the script).

Next, much of the script makes sense to me, and other parts seem somewhat circular. Could you look what I have done and see where I messed up? …because I think I did a good job on that : )

I need the script to look up each record that aligns with the PDF file name in the cvs file, and then update the records in the main database form called “Contracts”

I have attached a few screen shots in case that helps with context.

Thanks again.
Brent

——————-

function addKeyword(rec, kw_id, kw) {

var keywords = rec.getFieldValue(kw_id);

if (keywords == undefined || keywords == “”){
rec.setFieldValue(kw_id, kw);
} else {
rec.setFieldValue(kw_id, keywords + ‘,’ + kw);
}
}

function Update_Records() {

var csvForm = document.getFormNamed(“csv”);
var ContractsForm = document.getFormNamed(“Contracts”);

// Contracts
var pdffilepath_7_id = ‘fld-3ea77ccc18604174b722ece105965c44’;
var license_category_id = ‘fld-d63ab1856e554efebe62b9f1826c388d’;
var license_subcategory_id = ‘fld-f0c47c7e6ad244fe955e2c24cb448590’;
var agreement_keywords_id = ‘fld-a2126cbe512646e9ba144fb5a90b06dc’;

// get csv field Ids
var pdffilepath_7 = cvsForm.getFieldNamed(‘PDF’).getId();
var license_category = cvsForm.getFieldNamed(‘Category’).getId(); var license_subcategory = cvsForm.getFieldNamed(‘Subcategory’).getId();
var agreement_keywords = cvsForm.getFieldNamed(‘Keyword’).getId();

// Loop over all csv entries
// So this tells my Contracts database to look at the cvs form?
for (entry of cvsForm.getRecords()){

// get pdf file name
// is it looking for another fld-hash here?
var pdfName = entry.getFieldValue(csvPdf_id);

// replace spaces with underscores
pdfName = pdfName.replace(‘/ /g’, ‘_’);
console.log(“Processing: ” + pdfName);

// Loop over all LICENSEdb records
for (Contracts of Contracts.getRecords()){

// this assumes that there is only one PDF per Contract
// where have I defined (pdf_id) before? Do I need to ?
var filename = Contract.getFieldValue(pdf_id)[0].filename;

if (pdfName == filename){
console.log(“Found match: ” + filename);

// Update Contracts record
addsubcategory (Contracts, subcategory_id, entry.getFieldValue(csvKeyword_id));

// Update Contracts record
addKeyword(Contracts, keyword_id, entry.getFieldValue(csvSubcategory_id));

// Set category
Contracts .setFieldValue(category_id, entry.getFieldValue(csvCategory_id))

break;
}

}
document.saveAllChanges();

return ‘done’;
}

Update_Records();

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

I turned this into a video on using a link to form to create the link and scripts (both form and field) to copy the values.

[Sam Moffatt]

I think the link to form field is probably the way to do part of it but you also want to intentionally duplicate data which is where I’d leverage a script.

For your link to form, create it in the “address” form and link back to your “service report” form. If you create a 1:M, in the “service report” form it’ll give you the option to select only a single address. Downside as you’ve noted is that it’s a link and if you delete it, it’s gone. Additionally if you update it, the historic nature of the service address is gone (if you could avoid updating that’d be the best option but that is it’s own data modelling problem).

The next step would be to create a script that looks at the link to form field and copies the data out from it. There are a couple of hints on how to do it and you can do it either as a form script (explicit copy) or a field script might work (implicit copy). There are some trade offs each way though a form script is a little easier to get started with I feel.