AnsweredAssumed Answered

Sub Summary reporting - sorting based on calculated fields

Question asked by BrianFfar on Aug 12, 2011
Latest reply on Aug 12, 2011 by 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)
 

Outcomes