Link to Forms, using existing tables

[Nom Deguerre]

Hello,

I’ve been using iOS Tap Forms for some time as a really convenient way to access and display data on my phone in a way that is perfect in the field. Typically, I am importing existing data from other sources, reconfigured for my purposes. The information is for reference and it is rare that I add new records through Tap Forms. Up until this point it has been perfect.

The problem arises now that I want to link tables.

In this instance, the original data comes from a Microsoft Access database. I export the information as csv, manipulate the data, and import the new csv files into Tap Forms.

I am wanting to establish a one-to-many relationship between the tables (which exists in the source tables). The parent table concerns people and the child table concerns events.

So,
In table PEOPLE, there is an IDENTITY_ID and about 40 other fields of information about the person. In table EVENTS, there is a PEOPLE_IDENTITY_ID and about 10 other fields of information about the event. The records should be linked on that ID. Each event belongs only to one person. It is a very standard one-to-many relationship.

Having imported these tables into Tap Forms, is there a simple way to re-establish the links?

I can make it work if I set up “Link to Form” using a JOIN relationship and join on that ID but the display of the child table within the parent record is not ideal. I can also make it work if I set up “Link to Form” using a one-to-many relationship and individually assign the events to the people (which doesn’t use the identity ID at all) – the behavior and the display of the one-to-many set-up is exactly what I want.

The problem is that there are around 15,000 PEOPLE records and around 10,000 EVENTS records. (Some people have dozens of events, many have none). I can’t manually assign all of the events to people.

Is there an easy solution to my problem?

Sincerely,
Nom

[Prashant M]

Nom,

I’m a new user so take my reply with pinch of salt.

Using Join relationship , if you don’t want the display of child within parent, you can always switch it off by using hide field ?

I feel your problem can be sorted faster if you use excel once to manipulate data with (v)xlookups but since I don’t have the dataset it’s not easy to visualise

[Sam Moffatt]

I think this is a case where the JOIN mode on iOS will render the table inline but the 1:M mode has a selector to go to a subview that has the table.

I feel the tangible feature request is to be able to control if the JOIN displays inline or as a subview (conversely one could consider the inverse for the 1:M/M:M modes).

In terms of fixes right now, you could use scripting to create the links for you. You’d start with a JOIN field so that Tap Forms does the heavy lifting for finding the child records and then you just need a script that iterates through each of the records and attaches the child records from the JOIN field to a 1:M field.

The script is pretty simple, this is from an earlier post:

record.getFieldValue('fld-joinfieldid').forEach(rec => record.addRecordToField(rec, 'fld-1tomanyfieldid'));

This will do just the currently selected record and whilst I (still) haven’t tested it but it should work though you need to replace the field ID’s to match. You could probably put this in as a field script as a test run but I’m not sure if the JOIN field triggers a script field update automatically (since the field isn’t edited directly) so I’d probably stick with a form script. Something like this as a form script might do it:

form.getRecords().forEach(parentRecord => parentRecord.getFieldValue('fld-joinfieldid').forEach(childRecord => parentRecord.addRecordToField(childRecord, 'fld-1tomanyfieldid')));
document.saveAllChanges();

Basically get all the records from the current form as parentRecord, then for each parentRecord get all of the records from the JOIN field as childRecord, then for each childRecord add it to the 1:M field in the parentRecord. Then save all the changes :D

Again, I’ve not tested it and you’ll have to splice your own ID’s in, running this more than one should be safe (TF generally dedupes multiple record links) and should allow you to relink everything after you do your own import. The document.saveAllChanges() makes sure that everything is saved properly so it’s important it’s there as well. You can delete the boilerplate you get when creating a form script and just put this in with the ID’s replace to match your field IDs.

[Nom Deguerre]

Prashant,

I appreciate the response.

I do want the child records to appear in (and be linked from) the parent record so hiding is not the answer. This may be different on the iOS version, but there is no control over how the child table appears in the parent record – All of the child fields are shown and they are shown in the order of the child form.

Sam’s response has correctly identified the issue. I want the child records not to display inline but as a subview and his method does provide the solution.

The imported tables (forms) are exactly as I want them so no further manipulation in excel is required. I was very surprised that I wasn’t immediately able to join my tables as a one-to-many field in exactly the same way as the join field is created – that is, choosing the type of join and choosing the matching fields.

Nom

[Nom Deguerre]

Sam,

Thank you so much for this perfect response.

I had seen the earlier post and I felt that the solution lay in that direction but, not having used scripts in Tap Forms before, I wasn’t sure how to apply it. You’ve added just enough information to get me over the line and I can assure you that it works perfectly.

My biggest hurdle (when reading the original post and having received this response) was simply determining the fieldIDs. I didn’t know how to discover them – it’s perhaps not as obvious on a phone as it is on the desktop version.

So, for the sake of anyone at my level who needs to follow this path, here’s how I finally got there:

1. In the parent form, create a “Link To Form” field that links to the child form as a JOIN on the ID field – this will populate and link the forms and be functional but will display inline.

2. In the parent form, create a “Link To Form” field that links to the child form as a 1-to-MANY link – no functional links will be created.

3. At the form level, choose “Run a Script” which opens the script editor.

4. Add a new script and paste Sam’s code, in its entirety, into the editor replacing the default starter script.

5. Watching the first 5 minutes of Sam’s own “Simple Intro To Scripting” video allowed me to see that the field ids are self-populated from the script editor. Knowing that, I was able to find that feature on the iOS version, above the software keyboard. For me, this was the key breakthrough. I could now obtain the field ids for both the functional join field and the empty 1:M field.

6. Insert the obtained field ids into the placeholders in the script.

7. Save and then run the script. Problem solved!

8. To tidy up, delete the script and delete the “Link to Form” join version.

Once again Sam, thank you. It won’t be the only time that I’ll be making use of this method of linking forms.

[Sam Moffatt]

There is an equivalent for the scripting video on using an iPad to script in TF5 which is a very similar interface to iOS featuring the same options though with a wider display and split views. The balances videos (part 1 and part 2) also are done on the iPad as well to try to demonstrate some of those capabilities. In terms of scripting 99% is the same on the two platforms, there are a couple of things around UI navigation that I think is better on the Mac but most of the core data manipulation stuff is identical.

I’ve wondered about having a dedicated documentation page I could link to that covered getting the field ID on both the Mac and iOS/iPadOS because once you know it things are easy but it isn’t immediately obvious and I write down to get the field ID an number of times. I might have to email the keymaster about it.

[Author]

Posts