Search Results for 'script'

[Sam Moffatt]

Calculation and Script fields are automatically run each time a field they reference is updated within a record. A script field can then change other fields in the record as well which can optionally also trigger scripts that watch those fields (a behaviour that can be disabled if needed to avoid cycles or other behaviour that might not be intended). Calc and script fields are also evaluated when a record is created.

Form scripts are executed on demand and you can execute them against the currently selected record (in which case record is populated) or you can apply it to all records in the form (form is populated which has a getRecords() method). There is also another special search variable that is populated when you’re working with a search (you can do typeof(search) !== "undefined" to see if it is set).

[Keith Nolen]

Hi! Is there a way to run a script on a record change or save event? And can I modify just one record with a script, or does a script have to modify all records in a form?

What other events are there?

[T.L. Ford]

As a long-time Access dev, I can say that Tap Forms is the closest database I’ve found for a native Mac platform, and I really like it. There’s some “adjusting” to do to learn how to make relationships (linked fields) as it is a no-sql database, and scripting is via JavaScript, not VBA (but the “there are objects/properties/methods” is the same). If you are importing relational data, you can expect to go through a bit of a learning curve for making the fields link in the converted data. Form design is different than in Access and I’ve just gotten used to using the defaults. Notably: Form == Table in Tap Forms, so you can find both the form and the table design together. Multi-user security is not as robust as Access. Photos (and other “extended” field types) are much nicer in Tap Forms.

[GLS]

Thank you very much for your help!

I edited the script so that it has the proper IDs but I get the following error message in the console:

Invoicing: TypeError: document.getFormNamed("Consultations").getSearchName is not a function. (In 'document.getFormNamed("Consultations").getSearchName("Uninvoiced Consultations")', 'document.getFormNamed("Consultations").getSearchName' is undefined), line:(null)

I also tried with simple quotes ‘ ‘ without success.

[Daniel Leu]

There are many ways to convert a date object into a format string in Javascript. Following works for me:

var date = record.getFieldValue('fld-xxx');
console.log(date.toLocaleDateString("en-GB", { year: 'numeric', month: 'long', day: 'numeric' }));

Console:
January 21, 2021

Cheers, Daniel

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

[Victor Warner]

In my form I have a number of fields of the date type with a date format of long (that is 27 January 2021). In a script I have included the date field, but the output is as follows: “Wed Jan 27 2021 00:00:00 GMT+0000 (GMT)”, for example:

var hedd_verification = record.getFieldValue('fld-e7c386b5203a456dbaeb4755c6897a23');
var hedd_verification_id = 'fld-e7c386b5203a456dbaeb4755c6897a23';

var degree_level = record.getFieldValue('fld-04d88b9bf5c34cef8b2b488e4bccd67d');
var degree_subject = record.getFieldValue('fld-e1c8f4bdea994a149cc338498c22e20e');
var hedd_date_verification_sought = record.getFieldValue('fld-db9621fa80094bb9ac4fc2f68bcc2595');
var hedd_date_verification_obtained = record.getFieldValue('fld-b7c0b62ed0514a15848e8cb32262b617');
var gender = record.getFieldValue('fld-e6697b5fc0d24f328757592bf4362d4e');

if (hedd_verification == 1) {

	"and I verified that " + gender + " was awarded the " + degree_level + " in " + degree_subject + " by contacting HEDD (www.hedd.ac.uk, who are 'UK Higher Education's official service for candidate verification and university authentication') on " + hedd_date_verification_sought + " and receiving confirmation from HEDD of the award on " + hedd_date_verification_obtained
} else {

"but I have not verified that the degree certificate is genuine nor have I made any independent check as to its authenticity"
}

Is there a way to format the date in the script so that the date comes out as 27 January 2021 rather than “Wed Jan 27 2021 00:00:00 GMT+0000 (GMT)”?

Any help would be gratefully received.

[Daniel Leu]

Here is the same code again but this time it uses the new javascript iterator map:

function Childs() {

  // get all child records
  var childs = record.getFieldValue('fld-c22501cc3910474aa1c59387fde30d82');

  // define id of name field of the child form
  let childNameId = 'fld-8b8f3b0cd59b4b4c9180a8903685e7ee';

  // get child names in a array
  let childNames = childs.map(function(i){ return i.getFieldValue(childNameId)});

  // return child names as a comma separated string.
  return childNames.join(', ');
}

Childs();

Cheers, Daniel

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

[Eddy]

Hi Folks!

I have a (hopefully) small issue …

In order to categories records, I use a many-to-many relationship. This allows me to change the names of the categories later, because the category name is the value of the first field in the related record. And in the related record I can see easily a list of all records, with are under a specific category. So, this is the advantage compared to a picklist.

But now I need to read out the category-names, so that I can use them in the mother record. Concrete, I want to script, so that I have a text field, with just list all the category names a specific record belongs to.

So, call my stupid, I find no way. Of course, I know how to import a field value from a related record. But how can I get a list of all the field values in the first field of all the records wich are related?

Thanks in advance, all the best, stay healthy!

Eddy

[Sam Moffatt]

Play with the link structure and how it works in the UI is probably my best suggestion there to get through what flow works best for your processes. One other aspect I’d add is that if you can tackle some Javascript, it can make working with linked fields more efficient. A simple example is that I have a form script that triggers a prompter to quickly input a subset of the data or fields without having to leave the parent form (particular use case is entering shipping data, it also sets the ship date to the current date plus some other nice features).

On the second part, you just create a link that is file:///Users/username/path/to/folder/ in the website field and when you click on the globe it should trigger Finder for you. If you put the full path in, it should trigger selecting the file in the Finder window (I don’t think it’ll open as a security limit from MacOS).

[Doug Bank]

I figured (or possibly stumbled into) a solution. I noticed that equations on the Minerals form can see all the fields on the Specimens form.

I added a field in the Specimens form to indicate whether or not the specimen is owned. That’s easy now, since I own all of them. On the Minerals form there is another field that indicates whether the mineral is truly a mineral (for example, ruby is not a mineral, but corundum is).

It took me a while to find a syntax that worked, but I ended up with an equation that says

IF (Mineral? * COUNT(Specimens::Own?) ;1;0) (basically, if it isn’t a mineral or the count is zero, then the result of the first part will be zero, so don’t count it)

I tell the field/form to use a Total summary calculation, and then I get a count of how many minerals we own in the catalog.

(Is there a way to put comments in an equation, or should I just put them in the description?)

[Brendan]

This is a consequence of me using SQLite’s FTS (full text search) engine. It only works by searching for word prefixes. It won’t work on word suffixes or substrings of words. There’s nothing I can do about it unfortunately as that’s how SQLite was written.

However, if you put a dash after the text based prefix, then it would work because punctuation is ignored in the search engine. For example JH-15813 would be returned if you just searched for 15813.

Alternatively you can create a separate Calculation or Script field that takes your ID number and strips out the JH and returns just the numeric part. Then you could search on that.

[Brendan]

That’s correct. The Saved Search mechanism doesn’t traverse down into relationships.

If you want to do searches based on relationships then you need to write a script that extracts some information from the relationship to appear on the parent form. Then create your search based on the values in the parent form.

[Sam Moffatt]

I’d probably handle this with a combination of a saved search and scripting.

Saved search is the easy part, create a checkbox for “invoiced” that I think you’re already hinting at and if that isn’t checked then it’s an available consultation. You could also use a script field for this which might make more sense in the long run too but a check box is nice and easy to get to in the short term. For the purposes of the script, I’m going to call this the “Consultations” form and the saved search is called “Uninvoiced Consultations”.

The next part is likely a form script that is on your invoices form. I’d expect the workflow is that you create a new invoice, select the client you’re interested in from the link from form field and then run the script. The script looks something like this:

function Import_Consultations() {
  let consultations = document.getFormNamed("Consultations").getSearchName("Uninvoiced Consultations");
  let invoiceClient = record.getFieldValue('fld-invoice_client_fieldId');
  for (consultation of consultations) {
    if (invoiceClient.getId() == consultation.getFieldValue('fld-consultation_client_fieldId').getId()) {
      record.addRecordToField('fld-invoice_to_consultation_fieldId', consultation);
      consultation.setFieldValue('fld-consultation_invoiced', true);
    }
  }
  document.saveAllChanges();
}

Import_Consultations();

It’s a bit rough and untested but essentially the idea is that you’re using the saved search to find candidate records that haven’t been invoiced, that’s our first line of the function.

The list of uninvoiced consultations is going to include all of your clients because it’s a simple saved search (for ease of use), so we need to make sure we only get the client for the invoice. The second line in the function is getting the client record from the invoice record we have selected (remember this is a form script in the invoice form, so we’ll have an invoice record selected generally). The fld-invoice_client_fieldId is the field ID of the “Link from Form” field that you will have in your invoice form. If that field isn’t there, go to the “Clients” form where you’ve got the “Link to Form” with 1:M set and tick “Show inverse relationship” to create the inverse field. Once the field is available you can look below the “Description” box to get the field ID.

The third line starts the loop through all of consultations where consultation is an individual record to work with. The fourth line then implements the test to see if the record ID of the invoiceClient (this is the client record linked to the invoice from the second line of the function) matches the record ID of the client record linked to the consultation. The fld-consultation_client_fieldId is expected to be the “Link From Form” field matching that in the consultation following the same pattern as we did with the invoice.

The next line attempts to add the consultation record to the current record’s fld-invoice_to_consultation_fieldId. This should be the field ID of the Link to Form field in the Invoice to the Consultations form.

The final line in the if statement toggles the checkbox referred to as fld-consultation_invoiced in the consultation record (again, replace with the field ID of the actual checkbox) and then as we work down through the rest of the script document.saveAllChanges() tells Tap Forms that we made changes it needs to save and the final line Import_Consultations() runs the script.

Now the script is going to be fragile to bad data so I’m going to make two slight changes to it that should make it a little less fragile, here’s the updated script:

function Import_Consultations() {
  let consultations = document.getFormNamed("Consultations").getSearchName("Uninvoiced Consultations");
  let invoiceClient = record.getFieldValue('fld-invoice_client_fieldId');
  if (!invoiceClient) {
    console.log('Client is not set for invoice.');
    return;
  }
  for (consultation of consultations) {
    let consultationClient = consultation.getFieldValue('fld-consultation_client_fieldId')
    if (!consultationClient) {
      console.log("WARNING: Consultation is missing client: " + consultation.getUrl());
      continue;
    }
    if (invoiceClient.getId() == consultationClient.getId()) {
      record.addRecordToField('fld-invoice_to_consultation_fieldId', consultation);
      consultation.setFieldValue('fld-consultation_invoiced', true);
    }
  }
  document.saveAllChanges();
}

Import_Consultations();

The first change is to check to see if invoiceClient is set and exits the script if it isn’t. Obviously if the client isn’t set then we can’t find the matching consultations. The second is to check on the consultation and flag a warning if the client isn’t set on it. When it logs, it logs the link to the record so if you run this outside of the script editor with the console open, you can click on the link to find the record.

Disclaimer: I haven’t tested any of this, just wrote it off the top of my head and it might have errors or changes but it should give you a general idea of how to approach the problem from the scripting perspective to help automate some of the action for you.

[Sam Moffatt]

But this is a one-to-many relationship, is that a problem? (I usually think of a ‘join’ as supporting a many-to-many relationship).

JOIN obviously is ambiguous in that it’s really based on your own data if it’s 1:M or M:M. If you only ever have one record with that key though it’s effectively a 1:M. The UI will treat it as M:M with the table rendering but if that bothers you then you could build a script later to relink records if you want to have that 1:M enforced link.

I will be importing another form later, with images of the garments for each pattern, but with the garment type associated with each picture. This will be a one-to-many in the other direction, e.g. a pattern might have a top, a dress and a skirt associated with it.

When I input a pattern I want to be able to load pictures of the different garment types, and also link to a folder containing the various PDFs associated with it.

Later, I want to be able to look at the garment type form and search through a particular garment type (for example all tops) then link back to the pattern for the one that I select and then open the files for the PDF or the instructions etc.

Will the method you described support this too?

I can’t comment entirely on the import use case because it’s not something I’ve done a lot of but my understanding is that you should be able to import the attachments as well. If you’re on a single device, you can generally use the “web site” field to link to a location and Finder should be spawned to go to that location which should solve your link to folder use case. You could also import the attachments into Tap Forms as well but that might not make sense for you.

You can created searches per form and then use the links to navigate to other records where it makes sense. You can also create script fields to combine values from multiple child records to make it a field you can pull directly in from a saved search as well.

I was planning to use a join type of relationship when I log projects which will need to have Many to many relationships with both garment types for a pattern, and fabric (i.e. a project may consist of more than one garment type from one or more patterns, and also use one or more fabrics, but I could use the same pattern or fabric in another project.) I might want to keep a note on a specific join in this circumstance.

It feels like you’re in a situation where I’d recommend a third form that handles the join details and adds any extra notes you might want to keep with it. It does add a layer of indirection to the system that you have to navigate through but it will permit you to model this many to many relationship with the extra data you want to track. I have a video that might help with leveraging link to form fields. Doing this natively will be a little easier than importing it later but obviously you can import it using the JOIN mechanism as well and change it later.

I’d still be interested to know if you think I am attacking this structure in the right way, otherwise consider the thread closed and answered.

I think it’s a reasonable approach to the situation. I’d suggest to create a simple test document where you work through the flow to see how it works and make sure it does what you need and models what you want to achieve.

Good to hear you figured out the import in the end too, it can be frustrating when things that should work in an obvious way don’t and you don’t understand why :)

[Brendan]

A Script would be perfect for this. But can also be done with a Calculation field.

See the section called:

Eliminating blank lines from a mailing address label if certain values are empty

https://www.tapforms.com/help-mac/5.3/en/topic/calculation