Export -> Import round trip, linked forms

[David Butenhof]

I’ve been using Bento since it first came out, importing from an even more primitive database on the PalmOS device that I gleefully discarded for an iPhone 3G when that first came out. I tried Bento related tables, but didn’t like the interface: and when Bento 4 came out with typed Simple List columns, it provided just what I needed to track things like movie viewings, and a set of stories contained in collections.

And I knew there’d be a risk that moving this data out of Bento might be difficult; but I figured I’d deal with that later. Well, FileMaker decided it was “later”, and I’ve been looking for a way out. When I saw TapForms I thought this might work, but the jury is still out.

My objective is to attempt to import Bento records with simple lists into TapForms as linked forms. My idea was to manipulate my Bento CSV export into multiple CSV forms with generated UUIDs matching the schema used in TapForms export of similar records.

Before going too far on that, I decided to see what TapForms does with its own data. So I modeled my basic data in the Mac TapForms Demo 2.0.1 (400), exported three linked forms, deleted the forms… and then tried to re-import the .csv files.

First, I noticed an oddity in the export of my main Books form. The records have your form_record_id, then Date Created and Date Modified as the first 3 fields; however the .csv file created by TapForms includes the headers for Date Created and Date Modified… but not the data. This causes the re-import to shuffle all the remaining fields.

The documentation suggests that TapForms will recognize the parent_record_id in the “child” forms and re-connect them on import; but even after fixing the skewed columns in the .csv, this doesn’t appear to happen for me. And if I manually re-link the forms, it still doesn’t connect the existing records.

I’m attaching the three .csv files generated by TapForms. Maybe this is a bug. Maybe it’s a limitation of the demo. Maybe there’s something I’m missing in the import procedure. I’m not quite ready to give up yet. Ideas?

Attachments:

You must be logged in to view attached files.

[Brendan]

Hi David,

The skewed export file with the Date Created fields is a bug which I have fixed in version 2.1. I will be releasing that version on Feb 20th.

You would first want to import your parent records and then import the child records. However you’ll probably need to link the forms together manually as there’s no information in the CSV export files that will re-create the Link to Form fields properly. Well, it will create the fields for you, but you’ll have to manually select the linked form in the Field Properties and also set the Link Type correctly before you import your records.

It should work.

Thanks,

Brendan

[Brendan]

Also, don’t use the <from_form> field type tag. It should just be <form>

[David Butenhof]

I didn’t write <from_form>, you did, on the export. I also thought it was curious that none of the linked records had any value in that column — but I had no particular basis to second guess the export. However, I just tried editing the .csv to say just <form> — and it doesn’t appear to make any difference.

I import the main Books form, and then one of the linked forms. I edit the main form’s fields to make them links, the embedded tables appear on the records, but they don’t show the records that had been linked to that book.

Is this perhaps because I’d originally checked the “Show Inverse Relationship” box? I suppose I should go back and try without that to see if there’s a difference, but I hadn’t gotten around to that.

[David Butenhof]

OK, the “Show Inverse Relationship” doesn’t appear to make any difference here.

I’ve noticed that sometimes when I delete forms, they don’t completely disappear; they still show up with 0 records, and appear on the selection list when I try to link forms. So just to be sure, I deleted them, showed “All Forms”, deleted the empty “shadow” copies that remained, and then rebuild the three forms entirely from scratch. When I linked, I didn’t check the “Show Inverse Relationship” box. I populated each with a couple of records.

I exported the main form. This time, the .cvs files don’t have <from_form> anymore; and that last column in the linked forms doesn’t appear at all. I thought this might be progress. I manually added the created & modified data.

I imported the main form, then one of the linked forms, and linked them. The once linked data in the secondary form still doesn’t appear in the table that’s added to the main form record. Sorry, but it’s still not working for me, and I’m feeling a bit frustrated spending so much time trying to work this out.

Any more ideas?

[Brendan]

Hi David,

Hmm… I’m confused. The Show Inverse Relationship function doesn’t exist in the current release. Can you send me a screenshot of that screen where you can set that?

Which version of Tap Forms Mac are you using? 2.0.1 is the latest released version and I just checked and “Show Inverse Relationship” doesn’t appear in that version.

Thanks,

Brendan

[David Butenhof]

Wow. If you’re confused, just think how I feel right now. ;-)

Attached are two screen shots: the “About TapForms” dialog of the Mac Demo version that I downloaded from your site, and the record Edit dialog showing the checkbox.

Attachments:

You must be logged in to view attached files.

[Brendan]

Hi David,

Oh! Oops! I didn’t realize that feature snuck into the demo version and I didn’t realize (or remember) you were using the demo version.

The inverse relationship support wasn’t complete at that point in time when I built the demo version. I meant to take it out before I uploaded it as it wasn’t finished.

I will be uploading a new build of the demo in 5 days from now.

I’m really sorry for the confusion and thanks for clarifying that for me.

Thanks,

Brendan

[David Butenhof]

Ah. And if it snuck out in the demo prematurely, that would probably explain why it may not be working right; and potentially why my round trip experiment failed even without that option checked. Anyway, the mystery may not be solved but the investigation is on hold until I can try with a stable version.

Still hoping I can work through this and get my Bento data transferred to TapForms Demo so I can justify buying everything and switching over. (And again, thanks for having the demo so I can try.) I’ll keep an eye out for the 2.1 version, and you’ll likely be hearing from me again. ;-)

Thanks.

[David Butenhof]

Bento started behaving unreliably on Yosemite. For some, it apparently doesn’t run at all. For me, it runs, albeit with some occasional odd dialog boxes or hangs. Clearly, it’s time to jump ship — even if it’s still not entirely clear there’s a safe place to land.

Most of my Bento forms imported into TapForms flawlessly, so thanks for that! However, the two main forms where I made use of Bento Simple Lists remain problematic. They’re too big to re-enter manually. I can’t shut down Bento for good without moving them. And I’ve still got no handle on how to do it.

I had written a Python script that would strip out the Simple Lists from a Bento CSV export into separate forms which, in theory, could be imported into TapForms. Having been unable to accomplish this, I’d resorted to round-trip TapForms export/import; and as recorded in this thread back in the Spring, I’d had trouble even getting that to work. But that was a demo version.

Now I’ve gotten around to trying again with the released TapForms 3.0.2; and it’s not working any better than it did back then.

I created a simple Movies form, with a linked Movie Viewings form. I entered two movies, more or less randomly, with one viewing each, and this seemed to be working just fine.

So I exported the parent (Movies), and TapForms created a Movies folder containing Movies.csv and Movie Viewings.csv. Each shows two records, as expected, with all the UUID columns apparently intact. In the attached archive file, you can see the Movies folder and the contents.

I then Deleted both forms from TapForms, and imported the Movies.csv parent.

First oddity: it imported the form just fine, but with no records. I have to import a second time to get the data.

Now I go on to import Movie Viewings. The form imports fine, with data. Now I go back to Movies, and edit the fields, selecting Movie Viewings form the Link to Form popup on the appropriate record. The embedded table appears; however, no matter what I try, I can’t seem to get it to actually find the data in in the child form. The table is empty. This obviously doesn’t do me any good; and if I can’t re-import exported TapForms data, it’s no surprise that I can’t make my converted Bento data work.

Note that if I export the imported data, the Movie Viewings.csv file shows no data, only the headings. Clearly the linkage didn’t work correctly. This is the Movies1 folder in the archive file.

So am I missing something, or does this just not work?

Attachments:

You must be logged in to view attached files.

[Brendan]

Hi David,

One of the problems is that the Movie Viewings CSV file contains the parent_form_id value for each record. That means that if you delete the parent form and re-import the parent form again, it will get a new internal UUID and therefore the records won’t match when it’s importing. So you should keep around the template of the Movies form and import that, then import your Movies records into it, then import your Movie Viewings CSV file. And since a Tap Forms Form file (.tff) will also include any linked forms embedded, then you need only export the parent Movies form. Then when you’re importing, import into the Movies form first, then import into the Movie Viewings form, selecting the existing form from the popup.

Hope that helps.

Thanks,

Brendan

[David Butenhof]

One of the problems is that the Movie Viewings CSV file contains the parent_form_id value for each record.

So are you suggesting then that the re-import will work better if I DELETE that column from the file you export? I tried removing that, and it doesn’t seem to make any difference for me.

In terms of my actual goal, this suggests that I shouldn’t try to generate this column for my Bento data, which is useful info. I’d wondered exactly how TapForms used that on import since there’s no reference in the parent form. (And presumably the parent_field_id would have the same problem.)

It makes sense that these UUID values would help in adding rows to an existing form, and I suppose that could apply when manually syncing data between systems; but in general, what’s the point in exporting them?

And since a Tap Forms Form file (.tff) will also include any linked forms embedded, then you need only export the parent Movies form.

While this is fine for the “dry run” where I’m trying to at least get a round-trip TapForms export to come back into TapForms intact, it doesn’t help for my real goal unless I can figure out how to generate a .tff from my Bento data. And getting my TapForms to TapForms experiment to work using techniques that I can’t apply to the Bento data doesn’t actually help me.

[Brendan]

I’m not suggesting you delete that column. I’m suggesting that if you let Tap Forms generate a new parent form for you, it will have a new form_id which does not match the parent_form_id in the exported child form.

If you want to make a new form to import into, then you would have to find out what the internal UUID is of the new form and then change the parent_form_id values of the exported child form’s CSV file to match that.

You could find that information out by using an app such as SQLiteManager and look for the id value for your form in the form table.

Thanks,

Brendan

[David Butenhof]

If you want to make a new form to import into, then you would have to find out what the internal UUID is of the new form and then change the parent_form_id values of the exported child form’s CSV file to match that.

OK, that makes sense; I was assuming way too much about how TapForms handled the export and import. And that may be exactly the clue that I needed. It’ll make the import more tedious and only partly automatic. However I really only need to make this work for three Simple List forms to complete my transition from Bento: the manual lookup (and a simple search-and-replace on the CSV file) is acceptable.

[Brendan]

I know I should really take the time to figure out what to do with those darned Simple List fields :-)

Sorry that this is such a hassle for you.

Thanks,

Brendan

[Brendan]

Actually I think one of my other customers had converted them into Related Data fields in Bento before importing into Tap Forms.

They created a Related Data field and then used copy and paste to transfer the simple list values into the Related Data field.

Tap Forms imports Related Data fields just fine and coverts them to Link to Form fields.

Thanks!

Brendan

[David Butenhof]

Yeah, I’d considered that route, too; but there didn’t seem to be any more automated way to accomplish that transition, and I’ve got way too many records to edit manually.

I never really liked the idea of using related data because you end up with the “child” library visible at the same level as the parent, and it seemed messy. In native SQL programming you’d naturally use a set of tables but they’d never be directly visible at the UI unless there was a reason to expose them. The Bento 4 Simple List concept seemed to make a lot more sense to me at the time, in some primitive way almost like a representation of a SQL table with a built-in join. In retrospect, I suppose that I probably should have realized it was too unique to be “safe”.

From your perspective inside TapForms, importing Simple List data from a Bento CSV into a linked form would be easy. (I have no idea how it looks in a Template.) Whether it’s worth your time is of course another question, but it certainly would have made my transition easier. ;-)

Anyway, thanks for the dialog. And Happy Holidays!

[David Butenhof]

I got this working, and one of my two Simple List Bento databases now seems to be operational in TapForms. Awesome!

I did run into one annoyance, though it was something that I decided I could live with since the workaround would be messy.

There are two separate date format in the Bento CSV file … one was an actual data column in my intended schema, of the form 2014-12-25; and the other was Bento’s created & modified timestamps of the form 2014-12-25T21:02:22.

If I set TapForms’ date format to the latter to get the correct timestamps, then it would fail to parse my dates; whereas if I set it to the former, then it would ignore all of Bento’s creation and modification timestamps and substitute the current time.

Now, really, the latter behavior is acceptable, although it does bother me just a little. I don’t really need to preserve the original modification timestamp, although I’m just obsessive enough that I hate having to throw away that data. However, since you have special processing for the creation & modification timestamps, I’d think you should also be able to handle a different expected format for them — they have to be full timestamps, not just “dates” or “times”.

[Author]

Posts