Displaying linked form data

[Toby W]

I’m using Tap Forms (both Mac & iPhone) to catalogue books and their authors. So I have two forms: one called Books and one called Authors. The ‘Books’ form has a field called ‘Author’ which is linked to the ‘Authors’ field.

This works well for entering data, but I am finding it difficult to work out how to do even simple tasks with the linked field. For instance:

1. I would like to display a list of books grouped by author. How can I do this? Under ‘Sort & Group Settings’, linked fields seem to be ignored.
2. I would also like to display the author’s name under each book on the list of records. (If I have several books with the same title, I need to be able to see which one is by which author.) However, no matter which option I select under ‘List View Fields’, linked fields seem to be ignored.
3. Finally, I would like to search for all books where ‘Author’ is blank (so I can correct errors where I forgot to record the author). Again, though, linked fields seem to be missing from the search options.

Can anyone help with any or all of these problems? Thanks in advance!

[Jimmy Uptain]

I can answer your third question easily enough.
In the Mac version, if you look in the upper right hand corner, you will see a search box. Just to the left of that you will find a little gear icon.
Click on that. The “search rules” produce a dropdown menu of all your fields. Find “Author”. (this is assuming you named the offending field “Author”)
Beside the first dropdown is another dropdown. Click it, Find “is empty”.
This will show you every every where the “Author” is empty.
Now name your search and save it.

Once you set up the search on the Mac, it will transfer to your iPhone or visa versa.
I generally set up my searches on the Mac, but they are just as easy on the iPhone, however different.

Your #2 issue is a bit more complicated and without actually looking at your database, I cannot explain how or if you could go about solving the problem.

[Toby W]

Jimmy, many thanks for your reply!

Unfortunately, the solution you describe doesn’t work for me, because of the very problem I tried to describe in my first post. Any fields which have a field type of ‘Link To Form’ or ‘Link From Form’ do not appear in the search dropdown. Nor do they appear in ‘Sort & Group Settings’, nor does Tap Forms take any notice of them when counting which fields to display under ‘List View Fields’. Basically, it seems to me that linked fields are ignored.

So when I go to the gear icon and dropdown the first menu under Search Rules, I get a list of fields in my form (Title, Publisher etc), but this list omits any fields which are linked to other forms, such as Author.

I’m sure I must be doing something wrong here — because it’s quite normal in database design to store reused data (such as the names of authors) in a different table, and no doubt that’s why the ‘Link To Form’ type is available in the first place — but I can’t figure out how to display, sort or search records by the contents of a linked field.

[Toby W]

Maybe it will help if I describe what I’m doing that causes the problem, in case this helps anyone spot my mistake!

To reproduce my problem:

1. Create a new form, call it (say) Books.
2. In that form, create two fields: Title and Author.
3. Create a second form, call it Author.
4. In that form, create two fields: First Name and Last Name.
5. In the Books form, change the field type of the Author field to ‘Link To Form’ and choose to link to the form ‘Author’.
6. Create a couple of dummy records in each form; doesn’t matter about the content.

Now: try to sort or group the records in the Book form by Author. You can’t.
Also, try to search for records in the Book form where the Author field is empty (or, indeed, where it contains any data). You can’t.

[Jimmy Uptain]

OK Toby, I did what you asked and you are correct.
I’m not sure that TF is set up to do this, however, I have a suggestion.
In my DB I use the lowest common denominator as my “parent”.
So in your case, that would be “Author”, since many Authors don’t pen the same book but many books could be written by one Author.
So what I did was this:
I set up Author to have the fields, “First” “Last” and “books”.
I set up books to have the fields “Title” and “Author”.
Now in the form “Author”, I linked the field “books” to the form “Books”.
The link is a one to many, and I checked the box “show Inverse Relationship”
Essentially the opposite of what you done.

Now when you are looking at the Author form, the books will show up.
To get them in alphabetical order, go back to the “Books” form.
Now look at the section under “Search”. Find “Sort & Group Settings”
“First Sort Field” will give you a dropdown of the different field you can sort. The little arrows to the right determine the order in which they are sorted.

As far as finding a book without an Author, just go to the “Books” form and change the view to Multi Column list view and sort by “Author”. They will be easy to spot.
Same goes for sorting by Author.

This may be crude but it seems to work for me.

[Brendan]

Tap Forms does not provide a facility to search through Link to Form fields. So it’s not anything you’re doing wrong. I just haven’t written support for that. Sorry.

I understand that it’s a common thing to do in databases, but that’s generally for the bare metal databases such as SQLite, MySQL, PostgreSQL, Oracle, etc. Tap Forms is a consumer level database that attempts to remove all the complexities of the bare metal of databases so people don’t need to be database administrators or programmers. However, I do hope that I can figure out a way at some point to build a feature like that into Tap Forms. There’s so many things I still want to do to Tap Forms. I probably have another 20 years worth of work to do :-)

Brendan

[Leo]

> Tap Forms is a consumer level database <

Brendan, I think you are wrong. Tap Forms is a powerful app for business. All I need is there:

Clients, Sales, Costcalculation, Billing, Suppliers. And to connect each form where dat from another form is needed jus this feature is missing. I solved it by retyping the data from the linked form. It works but wouldn’t it be nice to….

I vote for a place 1 for this feature (after the engine migration is done) :-)

Leo

[Toby W]

Many thanks all.

Brendan, sorry to hear this feature isn’t available yet. I’ve been so impressed with Tap Forms that it surprised me, but I quite understand that you can’t do everything at once. I hope to see this added to a future release.

Jimmy, good tip: seems like a many-to-one relationship is a bit better than a many-to-many relationship. Unfortunately, I need a many-to-many relationship, since there are several books with the same author and some books with more than one author.

[guardajoias]

I certainly would also like to this ability as I also use Tap Forms (among other uses) for my book collection, and run into the same issue. The closest to a solution at this point seems to display the books in the Authors form through “Linked From” and look at the Authors form with the books listed under each author…

[Charles McLane]

I have created a multi-form Tap Forms database and, as others have described in posts above, my database needs the capability to search on the content of linked fields on a form. It would be great to have this capability added to Tap Forms at some point.

I read somewhere on the Tap Forms forum that someone solved the problem by replacing the linked fields on a form with text fields and retyping the data that they had originally intended to link to. This might be a workaround, but it has the potential to introduce typographical errors in various parts of the database for the same object name. And if the name of the object changes (e.g. Mary Smith becomes Mary Jones) the name in the text field on every record would have to be reentered.

If anyone has found another solution that works please let me know. Thanks.

[Author]

Posts