Sub Summary reporting - sorting based on calculated fields
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?
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)