Using Value Lists in a Script

[christoff]

Hi, I hope this question makes sense!

For each record of a certain table I have 2 value lists. ValueList2 is populated based on what I've selected in ValueList1.

Normally I've been creating a new record manually, selecting a value in ValueList1 and then selecting the 1st value in ValueList2, then duplicating the record and selecting the 2nd value in ValueList2 etc etc

If ValueList2 eg has 10 values, is it possible for me to create a new record manually, like I've been doing but then duplicate the remaining 9 records using a script? (So that when I look at the 10 records, ValueList1 has been given the same value, but ValueList2 has 10 different ones?)

[christoff]

sorry...I should also mention that I'm using drop down lists for both ValueList1 and ValueList2

[Søren Dyhr]

Of course you can, but it's in my humble opinion not relational healthy what you're after ... we would need to have a purpose and a context to lean up against here?

--sd

[christoff]

Hi, thanks so much for replying! I'll give you some more information. ValueList1 is a list of albums. when I select an album then ValueList2 is populated by the songs on that album.

When an album earns royalties then every song earns an equal share. eg if there are 10 songs then every song earns 1/10 of the royalties. So I select an album on ValueList1 then select the 1st song in ValueList2 then insert the royalties. Then I duplicate the the record and select the 2nd song etc etc

Rather than me having to manually do this 9 more times, I'd love to be able to use a script that does that and stops after the last song has been given royalties but I haven't been able to work it out ;)

[comment]

The value list is practically irrelevant to what you want to do - what's important are the relationships between the tables. You haven't said much about that, but I'll assume you have the following tables:

Albums

Songs

Royalties

Shares

related as:

Songs >- Albums -< Royalties -< Shares >- Songs 2

Now, after you've entered the AlbumID into a record in Royalties, you can run a script that goes roughly:

Set Variable [ $royaltyID ; Royalties::RoyaltyID ]

Set Variable [ $songIDs ; List ( Songs::SongID ) ]

Set Variable [ $share ; Royalties::Amount / ValueCount ( $songIDs ) ]

#

Go to Layout [ Shares ]

Set Variable [ $i ; 1 ]

#

Loop

Exit Loop If [ $i > ValueCount ( $songIDs ) ]

New Record

Set Field [ Shares::RoyaltyID ; $royaltyID ]

Set Field [ Shares::SongID ; GetValue ( $songIDs ; $i ) ]

Set Field [ Shares::Amount ; $share ]

Set Variable [ $i ; $i + 1 ]

End Loop

#

Commit Record

[christoff]

Hi, thanks for getting back to me again. My database centres around the Songs table. this is mainly due to songs being sold as albums (physical and digital) or songs sold individually (eg iTunes.)

so my table relationships look like:

Writer1 -

Writer2 - Songs - RoyaltiesInput - Albums

Writer3 -

I had to do a different table for each Writer so that each Song Record could have up to 6 different writers.

After seeing your response yesterday I was trying to work with variables as I haven't used them before. I even tried a script which generated a dynamic file name that included a date, but failed to do it...

I think because of my limited filemaker knowledge I was struggling with your script. Does the 'list' in the 2nd line tell it it's referring to a value list?

[comment]

I'd suggest you put the script aside and concentrate on getting your tables and relationships right first. If you have a separate table for each writer, then you are definitely on the wrong track, and there's not much you will be able to do until you fix that.

I now recall your previous thread. However, I am somewhat confused, because your description earlier in this thread is quite different from the description in the other thread.

Here: Albums earn revenues, to be divided among the album's Songs equally;

There: Songs earn revenues, to be divided among Artists by specific percentages.

So I am not sure how the two connect, if at all. I am guessing that if the revenue arrives as a result of selling an album, you want to divide it among the songs (equally) first, then divide each portion among the artists (by their percentages in the song).

If my guess is correct, then the script needs to be quite different (assuming you only need the final result of how much should each artist receive out of the received revenue).

In any case, as explained in the other thread, the solution to the problem of a song having many writers is to have a join table of Percentages between Songs and Artists.

[christoff]

Hi, I understand that I might've caused some confusion.

The relationship based around the Songs table with 5 different Artist/Writer tables is the only one I've been able to get working properly so far.

It's limitation is that it only works for one artist, and I then have to do a clone of the database for any other artists.

With the other post, I tried to get it working but i couldn't do it unfortunately.

[Søren Dyhr]

It's limitation is that it only works for one artist, and I then have to do a clone of the database for any other artists.

This is simply not true, why do you think sub summary reports as such work? Anything copying or cloning is utterly wrong, since they would share attributes ... what would be the benefit of splaying them out in identical looking tables?

What is it you are trying to make it behave as? Is it the ability to give you instant figures for each account that drives this wish? Perhaps you by studying this template:

http://fmforums.com/forum/showpost.php?post/266487/

...Could make you see that even that could be accomplished pretty easy, although the template only Counts( the instances of each, but this could be summing as well.

What you seems to be missing is that a field could be used to categorize such as an account number put to each transaction, instead of reaching out for each card/ledger ... why do you think fields indexing could be turned on? The organisation of the data could be data driven, not just user driven!

--sd

[comment]

With the other post, I tried to get it working but i couldn't do it unfortunately.

Well, I don't want to repeat the previous thread here. Implementing the join table between Songs and Artist is the first step - I don't think it's possible to move further until that is done.

[christoff]

Sorry, by artist in that context I meant band / solo artist / project. So i have to use a database for 1 band (eg say beatles) then clone the database for another band (say the stones). Each band can have up to 5 artists. That's the record royalties side of things.

Then I have another database for publishing which is structured in a similar way. instead of each song having up to 5 artists credited, each song can have up to 5 writers.

the way i set it up works perfectly but the limitation is that instead of all the bands being in 1 database together, I have to seperate them.

So what I was originally asking was: is there a script which says "go to the value list and choose the next value in the list"...coz I couldn't find it

[christoff]

Hi, I did do the join table but I found it didn't work, but that is probably down to me not doing it correctly. I will have another go.

[Søren Dyhr]

Sorry, by artist in that context I meant band / solo artist / project. So i have to use a database for 1 band (eg say beatles) then clone the database for another band (say the stones). Each band can have up to 5 artists. That's the record royalties side of things.

No no no! Cloning is utterly wrong ... I think I'll join Comment here, you do stubbornly refuse to understand what we're saying!

Perhaps you will understand a tiny bit more by reading this?

http://jonathanstark.com/recursive_data_structures.php

But chances are that it only will contribute to the confusion!

So what I was originally asking was: is there a script which says "go to the value list and choose the next value in the list"...coz I couldn't find it

Ha Ha I sat out to prove at point in keeping everything in one table to prevent cloned databases with same purpose, preventing summaries to happen ... I have however littered the attached template with some Socratian noise, by having just one single summary field taking care of all the summing up, well it can handle payouts as well!

But as such is it using the valuelists as a clunky substitute for genuine relational structure!

--sd

FlatfileRoyalty.zip