Query to display selected records

[Tim W]

I’m pretty familiar with SQL and even MS Access, but can’t seem to figure out how to do what I need with Tap Forms because it lacks some of the basic features at the core of every other database system (eg a unique field/key, queries, etc).

The scenario: A form called ‘sites’ to enter the name of a site, its URL, username and password and some notes, with a many-to-many link to a second form called ‘users’ which should display all users with a login to that site. There are a large number of sites and a smaller number of users. The ‘users’ form contains a field for the username with another many-to-many link back to ‘sites’ to display which sites each user can access.

Ideally, I’d prefer to select a user from a dropdown list (or an auto-complete field) of users, and it display the sites for that user. Everything I’ve tried ends up with unexpected behaviour – the most common issue is that I end up with dozens of user records for the same username. There’s also no ‘clean’ way to enter data via a ‘link from form’ field, it’s not immediately obvious that you should enter data in there.

Am I missing something obvious?

Minor side note – masking the password field is a nice touch, but it doesn’t always mask properly when entering into a ‘link from form’ field.

[Sam Moffatt]

On the Link to Form field there should be a little tick icon that will let you search for existing records already in the form on the other side. On the Mac it should be under the record list and on iOS it’ll be in the top right corner when you tap into the Link to Form field. It should be there on the Link from Form field assuming it is set up as Many to Many, you might need to toggle the “Show Inverse Field” option on the Link to Form field to recreate the Link from Form field if it was created as a one to many field. A Many To Many or JOIN type should be a table on both sides (e.g. the Link to Form and Link from Form). A One to Many Link From Form will render as a single record view and if you created the inverse field before changing the type to Many to Many then it will retain that (I recall that might have been a bug that was fixed but perhaps not).

If in your user form you store a per site password then you might need a third form to store the unique passwords per site (user link from form, site link from form and password). I have some other posts on the forum about modelling such a relationship.

Internally Tap Forms store a record ID for each record and it’s backed by a document store called CouchbaseLite (which is where the CouchDB and Cloudant sync comes from). This is a semistructured data store and in a sense could be modelled as a key/value data store where the key is the document identifier and the value is a JSON document. The key exists however it’s abstracted away from the user most of the time.

CouchDB/Couchbase/CouchbaseLite doesn’t have queries in the SQL sense but instead you write a map/reduce function in Javascript. The Saved Search feature in Tap Forms is an abstraction of that interface to build the query for you. Tap Forms doesn’t expose a way of building your own map/reduce functions to power a saved search but you can do this with your own CouchDB instance.

Depending on what you’re trying to achieve, you can replicate all of your data into a CouchDB instance and get access to it there. I personally do this and have built tooling to do a bunch of different tasks. Having your own CouchDB enables full access to your data and if you follow the way Tap Forms works you can also use this interface to push data into Tap Forms.

Attachments:

You must be logged in to view attached files.

[Tim W]

OK I think I get it now – this database uses javascript rather than SQL to query it directly. My javascript skills are pretty rudimentary, and even with the API, I’m struggling to write working code.

The first form is just for data entry. The second form is for the ‘query’ – one field with a combo box using the values of the other form’s username field and another field set as script type.

So what I’m trying to achieve is to select an entry from the combo box and it should display a table with the matching results. I know I need to set the variables and run a loop through the index of all records in the first form. I don’t have a clue how to output that data though – something like a ‘link to form’ table would be perfect.

This isn’t correct but it’s what I have so far:

var query_user = record.getFieldValue('fld-abcd');
var sites = record.getFieldValue('fld-1234');
for (var index = 0, count = sites.length; index < count; index++)
{
	var sitename = sites[index].getFieldValue('fld-1111');
	var url = sites[index].getFieldValue('fld-2222');
	var username = sites[index].getFieldValue('fld-3333');
if (username=query_user) {
	sitename;
	url;
	}
else {
	}
}

[Sam Moffatt]

I’m going to propose something that is a little orthogonal to what I think you’re trying to do but might get you to where you want to be. Here are some ideas.

<b>Pick list for username</b>

If you’re after a pick list populated with values from another form, step one is to go into Preferences > Lists > Pick Lists. Create a new pick list and select “Use values from field” to select the form and field in your main form with the usernames in it. This should give you a unique list with duplicates removed that will update as the values change.

The next step is to set up your field as a pick list and point it to the pick list you just setup.

<b>Link to Form JOIN</b>

The next step is to create a “Link to Form” field, set it to JOIN and then you’ll want to join on the two username fields. This will give you a table in the record filtered by username. You can build a script field to process the records of the children to do some work.

<b>Use a Saved Search</b>

In the top right corner next to the search field should be a filter icon that lets you create a saved search. You can use this to filter on what ever you want and update it as necessary. A filter could be on username and then you can use the multicolumn list view (MCLV) to get a table of results.

<b>Use the Form grouping option</b>

Depending on what you’re after, you can set the form grouping option in tandem with the multicolumn list view (MCLV). There is a button, on the MCLV located on the right near where your Form panel would be, that lets you control the sections that are automatically generated. If you set the form grouping to use the username field, it’ll automatically create sections for each username. This won’t hide any of the usernames but with section headings enabled you’ll get nice little headings. You should be enable totalling as well based on your field configuration settings.

[Tim W]

Thanks for that tip – I’d already done the picklist, but it looks like simply joining the 2 forms is all I needed to do to make it run like a query. Selecting the username from the drop down list now instantly updates what gets displayed in the ‘like to form’ table.

[Sam Moffatt]

Happy to see it helped! Shame my HTML formatting got butchered but live and learn :)

[Brendan]

@Sam, to bold text, use strong instead of b

[Sam Moffatt]

To b or not to b, that is not a question any more! Yeah, I used the WYSIWYG editor and saw it used strong tags instead. This is what I get from trying to be a little quicker, much quicker to type!

[Author]

Posts