INTERACT FORUM

Please login or register.

Login with username, password and session length
Advanced search  
Pages: [1]   Go Down

Author Topic: Sorting / Grouping Movie Names Expression Query  (Read 1577 times)

stricko

  • Galactic Citizen
  • ****
  • Posts: 423
Sorting / Grouping Movie Names Expression Query
« on: August 10, 2009, 11:04:52 am »

Hi there

I've got a lot of movies and I was trying to build a view that groups them alphabetically. Ah, simple I hear you say, but I've a twist to make it more complicated. I've got lots of movies begining with A, B etc but only a handful beginning with 0-9. I don't really want my group list to begin with 0, 1, 2, 3 etc, I'd prefer a single group containing anything non-alphabetic.

Again, not too hard, I thought about creating a new field that uses an expression to group the non-alpha titles, but then I remembered that I'm not really sorting / grouping on the name itself, but something else where "The" "A" etc has been removed.

So take "The 39 Steps" for example. with the right options set, sorting and grouping will use the first significant character, ie. the 3 of 39 rather than the T of The. But how do I reference the first significant character in my expression?

I've tried to prototype this, with slightly unexpected results. I set up a new field called "first", which had an expression of left([name],1. At first, and I may be mistaken herer, but it seemed to be gfiving me a value of "3" instead of the expected "T". Pleased with this, I built another field that used "first" and substituted "#" if "first" was not alphabetic. At this point the value of "first" chnaged from "3" to "T" and no matter what I did I couldn't change it back.

So apart from that little mystery, I'm still stuck with my same problem, how do I group my numeric titles?

Any ideas?       
Logged

stricko

  • Galactic Citizen
  • ****
  • Posts: 423
Re: Sorting / Grouping Movie Names Expression Query
« Reply #1 on: August 13, 2009, 09:55:14 am »

Sorry for the bump guys, but does no one have a way of doing this?

To rephrase the question a little. MC must generate a field, either within the database, or during the a sort process, that excludes "A" "An" "the" etc if the "Ignore Articles" option is set in the Tree & View Options. How can I access that "field" in an expression?

I've manage to get around this for the moment as I import my Movie data from My Movies. This has a field called SortTitle which contained "39 Steps, The", but it's a terrible clunky way around this.
Logged

marko

  • MC Beta Team
  • Citizen of the Universe
  • *****
  • Posts: 8967
Re: Sorting / Grouping Movie Names Expression Query
« Reply #2 on: August 13, 2009, 11:16:13 am »

Here's how I do it for audio.

Scroll up a couple of posts 'till you see a screenshot, and click on it to see it full size. Is that what you mean, and can you bend it to fit your video view?

stricko

  • Galactic Citizen
  • ****
  • Posts: 423
Re: Sorting / Grouping Movie Names Expression Query
« Reply #3 on: August 14, 2009, 01:55:20 am »

Thanks Marko. It would do the trick. I'd have to expand it to deal with more than just "The ", but it could help.
Logged

stricko

  • Galactic Citizen
  • ****
  • Posts: 423
Re: Sorting / Grouping Movie Names Expression Query
« Reply #4 on: August 15, 2009, 12:56:38 am »

Marko

I've use and expanded the expression to replicate what MC is already doing. To keep the formula manageable, I've broken it into 2 stages;

A calculated value field called Initial, which gives the first character of the Name field without "The" "A" or "An" if necessary
A second calculated field called Group, which groups together numeric values of Inital as 0-9

I had to go this way to deal with "The 39 Steps"!!!!!!!!!!!!!!!!!!

But a second question, in you snapshot, you have entries for (various), 0 - 9, etc. but how did you avoid getting the "All Artists (#)" at the beginning of the list? 
Logged

stricko

  • Galactic Citizen
  • ****
  • Posts: 423
Re: Sorting / Grouping Movie Names Expression Query
« Reply #5 on: August 15, 2009, 01:35:45 am »

Just when you think you are getting somewhere................

The new field appear to work Ok, but the sort sequence is getting screwed up. The values of "Group" come out in the following sequnce

0 - 9
B
C
.
.
Z
A

The values of the Initial calculated field look OK......... help
Logged
Pages: [1]   Go Up