complex relationship issue--calculation with "IF"?

[chikanyc]

i'm trying to edit existing databases that connects songs with what songs are used in what tv show and for how long.

i'm working with a couple databases.

(1) list of SONGS with unique ids (SONGID)

(2) invoices--each invoice with a unique ID details what songs (SONGID) were used for each show, and how long each song was used for (DURATION)

i took up fmforum user, mr_vodka's suggestion (thank you mr_vodka), and have separate fields for each song entry in the invoice (no repeating fields). so each line item in the invoice is a SONGID and DURATION for the song.

i thought that i would try to create another table that would create a new record every time i entered a SONGID and DURATION on an invoice. this would then hopefully allow me to look at a song in Table 1 and:

(1) see how many times a song was used (record count)

(2) see the total duration of song usage (total of DURATION by SONGID)

(3) get a portal list of what invoices/shows the song was used on

i am been trying to tackle the relationship issue, but am failing pathetically. can someone out there help me please?

Thank you!!!!!

Edited March 14, 2007 by Guest

[mr_vodka]

You are getting on the right track and it was definately wise getting rid of those repeating fields.

What you are looking for is a join table that is a basic line items table. There are numerous posts on here with samples of a basic order/lineitem/product setup.

You can create a portal on your show/invoice layout that displays all the records for the lineitem table. If you want to directly add the record from this portal, then when you are establishing your relationship from the invoices to the lineitem table, make sure you check 'Allow creation of records...' on the lineitem side. If you do not want to create a new record this way, then you can script it so that it would add a new record.

Basically, this line item table would look something like this:

fkInvoiceID    fkSongID  

Invoice1          Song1

Invoice1          Song4

Invoice1          Song6

Invoice1          Song7

Invoice2          Song2

Invoice2          Song5

Invoice2          Song8

Invoice3          Song1

Invoice3          Song2

etc...

You can use a calc field from the song side (lets call it cNumTimeUsed)to do a Count (lineitems::SongID) and then a calc field with time result of ( cNumTimeUsed * duration ) to give you a total time used.

Edited March 13, 2007 by Guest

[chikanyc]

thanks for the response john! for some reason i'm having trouble setting up a line items table in the databases that i'm working with. but will keep trying..

i have a question about the duration calculation tho...song1 could be 60 seconds song, but in invoice 1 it could be used for 30 secs, and then in invoice 2 only 5 secs. is there a way to calculate the sum these differing durations for each song? (we get paid by # of times a song is used, and how long a song is used for)..

thanks again:)

[mr_vodka]

In that case, you need another field in the lineitems table that tracks duration used.

[chikanyc]

Thanks for the advice John. I was able to set up the line items table and link everything together. But I had a question about calculations...

I am able to calculate the # of times a song has been used (cNumTimeUsed), but how do i calculate the cumulative time duration of song usage? i want to use a case or if calculation but cannot figure it out...

Thanks...

[mr_vodka]

Simliar to the calc that counts the number of songs, you can use Sum (lineitems::DurationUsed ) with Time result.

[chikanyc]

THANK YOU!