My Convoluted FMPRODB question

[theladyboo]

I created a database that has a lot of relationships and portals. Everything works except one place and I'm wondering why.

I have a book table, person table, penname table, assignments table, a sales table, and a royalties table.

The book table creates a book and then uses the assignment table in a portal to create new assignments of people of each book.

The sales table creates a sale and uses the assignment portal to figure out what the royalty will be for each person. The problem is I'm using assignments portals instead of royalty portals. Maybe I just answered my own question, I should put a royalty portal in there and use the child related assignment table to place the data.

I'll try that and let you know :-)

My database is very obnoxious looking because I'm rather new. I'll post the schema if I can't figure this out. It's much more than those 3 simple tables above.

[theladyboo]

no, that didn't work and i'm just too confused at this point for anything.

Edited June 9, 2007 by Guest

[theladyboo]

i thought i had attached the pdf. let me try again.

bookdatabaseschema.pdf

[Fenton]

I would think that you could continue from Royalties to Assignments, new table occurrences (TO's), in order to get (eventually) to people.

But I'm unclear exactly what a Royalty is, how it's figured. Also, Payments; is that for payments on a Royalty?

Also, you really should learn to "toggle" your table occurrences. It's really messy to look at now. The little widget in the top right corner will collapse it. There's 3 states; wide open (default), name and keys only, and name only. You likely want the middle one (like "store"). It can be done to all at once; select all, Cmd-T.

If you want to go further, you could learn the "anchor-buoy" method of separate table occurrence groups (TOG). But that's more intermediate. It makes your graph easier to read, but is especially useful in drop-down lists of TOs.

An important fact about FileMaker relationships (TO's). You will often need more than 1 for a table. Because the one unbreakable rule is that you cannot create a "circular relationship" (because it's an ambiguous path). FileMaker won't let you. It helps to think of a TO as an "alias". A corollary fact is that you need to name your TOs so you can tell which is which. Anchor-buoy vastly helps, as it separates things more.

Another corollary fact (now that I've got the spelling right :-) is that a layout is assigned to one TO. You need to know that to work within anchor-buoy, and generally for relationships. The current TO location (layout) is the origin of its relationships (fairly self-evident from the graph). It's also important for the destination, but more flexible; it is possible to "jump" to a different TOG using a non-connected layout, of the same underlying table, as the destination (of Go To Related Record).

[theladyboo]

Hi Fenton,

Yes, I should have rolled up the tables for a better view. :-)

Here's how I figured it to work:

A book has many assignments who are people. This is related by bookid and assignmentid. All the other great stuff that comes with the assignmentid are assigned roles which titles, a royalty percentage base or fixed amount depending on the type, and some other misc. stuff that is not related to anything else but reporting.

My original design had a sales layout that assigned a book to a sale and based on the book it would populate the royalty information into an assignment portal and then from there, a button would be clicked to enter a royalty in the royalties table based on the assignment because sometimes I need the name there but not the royalty.

I thought I had it working, but when I started filling the sales it would

...nevermind...

I just went to go get screenshots for you (it's so cool how I can print to an iPhoto pdf) and the previous version I had before adding the payments table is working how I expected. Now I'm angry. I want to add a register because otherwise I will never know how much I really owe anyone without a payment method. I guess I will figure that out now. If you're really, really, really interested in what I did I'll send you a shell w/o the private author information. It's kind of neat except how I think is kind of strange so it's way out there.

Such is the life of an adhd programmer who has a 6 yo with the same special abilities who taps me on the shoulder every 5 minutes while I'm programming. thanks for all the help, i think i have it somewhat working now.

[theladyboo]

I give up. How I have it setup it will create a new royalty record for each assignment id if it's a new book sale. If it's a sale for a book that has already a recorded sale it will change the salesid of the original royalty record entry and not create a new royalty record for each of the assignments.

I've attached what the layout looks like too. I'm lost now.

Picture_1.pdf

[Fenton]

I don't think this is a difficult relational problem. But I don't really know what the "business rules" are, what/when a royalty exists, nor whether it's all paid at once; both important facts.

But one thing stands out as clunky; "change the sales id of the original royalty record". One does not rely on changing an earlier record in a properly designed structure, so something is off.

[theladyboo]

I know, that's the problem it's changing the sales id and I don't want it to.

I think what I'm doing wrong is trying to create a new record in the royalties table through the assignments portal. I don't know if that's allowed or not.

The rules are this:

A royalty exists every time there is a sale of a book and an assignment (or role) attached to that sale. A payment is entered when the account reaches $25.00 or more, depending on the day of the month. I haven't even worked out how the register will calculate the amount owed and due yet.

What I've resorted to for now, which is costing me time, is creating a royalties id that is a calculation of the salesid & the assignmentid. I'm manually entering it it in the royalties portal and the assignment portal so they match. :-( I'm sure there is some sort of for loop that will count the number of rows in the assignment portal and let me do a calculation, but I'm running out of time and I have to pay people.

Thanks,

boo

[Fenton]

I think the new record goes into the Royalty table. It needs to be scripted, because it does not know which Assignment you mean, until you click in the Assignments portal.

You would create that record by clicking on a button in the Assignments table (maybe a + button, maybe a $ icon; whatever you think). It would know the Sales id from the parent (Sales), and the Assignment from the portal row.

Set Variable [ $assignment_id; Assignments::Assignment id (primary key of portal's table) ]

Set Variable [ $sales_id; Sales::Sales_Id (primary key of current table)

Go go Layout [ Royalties ]

New Record

Set Field [ Assignment_ID (foreign key); $assignment_id ]

Set Field [ Sales_ID (foreign key); $sales_id ]

Commit Record

Go to Layout [ Original ]

Go to Field [ Royalties Due ]

Go to Portal Row [ Last ] (or 1st, if sorted descending by date/time )

[Fenton]

(P.S. You do not need to create calculation fields concatenating 2 IDs. If you need to use the equivalent in a relationship you can just use both keys in the relationship. I don't see that you need them in this case anyway; though you do want both of them in the Royalties table, in their own field.)