Creating and maintaining sequences

[Ladd Vagen]

I’ve collected many album recordings on reel-to-reel tapes, mostly my own recordings. I’ve kept track of the songs and the artist in the sequence they were recorded on the reel – this all on paper back in the 1980s. Now, I want to be able to create a catalog database of the reels and the songs on them, including the artist, in the order in which they were recorded. I sometimes have the same song on multiple tapes for various moods.
I’ve created an artist table and a songs table – one (artist) to many (songs) – works great.
I’ve created a reel table with the idea to link the songs (one (reel) to many (songs)) in the order in which they were recorded, pulling in the artist as well. I add them to the reel table in that order, but they don’t maintain the sequence – they default to sorting by name, which isn’t what I need. I can’t put a sequence number in the songs table as the song may appear in different places on different tapes.
Any suggestions, ideas, or brilliant alternatives? Let me know if any further explanation is needed. The reel, artist, and song table are pretty simple.
Thanks, all…

[Brendan]

Hi Ladd,

This is where Table fields come in handy. A Table field is much like a Link to Form field in how it is displayed, but there’s no direct relationship between the Table field and another form. You do link it to another form though, but the idea is that when you select records from the linked form, Tap Forms will copy the values from the selected records into the Table field. It copies the values from any fields that have matching names.

With this ability, you can have a Songs table that mirrors a Songs form that contains the master list of songs, then add an extra field to the Songs table that is the track number. That way you can have the same song in different albums that have different track numbers.

The only downside of this approach is because Tap Forms copies the values from the selected records and doesn’t directly establish a relationship between the records, you wouldn’t be able to go to the Songs form and see a list of albums a song is contained on like you could do with a real relationship.

Hope that makes sense.

Thanks!

Brendan

[Arthur Maccabe]

I have a similar problem that I’m trying to work through. I have a collection of books and a collection of authors. I want to keep track of all of the books for an author and I also want the authors for a book ordered by something other than their names. If I use a table for the authors of a book, it looks like I will need to write a script that builds the inverse relationship for each author.

Alternately, I could use the many-to-many relationship between authors and books, and add a table for each book, that duplicates the author list, providing the ordering I want.

Neither seems ideal. Are there other ways to approach this?

Thanks!

[Sam Moffatt]

Generally for these I suggest a three table setup: one for the books, one for the authors and a third form to join them. You do a 1:M link into the third form from each of the other forms with show inverse relationship set, use calculation fields to materialise details from the parents form into the third form and then you put your ordering field inside this form. Then on the books form you can select the fields from the author you are interested in displaying and then use something like a “book ordering” number field to sort on to control the ordering it shows up on. Similar with the author form you select the book fields you are interested in and then you can have an “author ordering” number field which controls where the book shows up in the author’s list.

This allows you to control sorting on both sides, calc fields keep data in sync (no duplication) and you don’t duplicate data.

[Arthur Maccabe]

Awesome! Thanks for the quick post. It would have taken me a very long time to figure this out.

[Sam Moffatt]

One more thing to help you on the journey, here’s a link to creating a script that will automatically count the number of child records. If you go with the above advice, you’ll need to update the script to look at each side (one for author and one for books) and it’ll automatically set the value as you create them into a corresponding field. Obviously you’d need to change the field ID’s over and what not. It’s not foolproof but it generally works for me. There is a follow up post that goes into details about what the script is doing and why.

It’s derived from my own use with order to order item/line item that auto creates the line item number.

[Arthur Maccabe]

Thanks. I’ve been busy updating my database — migrating from the original many-to-many, to a pair of many-to-one’s with the intermediate joins. I ended up writing a script to create the join records and then built the links by hand.

One thing that I’ve noticed is the calculated fields don’t seem to update — I have to re-save the calculation to force an update. Is this common, or have I done something wrong?

[Sam Moffatt]

Calc fields should update when their referenced fields update, sometimes it’s a little buggy across links but I’ve generally found poking the record refresh button kicks things into action. I do also find that pressing the “refresh records list” will also generally shake out anything weird.

[Arthur Maccabe]

On to the next issue — data entry. Every time I add a book, I need to create an individual “join” record for each author and then link them from the author and book records. I’d like to automate this process. My plan is to create an “entry” form for the book information and author list. A script associated with the form would create the needed structure (adding author records when needed). After running, the script would tag (or delete) the “entry” record so that the structure would only be created once.

Is there a better way to accomplish this task?

Thanks!

[Sam Moffatt]

If you add a record from the book form, when you’re in that subview it should automatically link the parent record automatically. So each new record will be autolinked to the book and you should only have to select the author record via the inverse link. It should give you a search interface to look for it on both iOS and Mac.

You could script it if your author list is small enough with a prompter and list, I think there is an example somewhere on the forum for it where you iterate over the values of the form, put that into an array keyed by author name and then record ID and use that to link things together. You could make this reasonably quick with a prompter loop as well. I don’t think that works great as you get too many authors though because it’s a big pick list to build.

[Arthur Maccabe]

I hadn’t thought through how easy it is to create the “join” records and follow the links. Thanks for all the help!

[Sam Moffatt]

I did a walkthrough of creating this sort of three form structure in a recent YouTube video that walks through creating it. There’s some earlier topics that might be useful but that’s the section dealing with replicating the technique I suggested here.

[Author]

Posts