Relationship trouble

[Jenna]

I'm new to the forums, but I've been fighting a battle with Filemaker over the past week, and I just can't win. Basically, I've got a database that tracks the authors and their article titles for a magazine.

There are two layouts roughly corresponding to two different tables; when I enter a record into the first layout ("author") and then run a script that takes me to another layout to enter another record ("publication"), I need the first (author) record to be automatically linked to the second (publication) record. Right now, sometimes this works, sometimes it doesn't.

I've uploaded a simple version of the file, hoping that one of you brilliant people can shed a little light on the problem. I put a more detailed description of the problem to the right of the "Publications" layout.

I built the framework of this database myself, but a developer at my university added the join tables and "CONNECT TO" tables (as you'll see)--and he has since left his job! I can't seem to decipher what he's done and so can't diagnose the problem myself. Can anybody help?

Test.fp7.zip

[efen]

I can't recreate the problem?

If the New author button on the author or publication layout is used then a new author record is created without any links to any [no-existent] publication - these are created when the Add publication button is used.

If an author is created and the layout is switched to publication then no link to the publication table has been created so nothing will appear in the name fields. Is this what you mean?

The Add new title for this author button on the publication layout only creates a new record without any links to the author being created so again nothing will appear in the name fields.

Edited August 3, 2009 by Guest

[Jenna]

Hi efen--

Yes, when the "Add new author" button is clicked all I want FM to do is take me to the author layout (if I'm not already there) and create a new record. You are correct that the problem is when an author is created and the layout is switched to publication (via the "add publication for this author" button on the author layout), no link to the publication table is created (and the proof is that nothing appears in the name fields). This is also the case with the "Add new title for this author" button on the publication layout. How can I create this missing link to the publication table? Modify the script that's linked to those buttons? If so, what script step am I missing?

Thank you again!!

Edited August 3, 2009 by Guest

[efen]

I have added a script to the "Add new title to this author" button and amended a couple of other scripts. See attached.

Does this solve it?

[efen]

the other amended script

[Jenna]

You did it!

I really am so grateful for this...I'm compelled to repay the favor in foodstuffs or writing/editing (my only practical skills). I want to bake and send you some cookies, but I see you're in England. Is there anything else I can send? I'm serious--this is a huge help!

[efen]

Thank you for the offer of goodies but your last post is more than enough thanks.

Please be aware that I didn't check whether the scripts I changed are used anywhere else because if they are they may not now work properly for that.

Before you get too far with this file I think that you have a few field definitions that need correcting:

Number of pages field should be a number, not text.

The total cost field formula just calculates the value of the one record, not the value of the records of the various articles of an author in an issue.

And I am not too sure how or where you can calculate the total payment to an author when each article/publication is given different publicationID foreign keys.

I think that you have some of the fields in the wrong tables for what you want to do. Some - number of pages, piece payment - would be better in the Appear_In table?

Anyway, if you have any further problems just ask. I - or someone else on the forums - will try to help you to resolve them.

[Jenna]

Much obliged for all the care you've taken!

Let's see, here...

I don't use the scripts that you changed anywhere else in the database, so everything's fine in that regard.

Yes, you picked up on the "total cost field formula" problem; that's my next hurdle. Is there a way to get around the issue of each article having different publicationID foreign keys? It seems to me that I should be able to have a calculation or a script "get" all the records whose Author ID matches the one currently displayed on the layout, then, from those records, "get" the ones whose Issue ID matches the one currently displayed on the layout, then sum the "piece payment" fields of those records. It seems logical to me, but I'm not sure how to tell FM to do this (if it's possible in the first place). Does it sound possible to you?

Also, in the full database (not the simplified version I zipped and posted), I've got a tabbed layout, but I want the different tabs to correspond to different tables. FM doesn't seem to allow more than one table per layout (unless you use portals, etc.) Is there any way to have a tabbed interface where each tab corresponds to a different table? I used "Insert Tab Control"...maybe there's a better way?

(efen: If you're at all into literature, I'd be happy to subscribe you to our literary magazine for a year!)

[efen]

I have quickly amended your file so that you can determine how much an author is to be paid for the articles/publications in a journal issue. I have added/deleted a few fields and relationships. (I have also changed some of your field names/relationships/table names so that they made more sense to me.)

The way that I have got around each article having different publicationID foreign keys is to use a calculated volume/issue/year field as a key.

The whole thing is rather clunky at the moment and could be simplified (and needs to be) - I don't have time at present.

Yes, you can only have one table per layout but you can access the content of other tables (with or without a tabbed layout or portals) in the file from that layout if there is a relationship between the tables and the layout table.

Test_1.zip

[Jenna]

efen, I have one more question about this file--and after this, I promise to leave you alone! I really do.

Your file was awesome. I wanted to keep most of its functionality but also tailor it to our office a little more, so I copied a lot of what you did into my own file, rather than importing all my data into yours. (My file has a lot of other tables and layouts that weren't on the test one I sent you--staff, donors and donations, etc--because I'd wanted to keep the zipped file small). But something didn't translate as I copied your graphs and portals to my file, because the portals on the publication page only work for new records (not the ones I imported from my earlier version), and the portal on the author page doesn't work at all, for new or imported records. The biggest problem is with imported records--getting the authors to show up alongside the titles. (I also put some explanations on the layout.) I feel like there is something very simple I'm missing--can you see it?

I'm sorry if this is unclear. The more I think about it, the more confused I get!

I feel badly about asking you to help again, but I can't figure another way. But I'll cut myself off after this, I really will. :D

Master.fp7.zip