Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191

    Answered: Creating my music database - some help needed

    I have finally found the tool that enables me to create the application I have always wanted : My music database.
    With Brilliant Database I have been able to create all forms I need in just a few hours.

    I have folders for Artists, Tracks, Media, Hits, Ratings, Labels, ChartRuns. They all are related to eachother somehow.

    But there are still some things I need help with. Tried to find the solution on Brilliant's website and using Google, no luck so far.

    Let's start with something I think should be simple.

    The Artists form has a many-to-many relationship with Tracks. So on the artist form I see the tracks by that artist. One of the columns in the track list is the Rating for that track.
    I have added a field for the 'Average rating' for the artist, using the function 'mmrMathOp(|Tracks|,|Current Rating|,|SimpleAverage|)'.
    The only problem with this, is that it also takes the tracks without a rating (where the rating is 0). I would like to exclude the tracks where rating = 0.
    I'm not sure if I can use the IF statement for that, and how to use that with the mmrMathOp statement.
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  2. Best Answer
    Posted by Tubbritt

    "Hello.
    See attached file. I've added 3 global variables and a filter.

    One variable makes a list of all the tracks that are in your many to many field and it uses a filter to keep out any track that's not rated.

    The second variable then counts how many records there area after the ones with no rating are filtered out.

    The third variable gives you the SUM of all ratings.

    And finally a maths formula divides the SUM by the amount of records rated, which in turn then populates your Artist Ratings field with the value you wanted.

    Regards
    James"


  3. #2
    Join Date
    Jul 2012
    Location
    Ireland
    Posts
    815
    Provided Answers: 17
    Hello.
    No problem, see attached. I've very quickly thrown this together but it works out the average rating, as well as the number of songs that are rated, and the number that contain no rating.

    Regards
    James
    Attached Files Attached Files

  4. #3
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    Thank you! Now on my way home from work. Will check when I get there

  5. #4
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    Well, that almost works for me.
    Problem is that when I do that, it will get the average of all ratings of all artists.

    In your example I can edit your query to match the artist in the 'songs' folder to the artist in the Blanks folder. Then it works.

    But in my database I would like to match the Artist_ID with the artist_ID linked to the tracks, but I don't know how.
    (Prefer not to use the artist name for matching, because some artists could have the same name.)

    I have attached my database.
    Attached Files Attached Files
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  6. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if one of the pre existing libraries doesn't cut it for you, then perhaps at least base it on one fo tghe pre existing schema
    ...the great advanatge of the exisitng library management tgools is that they work
    whether you chose to use iTunes, Windows Media Player, Squeezebox, Real Player or whoever.
    heck I'd be tempted to use a more open/commoin standard such as DLNA https://www.google.co.uk/webhp?sourc...edia%20library

    they all can scan for exisiting music files.
    granted sometimes theres a few issues, but if you use a pre exisitng service it reduces drmatically the amoun of worjk you end up doing.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #6
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    Not sure what these libraries do, but I'm not yet planning to manage media files with this database.

  8. #7
    Join Date
    Jul 2012
    Location
    Ireland
    Posts
    815
    Provided Answers: 17
    In your example I can edit your query to match the artist in the 'songs' folder to the artist in the Blanks folder. Then it works
    Yes, correct. It's a simply way of filtering the data exactly how you want it. Query's are awesome :-)

    But in my database I would like to match the Artist_ID with the artist_ID linked to the tracks, but I don't know how.

    (Prefer not to use the artist name for matching, because some artists could have the same name.)
    That would be no problem if both your Artist and Tracks form contained an Artist ID field, but they don't. You only put that field on one of the two forms. Which poses quite a problem.

    Leave it with me for a moment and I will see if I can think of a way around this without suggesting that you go back and add the missing Artist ID field to the tracks form.

    Regards
    James.

  9. #8
    Join Date
    Jul 2012
    Location
    Ireland
    Posts
    815
    Provided Answers: 17
    Hello.
    See attached file. I've added 3 global variables and a filter.

    One variable makes a list of all the tracks that are in your many to many field and it uses a filter to keep out any track that's not rated.

    The second variable then counts how many records there area after the ones with no rating are filtered out.

    The third variable gives you the SUM of all ratings.

    And finally a maths formula divides the SUM by the amount of records rated, which in turn then populates your Artist Ratings field with the value you wanted.

    Regards
    James
    Attached Files Attached Files

  10. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Ruudboy View Post
    Not sure what these libraries do, but I'm not yet planning to manage media files with this database.
    My suggestion has more to do with how you populate your data. I dont know how easily BDB could integrate with software which can strip out the metadata embedded in music files. Im guessing it should be easy enough to download a gracenote entry for an album (or what ever that db is now called if still available).

    It isnt so much about the playing of the media, but instead the data handling. I dont know how much music you have got but manually entering all the metadata for even a couple of CD's can be an error prone PITA, let alone yhe hundreds some people have amassed over the years.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #10
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    It would be helpful if I could import data from a website like Discogs.
    My music collection is over 7000 physical media and over 300.000 mp3's so yes, it would be quite some work to enter everything manually.

    But let me first get at least something I can work with, and then see to add functionality like importing data.
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  12. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    with 300,000+ MP3's then at the very least explore using one of the pre-existing libraries BEFORE writing your own code, even if only to do your data take on, then stuff that data into whatever your end application is. personally I'd suggest using squeezebox, but thats because I use Squeezebox myself, there's other options out there. If i were you I'd be temtped to look at a DLNA server such as http://serviio.org/ I have no knowledge of the product, Im not related to them in any way shape or form. just the DLNA doesn't require specific branded hardware. As said before I use Squeezebox (big thansk to Logitech for stopping producing squeezebox equipment, its much appreciated )

    However one thing to be aware of is that the publicly available CD library information db's have user contributed data which can sometimes be wrong.
    there can be typos, mispellings. there can differneces between the same artist/composer /whatever. this is more of a problem with classical music where you may have several spellings for the same name, or a composition may havbe more thasn one name, or worse yet a composer may have several compositions with ostensibly the same name
    they are also sometimes contradictory. ferinstace a record company may have released the same title CD with the same unique identifier code but with different content (different tracks, different track order). Sometimes the same album correctly identified may have a differnet title between say Europe and America.

    If you want to stay out of ther clutches of Microsoft or Apple and not use Access or Fielmaker then consider using Libre Office's or open Office's DB offerigns
    I'd rather be riding on the Tiger 800 or the Norton

  13. #12
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    Quote Originally Posted by Tubbritt View Post
    Hello.
    See attached file. I've added 3 global variables and a filter.

    One variable makes a list of all the tracks that are in your many to many field and it uses a filter to keep out any track that's not rated.

    The second variable then counts how many records there area after the ones with no rating are filtered out.

    The third variable gives you the SUM of all ratings.

    And finally a maths formula divides the SUM by the amount of records rated, which in turn then populates your Artist Ratings field with the value you wanted.

    Regards
    James
    It took me a while figuring out how you did it, how the variables work and where to add the script. But I got it now.

    I still need to understand the difference between the variable and the query. You use a variable for getting the track list where ratings > 0. I would say that's a query.
    Would it also work using a query instead of a variable?

    There's a lot of overviews and lists I would like to get out of the database, so understanding the queries and scripts would help
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  14. #13
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    Quote Originally Posted by healdem View Post
    with 300,000+ MP3's then at the very least explore using one of the pre-existing libraries BEFORE writing your own code, even if only to do your data take on, then stuff that data into whatever your end application is. personally I'd suggest using squeezebox, but thats because I use Squeezebox myself, there's other options out there. If i were you I'd be temtped to look at a DLNA server such as http://serviio.org/ I have no knowledge of the product, Im not related to them in any way shape or form. just the DLNA doesn't require specific branded hardware. As said before I use Squeezebox (big thansk to Logitech for stopping producing squeezebox equipment, its much appreciated )

    However one thing to be aware of is that the publicly available CD library information db's have user contributed data which can sometimes be wrong.
    there can be typos, mispellings. there can differneces between the same artist/composer /whatever. this is more of a problem with classical music where you may have several spellings for the same name, or a composition may havbe more thasn one name, or worse yet a composer may have several compositions with ostensibly the same name
    they are also sometimes contradictory. ferinstace a record company may have released the same title CD with the same unique identifier code but with different content (different tracks, different track order). Sometimes the same album correctly identified may have a differnet title between say Europe and America.

    If you want to stay out of ther clutches of Microsoft or Apple and not use Access or Fielmaker then consider using Libre Office's or open Office's DB offerigns
    I will have a look at the possibilities with these libraries. Thanks!
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  15. #14
    Join Date
    Jul 2012
    Location
    Ireland
    Posts
    815
    Provided Answers: 17
    It took me a while figuring out how you did it, how the variables work and where to add the script. But I got it now.
    There's two places to look, one is when the record is simply selected, and the second is when the many to many filed is edited. This way depending on how your operating the database, the changes you make are noticed and the value is always calculated at the right time.

    I still need to understand the difference between the variable and the query. You use a variable for getting the track list where ratings > 0. I would say that's a query.
    Would it also work using a query instead of a variable?
    A Query is a search though All Records, based on whatever criteria you define. Because of how you created your database, that was not an option any more because your Artist ID number is not stored in your Tracks, so there was no way to use a Query to find tracks that matched the artist ID because you never put the Artist ID into any of the tracks.

    So I came up with a way to use variables to look at the content of the many to many field. So there's no search taking place. The tracks to calculate are known and defined as the contents of the many to many relation field you created. Using variables it was then possible to count the number of songs, while also ignoring those who had to rating and to get a SUM value to divide by the number of songs that had ratings.

    Regards
    James

  16. #15
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    I'm now trying to add a button to the "Top 40 Chartruns" form, allowing me to enter a chartrun for a track quickly.
    What needs to be done when the button is pressed:
    - Save current record
    - remember the date and hit field (clone?)
    - Close window
    - Add new record
    - Link to the same hit record
    - Enter the date of the previous record + 7 days (next week's date)
    - Focus on the position field.

    I don't know where to start. The choice of options when adding an action are too many. Could someone create the button for me (so I can use that to learn for the other buttons I want), or point me in the right direction?
    Also, is there some document describing all these action options?

    (By the way, would it be better for me to open a new thread for this?)
    Last edited by Ruudboy; 03-19-15 at 05:28.
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •