Importing additional data to some records

[jim Meyer]

I have a form where each record is an individual.

I have a spreadsheet with additional information (results) for some of these individuals.
The form has what you might call a key value – IDnumber. The spreadsheet records also have this key value.

Is there a way I can import the results from the spreadsheet to the existing forms if I only have results for, say 25%, of the individuals in the form using the key value?

Or if there is a way to link separate forms by this key value it would be great if someone could walk me through it.

[Brendan]

Hi Jim,

Tap Forms 5.2 has a new Join Link Type you can use to join forms together based upon a common field between the parent and child form. So you could try using that.

However, it sounds like what you want to do is export your current records as a CSV or XLSX file including the Record ID. Then edit the data externally to update your data, then re-import that back into Tap Forms.

When you enable the Export Record ID option, Tap Forms will include a form_record_id column which is used when re-importing back into Tap Forms to match up with existing records in order to perform an update instead of an insert.

[jim Meyer]

It Sounds like the Join Link Type is exactly what I am Looking for. When will that be released?

In the meantime, this will involve some Vlookup voodoo on my end to match the new data to the records.

Would it ever be possible to use existing data – say a unique id number that is already in place – to match records for an update rather than the Tap Forms Record ID? Is this what you mean by Join Link Type?

When I export from Tap Forms only one of my spreadsheets will have the Record ID making it basically a huge pain in the butt to update each record with its matching data. In fact, the Record ID doesn’t even help. I still have to just add the separate data in by hand and then I have to re-import the whole thing again. – Tap Forms isn’t going to make duplicate records, is it?

[Brendan]

Hi Jim,

The Join Link Type is already there now in Tap Forms 5.2.1 for Mac and Tap Forms 5.2 for iOS.

Right now Tap Forms requires a unique ID for each record in the entire database, even ones in different forms. So allowing you to provide your own primary key could be troublesome. I would have to provide support for an alternative key that isn’t primary, but can still be used for lookups.

Duplicate records will happen if you don’t provide the same form_record_id in the CSV file that exists in the database. That is, it will insert into the database any records that don’t match the form_record_id value in the database.

[jim Meyer]

Right now Tap Forms requires a unique ID for each record in the entire database, even ones in different forms.

So to use the Join Link feature the common field between the parent and child form has to be the Unique ID that Tap Forms provides? So how do you link forms together based on a common field?

Imagine that I have a form with Unique Student ID per student, Names, and Period Fields.
Another form that has Unique Student ID per student, Names, Grades.

Tap Forms 5.2 has a new Join Link Type you can use to join forms together based upon a common field between the parent and child form. So you could try using that.

Is there some way to link these based on the common Unique Student ID?

[Brendan]

Hi Jim,

No, the Join Link Type can join with any field in the database. I was referring to the main records before for importing purposes, not linked records. Linked records can be joined by a common field in the parent and child forms.

Yes, you can do what you want with the common unique Student ID.

So you would have a Student form with a Student ID field (plus other fields) and a Grades form which also has a Student ID field in it (plus other fields). Add a Link to Form with the Join Link Type set on it and Tap Forms can join the Students and Grades forms together such that when you view a Student record, you will see all of their Grade records that match the same Student ID.

[JB Be]

I am new to Tap Forms. What I want is not just to see matched values (in the above example the Grades) contained in one form (in the above example in the Grades form) in the other form (in the above example in the Student form), but to really import those matched values in the other form (in the above example in the Student form), so that those matched values (in the above example the Grades) stay and remain in the updated form (in the above example in the Student form), even if I delete the first form (in the above example the Grades form).

Also, I want to be able to use those matched values (in the above example the Grades) in the updated form (in the above example in the Student form) for Calculations etc.

How can I achive this? Thanks for any hint.

[Brendan]

You would need to use a Script to copy the data into fields of the parent form. When you have relationships between forms, it’s basically just a reference to the records stored in the other form. So if you delete the record in the other form, you’re deleting the reference too. The same goes if you make changes.

However, if you were to use a Table field and link the Table field to another form, when you select records from that other form, the values are copied into the Table field. So even if you delete the original record or make changes to it, it won’t affect the display of the records in the Table field. A Table field also has the added advantage that you can add additional sub-fields to it. This could be useful for example, if you had a Product form and an Order Items Table field. You could select items from the Product form that gets copied into the Order Items Table field and you could have an additional Quantity field which you can then specify what the quantity is.

Look for my Invoice Tracking sample document in the Template Exchange forum to see an example of this in action.

Thanks,

Brendan

[JB Be]

Thank you for the clarification. I successfully copied the values (all text values) from one form into the other form via a table field, as suggested. However, in the next step, using such values together with values in other fields of the receiving form in a Calculation field seems not to work (the calculation always seems to end up into a COUNT()-function), but is crucial to my use of Tap Forms. And no, I have no knowledge of scripting, but hope/expect that this should work out of Tap Forms as is (at least I didn’t find anywhere any restriction in this direction to the contrary). Did I miss something?

[Sam Moffatt]

Calculation fields will only let you do aggregate operations on fields that have multiple values (table fields, link to/form form on a many relationship). For numeric values there are more options but non-numeric fields generally results in a simple count.

Scripting will give you access to each record and let you do what ever operation you need. One of the other forum members has done a Tap Forms JavaScript Scripting 101 and the Scripting 102 has a section on looping through records. You can use Javascript template strings to relatively easily build strings with text field values.

[JB Be]

Thank you for your certainly very valuable suggestions. Unfortunately, scripting is beyond my capabilities :-(

[Sam Moffatt]

Check through the resources, Tap Forms writes most of the code for you. There are plenty of examples on the forum as well.

[Author]

Posts