Working with Linked Forms

[mchamlee]

A few things I’ve noticed while playing with the Linked (Sub) forms functionality today while testing the app for a new data entry process I want to implement. I’m already using the app for tracking workouts and other misc data collection needs.

When exporting parent data, I notice that the child data isn’t exported at the same time. Just wondering if there is a plan to implement this? I’d be happy with two CSV’s that could be linked by a foreign key (parent record id) or even repeated parent data with children listed in the same CSV.

It seems that removing parent records doesn’t delete the children. Is this a bug or a feature? I guess I could see why one might want to keep the child records around in specific instances, maybe add an option when linking a child form to enable cascade deletes?

Could you add an indicator somewhere that shows the amount of space the current database is taking up on the iPhone? If it’s there already just point me to it.

That’s all for now.

Thanks for the excellent app.

Matt.

[Brendan]

Hello Matt,

Yes, this is an issue that has come up before and something that I must address. My question is, how deep does the rabbit hole go? What I mean is, how many levels deep does the export go? Since with Tap Forms you can create relationships such as: Parent -> Child -> Child -> Child, etc.

Thanks!

Brendan

[mchamlee]

Yeah, I totally understand, and that occurred to me as I kept playing with the linked forms and realized you could have linked data several levels deep. Not sure I have a good suggestion for how you would resolve that. You could just consider it something the designer (user) must consider when building out their custom forms: the deeper the linkage goes the larger (potentially filled with redundant data or many multiple documents) your exports will be. It’s certainly not a show-stopper for me, would just be a nice to have the export of all that data happen on one shot.

Did you have any thoughts on my other two questions?

Matt

[Brendan]

Hello Matt,

I had thought about a cascade delete function. This would be good. Could be a bit of a performance issue, but something I can work on. Nevertheless, it’s in my grand plans for improving the relationship code.

I will see what I can do about adding an info panel to show some stats about the current database.

In the mean time, you can either do a backup with SyncDocs, or poke around with iPhone Explorer to see how big the database is. The file is called TapForms_db.sqlite.

Thanks!

Brendan

[Anonymous]

I have a suggestion for quickly implementing the exportable links (if it works).

Does the computer assign a unique identifier to each form as it is first created? If you could add a column to the .CSV export which lists the computer-assigned ID for each record, and if you add a column for each linked child which lists the ID of each created child, then you could re-establish the one-parent-to-many-children relationships when you import the related .csv files.

For example, if you had the following three .CSV files to import, couldn’t you program the import function to rebuild the parent-child relationships?

Project.CSV:

ID<link>, Project Name<text>, Task<link>, Occasion<link>

Project1, Bake Cake, Task67, Task68, Task69, Occasion12, Occasion14

Project2, Throw Party, Task152, Task154, Occasion13

Task.csv:

ID<link>, Task Name<text>, Context<text>, Status<text>

Task67,Buy mix, Grocery Store, Incomplete

Task68,Buy Milk, Grocery Store, Incomplete

Task69,Follow instructions on box, Kitchen, Incomplete

Task152,Invite guests, Phone, Incomplete

Task154,Serve Cake, Living Room, Incomplete

Occasion.csv:

ID<link>, Occasion Name<text>

Occasion12, Halloween

Occasion13, Thanksgiving

Occasion14, Christmas

But, if you programmed the import function to work with the above formats, you would have to protect against two potential problems. First, if two records had the same ID, then the importing one should overwrite the earlier one so that there are never two records with the same ID. Second, if the child ID does not match any existing record, you would have to display a “missing link” error message in the list of child records until a record with that ID is imported.

I hope that little brainstorm sparks an idea how to program TapForms to let users populate complex hierarchies from .csv files without having to type everything in from the top-most parent down to the lowest child by hand.

Full Steam Ahead!

Chip

[Brendan]

Hello Chip,

Wow, that was a pretty nice suggestion! Thanks!

There’s one glitch to your idea. There has to be the same number of delimiters in the data rows as there are in the header row. Your Project.csv file has 3 commas in the header, but 6 commas in the data rows.

I think another approach would be to allow the Link to Form field type contain some options where you could specify the name of a field you’d like to join with in the other form. That way, as in your example, your Project.csv file would simply contain the Project ID unique identifier column and the information only related to a Project directly (in your case, Name). Your Task.csv file would contain a Task ID and a Project ID. Each Task would contain a unique Task ID, but all the tasks that belong to the same Project would have the same Project ID. The same thing would happen for your Occasion.csv file. It would contain an Occasion ID and a Project ID. Each Occasion would have a unique Occasion ID, but occasions that belong to the same project would all contain the same Project ID.

Hope that makes sense. And thanks for posting this. It definitely gives me something to think about.

Thanks!

Brendan

[Anonymous]

Brilliant!

That sounds elegant. (Lots of power without lots of complication.)

I’m greatly looking forward to it (after you publish the iPad version and add the aggregate calculation functions; folks with iPads and folks who like to keep financial data secure on their portable devices will add up to lots of new customers I expect.)

If I think of any other ideas that won’t add to much work load, I’ll be sure to pass them along on the forums. When you benefit, all of us customers benefit.

Take care,

Chip

(P.S. I read your reply the day after you sent it, but it took me a while to reply. Keep up the good work.)

[jfhermans]

Hello Brendan,

A solution should to give us the ability to set link_from_field_id & link_from_form_entry_id fields from csv. If I set these 2 fields at correct value in your sqllite db it’s working.

Example of csv files :

code



Customers.csv

form_record_id	Name<text>

5499800000368	DOE JOHN

5499800000016	DUPONT JEAN



Invoices.csv

form_record_id	ID<text>	form_record_link_from_field_id<link>	form_record_link_from_form_entry_id<link>

A000/00100123	000/00100123	CBC9C301-9D4B-472E-85A5-2B0BB985179E-1917-0000026B441F67AC	5499800000368

A000/00100124	000/00100124	CBC9C301-9D4B-472E-85A5-2B0BB985179E-1917-0000026B441F67AC	5499800000016

A000/00100125	000/00100125	CBC9C301-9D4B-472E-85A5-2B0BB985179E-1917-0000026B441F67AC	5499800000368

A000/00100129	000/00100129	CBC9C301-9D4B-472E-85A5-2B0BB985179E-1917-0000026B441F67AC	5499800000368

Thanks

[jfhermans]

Test done with 500+ customers and 4000+ invoices. Sqlite db size 21580 Ko. No delay

See screen captures here : http://flic.kr/p/8Rz6Ep

What is missing to be usable ?

– Import of form links fields

– Auto-update of a set of databases (one category) from dropbox. Link DB-DropBox csv, auto emptying and reimport of last version of csv in dropbox.

Brendan, if you want to go in this direction I do the tests.

PS : To be able to fullfil the links fields I have created 2 text fields for the 2 links, put these fields in ‘Order’ fields and then copied from ‘Order’ fields to links fields via a query on sqllite db.

[Author]

Posts