one to many… what is relational?

[Eddie]

I have some customer data that I import periodically. Each form and its data is pretty straight forward and each contain a customer# field which is unique.

So, I also have a summary form which contains nothing more than a customer name and customer#. All other fields are linked to other forms using a one to many relationship. However, I can’t get it to display all the records from the other forms?

I’m sure this is me, since I can’t find where to create the relationship from the customer# field in the summary form to the customer# in the other forms.

I’ve tried this on both the Mac and the iPad… no luck. Any help is appreciated. I look forward to the slap to the forehead when someone tells me what I’m missing.

[Brendan]

Hi Eddie,

You’re not actually missing anything. Tap Forms doesn’t use a custom field to establish the link between records from other forms. You need to select the records themselves to establish a link.

I have some instructions on how the Link to Form function works for the Mac. It’s exactly the same for the iOS versions too.

http://www.manula.com/manuals/tap-zapp/tap-forms-mac/2.0/en/topic/relationships

Thanks,

Brendan

[Eddie]

This is unfortunate. My primary use of this tool is to import existing data and not do much data entry. Manually connecting several forms with thousands of records a couple of times a week, simply won’t happen.

The product is fantastic as an import tool for lists, which is how I will have to continue to use it. My money is not wasted. (iPad, iPhone, Mac versions)

Any plans to add this functionality in the future? Always great getting a reply directly from the developer. Another reason my money was not wasted.

[Brendan]

Hi Eddie,

I think that would be a great feature to have and I definitely see the need for it. I’m currently working on inverse relationships at the moment. Just started today. But I’ll need time to think about how to implement that. One thought I had was to use something similar to the saved search mechanism where you can pick a field and an operator. I suspect you might want to link not just for equalities, but also inequalities. But I won’t know for sure until I dig into it and think about it more. I can dream all I like about new features, but when it comes time to actually implement them, that’s when the technical difficulties get in the way :-)

[appledog]

Hi Brendan,

I would love to see this functionality as well. The ability to use a field such as customer number, etc. is very important to a relational database. I have used Oracle, DB2 and other SQL databases on larger systems and this is very important to be able to specify a key field that can be used to create such an automatic link to take out manual work of linking everything by hand all the time. I know that SQLite also supports select statements that do this sort of linking, but brute raw databases lack the user friendliness, presentation and beauty of Tap Forms. We could do so much more with such a feature!

[Brendan]

Hi Appledog,

I think it would be great to have this and would be a very powerful feature. Baby steps!

Thanks!

Brendan

[David Butenhof]

This is the most recent topic I can find on importing linked forms, so I’m going to reply here. (Simple logic.)

I’ve been looking for quite a while for a Bento replacement. Actually, I was looking long before FileMaker dropped Bento. What I wanted was an iCloud sync-ed database that would work on my Mac, my iPhone, and my iPad to keep everything up to date. I never really found anything that met the basic requirements. The closest was probably the venerable old HanDBase; but it doesn’t do cloud sync (which I’d find much more convenient than local WiFi sync), and the Mac client is primitive, ugly, and despite recurring promises virtually unmaintained for years.

And then today I almost accidentally found a recommendation for TapForms.

After spending some time today playing with the Mac demo (thanks for that, by the way!) and reading the forum and documentation, I’ve come to the conclusion that you’re almost there. I think I could use TapForms as I’ve used Bento, with only a few minor inconveniences… and with many advantages (including, not least, supported software, which is always nice).

Except that moving my data from Bento to TapForms would be unacceptably complex without the sort of import infrastructure being discussed in topics like this.

I never tried to use related Bento databases, because I didn’t like the UI. I think your UI for linked forms is a little better, though perhaps still not quite perfect. But my solution in Bento, which did just what I wanted, was the “simple list”. So now, to migrate, I need a solution for simple lists.

You don’t have such a concept, and that’s fine — I’d be satisfied with your linked forms. The challenge comes in converting thousands of Bento records with simple lists into a set of TapForm linked forms — and until that can happen, I’m stuck with Bento.

I can import the Bento data in CSV form. Obviously it’d be convenient if TapForms could just read that format and create the appropriate linked tables; but I’d be happy to write some Perl or Python to massage the data into an appropriate set of separate CSV files — which of course requires that I be able to define the SQL foreign keys that link the tables.

Although that leads me to a possibly more immediate question, since a topic from several years ago seemed to suggest that you’re using sqlite underneath. How sensitive is TapForms to which aspects of database & table structure? Would it be feasible for my hypothetical Python script to tear apart Bento’s CSV export and build a set of tables directly that TapForms would transparently “adopt” as if you’d created them?

(Whether or not I get an answer, when I have some time I’ll probably poke around to find your sqlite files and look inside them to see what I can figure out; but some hints would be appreciated and could be the key to selling a Mac, iPhone, and iPad version of TapForms… because if I can get the Mac demo looking the way I’d like, I’d be ready to go “all in”.)

[Brendan]

Hi David,

Tap Forms can already import your Bento templates without you needing to first export them as a CSV file. If you follow the online documentation it will tell you how to do it.

However, as you’ve discovered, there’s no concept of a Simple List in Tap Forms. Although technically speaking it’s really just a Related Data type thing. I’m not quite sure why Bento has both Simple List and Related Data (Link to Form in Tap Forms parlance).

Tap Forms doesn’t create tables when you add new forms. The table structure in Tap Forms is fixed for the most part. I only update the database schema when I send out a new update. The way Tap Forms stores it’s fields is actually in rows in the database as opposed to columns. This is an area that I would like to improve this year as it’s much faster to fetch from columns in a database than rows. But it was more flexible with rows for my purpose. Although one of the primary reasons I did this also was because with SQLite you cannot delete a column once you’ve created it. You ned to create a new temporary table, copy all the data over to it excluding the dropped column. Sorry for the tangent there.

I would recommend setting up a couple of forms and link them together using the Link to Form field. Add some records to each form and then export them using the Export Records command. Select the “Export Linked Records” option and turn on Field Type Tags.

Once you’ve done that, take a look at the CSV files generated. You’ll see the foreign keys in the CSV file for all the records exported in the linked forms. That’s how you’d have to re-create a CSV file in order for it to be imported into Tap Forms.

If you have some skills with Python then this should be possible. Then you would simply need to import the parent form first, then import the linked form CSV files second. Tap Forms will connect it all up to establish the links again between the forms.

I hope that helps!

Thanks!

Brendan

[Author]

Posts