Search Results for 'script'

[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

[Sam Moffatt]

Tap Forms on Mac has the ability to print a table of records and field values, it has a full page mode leveraging a default layout to print individual records plus custom layouts allow the ability to have more control over your layouts. It’s not without limitations around link to form fields but for relatively simple and concise use cases it works.

Tap Forms on iOS doesn’t to my knowledge support printing but as noted by @gregmartin you can export data out and use other tooling like Excel to print data.

@gregmartin If you’re looking to do more complicated reporting, the Pentaho Reporting platform might help you. I believe there is still an open source version of it that might provide the tooling you need without any cost. If you’re using CouchDB, you can checkout my TFTools project on GitHub that provides scripts for working with CouchDB when used as a sync system for Tap Forms. The ElasticSearch one shows how you can map TF records onto an ElasticSearch document. That could be updated to work with a more traditional database that SQL powered reporting tools can work against though you’d have to deal with mapping the link fields. You could synthesise tables from forms or leave the basic unified content model/content construction kit architecture in place to just map each record type (record, form, link, etc) to a table.

[Sam Moffatt]

@mranderson: I created a video on using a script field to format an address that handled some values missing and options you can use to format them.

@rafaelroa: Layouts are only on MacOS.

[Gregory martin]

I am pretty sure (happily be corrected here), that there is no reporting mechanism in TapForms. It is a single table data collection tool rather like dBase or Foxpro and while it does have one2many capabilities it has no “list records” function as such, although you could do this in a Script.

I personally use the export and then analyse my research in Excel. This does mean having to join my relationships again of course, and I am considering Report Studio for iMac to help with this, but it is a touch pricey just for now.

[Sam Moffatt]

For the moment that’s why I suggest using a bridge, MacOS ships with Apache and PHP out of the box that can be relatively easily enabled and Homebrew can be used to upgrade versions if that doesn’t work. PHP gives you access to it’s APIs for making HTTP requests as well as the ability to use libcurl to make requests for anything more complicated. Obviously if PHP isn’t your thing there are other options for building a web server that provides a bridge between TF and the services you need to interact with. For myself I have a bunch of PHP scripts that handle this using curl to add in extra headers or options that TF doesn’t permit some even provide a caching layer to avoid redundant requests.

In the long run I think if there were a JSC implementation of Fetch that could be added that’d be great but I don’t see anything. I feel leveraging an existing spec makes sense because it provides a framework of what needs to be built and a suggestion for an API. The closest I found was a gist with some code in it but far from a full implementation.

[MRAnderson]

I need to tell my envelope layout to slide the city/state/zip line up if the second address line (apartment or suite #) is empty. Looking for help online, I am finding ways to script this, and I have tried to follow the instructions to no avail. I know I’m doing it wrong. I didn’t expect to need programming skills (I know, they’re basic, but still above me) to use this app, which I love otherwise. Please help!

FYI, my layout fields are “address name”; “address A1” (street address for primary residence); “address A2” (suite or apartment #); and “csz” (city/state/zip). (I don’t need separate fields for these in my anticipated use, so there’s no horizontal-slide issue, just vertical.)

Thank you!

[Doug Bank]

So these scripts don’t handle the creation of ID numbers, but they keep watch behind the scenes to make sure that the IDs are never accidentally duplicated?

[Gregory martin]

Here is a more compact version. Sets the script field to 1 if exists or 0 if unique.

var mainfield_id = 'fld-d5dba46d240d4d41bd792970526b5bc9'; // change this to your field ID
var newvalue = record.getFieldValue(mainfield_id);
var thisrecord = record.getId();
checkunique();
function checkunique() {
unique=0;
var records = form.getRecords();
unique = 0;
for (var index = 0, count = records.length; index < count; index++){
	// do not check current record
	if (records[index].getId()!=thisrecord) {	
  	    // check other record values for duplicate
    	if ((unique == 0) && (records[index].getFieldValue(mainfield_id) == newvalue)) {
    	   unique=1;
           console.log("Already exists");
           break;
        }     
        return unique;  
}}};

[Gregory martin]

You could check a field for being duplicated by adding this to a script field on your form, and the script is triggered when you press enter on the field you specify in the mainfield_id. Not sure how fast this would be on thousands of records though! You need to change your id for the field your are checking. This code snippet writes out ALREADY EXISTS in the console, otherwise the values of the records, so amend as needed of course

// ***** this needs to be your field ID
var mainfield_id = 'fld-d5dba46d240d4d41bd792970526b5bc9';
// ===========

var newvalue = record.getFieldValue(mainfield_id);
var thisrecord = record.getId();
checkunique();

function checkunique() {
unique=0;
var records = form.getRecords();
unique = 0;
for (var index = 0, count = records.length; index < count; index++){
    // ***************************
	// do not check current record
    // ***************************
	if (records[index].getId()!=thisrecord) {
	
  	   console.log(records[index].getId());
  	   console.log(records[index].getFieldValue(mainfield_id));
  	   console.log(newvalue)
  	   
  	    // check other record values for duplicate
    	if (records[index].getFieldValue(mainfield_id) == newvalue) {
           console.log("Already exists");
        }       
}}};

[Sam Moffatt]

The Tap Forms built in autoincrement option for numbers shouldn’t duplicate even if you delete a record, the autoincrement count will increase. You could use a calc field to copy it across but in reality if you hide it then it is likely enough. The exception is if you use sync and multiple devices because each device might not be aware of the other and new records on both devices might duplicate an ID.

You can use a form script to override a value of the calculation field if you need to regenerate it later. Almost every field type can be modified by the scripting engine (exception being created date, modified date and modified by device), so realistically nothing is truly read only.