I'm writing an app that uses ClientDatasets in the flat file mode, using the Filename property. I don't have a clear master / detail relationship anywhere, which may or may not be part of the problem. What I'm trying to solve goes something like this:

cds_artists
ID (AutoInc)
Name

cds_recordings
ID (AutoInc)
Name
ArtistID

cds_reviews
ID (AutoInc)
Name
ArtistID
RecordingID
Issue (Integer)

My Recordings panel shows the Artist Name using a lookup from cds_artists
My Reviews panel shows Artist Name and Recording Name also using lookups

The client would like to be able to sort Recordings by Issue (and some other ways, but this would be a first step...)

Apparently (correct me if I'm wrong) I can't use SQL directly with ClientDataset. Since this is local, there is no TProvider or remote server to pass a query to. Someone suggested that I use a calculated field in cds_recordings, but I'm not grokking a way this will help. Another suggestion was to add a master Issues dataset. Two assumptions being made (with the client's approval) are that no recording will ever be reviewed more than once, and the same review will never appear in more than one issue.

Can someone point me in the right direction? I'm working basically for free at the moment (silly me), and the more time I spend on this, the less walletful impact it will have.

TIA!