5 Replies Latest reply on Aug 12, 2011 1:24 PM by BrianFfar

    Sub Summary reporting - sorting based on calculated fields

    BrianFfar

      Title

      Sub Summary reporting - sorting based on calculated fields

      Post

      Hi all-

      I'm new to Filemaker (actually running the trial right now to see if it's a good fit for my record label).

       

      My situation is as follows:

      Each quarter, I receive a statement from our digital online retailer with all of our sales information.

      I would like to create a report that will tell me how much each individual artist sold over the quarter.

      This would be very easy except for one problem.

      Within each sales record is:

      TRACK_ARTIST - The artist of the original track
      REMIXER - The artist of the remix (although the original track artist is TRACK_ARTIST and is still listed in the record)

      Sales with no TRACK_ARTIST and no REMIXER indicate an entire album sale

      So, in order to determine who gets paid for the individual sale, I've created a calculated field called ARTIST with the following calculation:

      ARTIST = IF (IsEmpty(REMIXER) and IsEmpty(TRACK_ARTIST), "ALBUM" ; IF (IsEmpty(REMIXER) ; TRACK_ARTIST ; REMIXER)) 

      I've tested the output of the ARTIST field, and I know it's working correctly, but when I try and create a sub summary report sorted by ARTIST, it won't sort, even when I indicate ARTIST as the sorting field, and even though I've set the sub summary leading and trailing parts to ARTIST.

       

      Is Filemaker capable of performing the sort on a calculated text field?

       

      Example:

       

      TRACK_ARTIST     REMIXER     TRACK_ID  CATALOG#
      ARTIST_A             ARTIST_B   001              SH001                 Sales for this track belong to Artist B
      ARTIST_A                             002               SH001                Sales for this track belong to Artist A
                                                                    SH001                 Someone bought the whole album (next challenge)
       

        • 1. Re: Sub Summary reporting - sorting based on calculated fields
          philmodjunk

          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 )

          • 2. Re: Sub Summary reporting - sorting based on calculated fields
            philmodjunk

            Also, be sure to clear the "do not evaluate if all the referenced fields are empty" check box.

            • 3. Re: Sub Summary reporting - sorting based on calculated fields
              BrianFfar

              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.

              I have:
              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 :)

              • 4. Re: Sub Summary reporting - sorting based on calculated fields
                philmodjunk

                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.

                Artists----<Artist_Track>----Tracks

                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.

                • 5. Re: Sub Summary reporting - sorting based on calculated fields
                  BrianFfar

                  Thank you again, PhilModJunk.  I will mull this over and see if I can get my head around it.  I truly appreciate your time.