How Do I Do This

[christoff]

Hi, I'm sure this sort of thing has been covered several times and if someone can paste a link to a previous relevant topic I would be very grateful.

I'll use the actors / movies example. Within the same database if I have 1 table called MOVIES and another table called ACTORS where in both tables each record has 1 actor only and 1 movie only respectively...

Then, is it possible that in each Movie record, I can enter what actors were in the movie, referencing the Actors table.

I can't work out how to do it.

[comment]

You need a third table (Roles?) for this. See a demo here:

http://www.fmforums.com/forum/showpost.php?post/246136/

[christoff]

thanks for pointing me in the right direction! I've been working on the demo and it seems to be working with what I had in mind. the demo has a contacts (actors), organisations (movies) and an affiliations table.

what has spun me out is that when i look at the Affiliations table in layout mode, it's completely different to when I look at it in browse mode. Do you know why?

[comment]

It's in Table view.

[christoff]

ahhh yes. I've added the Roles table but then changed it all to Songs, Artists / Songwriting %etc.

I'm not sure if I'm going to be able to do what I was hoping. I wanted to add another table for Song Revenue where a song earns money and the Artists earn their % of that money and I tried this. It worked for the 1st record but then I can't get the 2nd record to work at all.

Am I trying to do too much?

I've attached what I've done. Would really appreciate it if you were able to have a quick look when you get a chance. Sorry to be a pain

thanks

JoinDemo.zip

[comment]

This is not an easy issue to deal with. Ideally, each revenue would be broken up into separate "share" records in another table - this requires a carefully scripted process.

Without such separation, you can still tell how much an artist earned from each song by doing the calculation in Percentages =

Sum ( Revenues::Amount * Percentage )

And by summing this at the Artists level, you get the total earnings of the artist.

However, this approach has some limitations:

1. If the percentages ever change, past revenues will be re-calculated to fit the current percentages.

2. You cannot see, from the context of Revenues, how the sum should be split up. This can be solved, to some extent, by populating a global field with a value from the currently viewed revenue record. For example, you could have a gAmount field in the Percentages table, and a calculation field =

gAmount * Percentage

The result, placed in a portal to Percentages on a layout of Revenues, will show how the revenue is divided - but only as long as the global amount is "synchronized" with the currently viewed revenue record (scripted navigation or event script triggers are useful for this).

[christoff]

Thanks so much for all your help. I'm going to give your suggestions a go and yes you are right, percentages can easily change over time and the resulting recalculations of previous entries wouldn't be too good.

Thanks again!

[christoff]

I decided to have another look at this and got a bit further but was wondering if you could slightly elaborate on this, would the shares table be linked to RevenueInputs or to Percentages?

UPDATE...i put it in between RevenueInputs and Percentages...seems to be working well...

Ideally, each revenue would be broken up into separate "share" records in another table - this requires a carefully scripted process.

..and I'm just looking at your suggestion now that I've learned a bit more about FM

UPDATE...I've done a script which sets a heap of variables from RevenueInput and Percentages tables to populate SHARES which then populates the SHARES portal on the RevenueInput layout...this method seems to be working well too...

2. You cannot see, from the context of Revenues, how the sum should be split up. This can be solved, to some extent, by populating a global field with a value from the currently viewed revenue record. For example, you could have a gAmount field in the Percentages table, and a calculation field =

gAmount * Percentage

The result, placed in a portal to Percentages on a layout of Revenues, will show how the revenue is divided - but only as long as the global amount is "synchronized" with the currently viewed revenue record (scripted navigation or event script triggers are useful for this).

It might've taken me a while to fully understand your suggestions Comment, I feel I do now...thanks again

Edited June 14, 2009 by Guest
update