Make sure that you have selected "Text" as the return type for this calculation. I think it's currently set as "number". There's a drop down inside the specify calculation dialog where you select this option.
you can also simplify this calculation as:
If ( IsEmpty ( Remixer & Track_Artist ) ; "Album" ; Track_Artist & Remixer )
Also, be sure to clear the "do not evaluate if all the referenced fields are empty" check box.
That's it - thank you SO much PhilModJunk!
I was missing setting the calculation to Text.
My next challenge is the following, and I know it's going to basically mean restructuring the database/tables, but I view this all as a terrific learning experience - I LOVE this stuff!
As I mentioned before, I have "Album sales" where someone will purchase an entire release (meaning all tracks that are part of the release).
Each release has a designated Catalog number (ex. SH001, SH002, SH003, etc.). So, I can tell by catalog number which whole releases are puchased, but my challenge now is to determine which artists are assoicated with each release, AND, the calculation will need to be able to distribute the whole album sale between the artists associated with it.
I'm new to database construction so please tell me if I'm headed in the right or wrong direction, but I've tried to give this a lot of thought.
I view each release as sort of like a "product". So, wouldn't I need to create a product database? Each product would consist of an Artist, Catalog Number, a portal containing all of the tracks within that product, and a release date.
In conjunction with that, would it also make sense to create a separate artist table so that the Products could relate to the Artists?
I'm not sure where this fits in, but I DO have a TRACKS database which contains an individual record of each track on the label. Perhaps the portal relates to the TRACKS db.
I apologize if this isn't clear - it's not crystal to me either so I'm hoping some of you can usher me in the right direction. I'm not looking for a handout as I truly enjoy digging right in and learning from doing.
I guess, to simply, my goal is to be able to import a statement each quarter, use the same report layouts to determine artist payments.
TRACKS database/table which includes all release information as well as a unique ISRC id
I probably need:
A RELEASES database to group the tracks into releases
I'm winded from typing all of this :)
A Releases (or products) table (Note that I didn't call it a "database") will be needed. You'll also need an Artists table to list every artist (Should be able to put both kinds of artsits in the same table) and a "join" table so that a given track can be linked to more than one artist and also to document the percentage "split" for each artist linked to that track.
And it's also a good idea to add primary key serial number field to at least Tracks, Releases and artists and use them, not an externally supplied value to link them to each other as much as you possibly can. Any time you use an externally supplied value as your primary key, you make yourself vulnerable to issues created when that externally supplied value is changed in some way.
Artist::ArtistID = Artist_Track::ArtistID
Tracks::TrackID = Artist_Track::TrackID
One trick you can take a look at is that you can have Tracks and Releases in the same table so that it's easy to document the sale of any combination of individual tracks and "albums" in the same report. A related table can list the ID number of the tracks that make up a given "album".
This is a method that has been used before to handle the sale of "kits" and special price "package deals". When such a record is added to a sales invoice, a script checks to see if the item is such a "kit" and then uses the set of related records to add additional records for each item that's a member of that "kit". Depending on the desired pricing structure, either the kit entry is left with a zero price and the sum of the individual items = the price charged, or the individual items are priced at zero with the "kit" entry showing the price for the kit as the whole. Both methods can be used in the same system.
I realize that you aren't producing actual sales invoices here, just documenting the sales that have already taken place, but hopefully, you can see a way to apply this logic to the record keeping and payment to artist computations that you do need to use here.
Thank you again, PhilModJunk. I will mull this over and see if I can get my head around it. I truly appreciate your time.