DISTINCT

Hi zaba, 

You'd have to write a custom mysql query for that.  What's the total number of records in that section?  If it's not that many you could try just loading all the projects, looping over them, and only displaying the first record from each client in the foreach loop.  It's CPU/Memory time vs development time and would probably run pretty fast if you didn't have too many records.

Hope that helps!

Ok, could you give an example of that, theres not too many records.

Also do you know how I can achieve the second part to my question?

Hey Zaba,

Is the client field a multi select field and does it link to another section in the CMS?

Thanks,

Greg

No the client file is not multi select. in the editor it is pulled in as a single select list. with the value as 'num' (from the clients table) and label as 'the client name'

The sector is a multi select field in the editor this is pulled in as a multi select list. with the value as 'num' (from the sectors table) and label as 'the sector'.

So basically what I am trying to achieve is 

A. select the first 8 projects whose sector (this could contain more than 1 value) is $sector (which is a number passed to the page), 

B. Only display the projects from unique clients. 

Hi Zaba, 

If the client field is a single select list field, then you can use the groupBy option in getRecords to only return one option for each client:

  // load records from 'projects'
  list($projectsRecords, $projectsMetaData) = getRecords(array(
    'tableName'   => 'projects',
    'limit'       => '8',
    'loadUploads' => true,
    'allowSearch' => false,
    'where'       => "`sector` LIKE '\t" . $sector . "\t'",
    'groupBy'     => 'client',
  ));

This will limit the items returned to one per client. 

This post by Chris explains how you can filter on a multi list item list for a section:

https://www.interactivetools.com/forum/forum-posts.php?postNum=2233515#post2233515

I've integrated this into the where statement in code above.

Cheers,

Greg

Note,

changed

  'where'       => "`sector` LIKE '\t" . $sector . "\t'",


TO

  'where'       => "`sector` LIKE '%\t" . $sector . "\t%'",


This checks multiple tab separated entries in the one field

Thats absolutely spot on...

Thanks Greg, you are a genius.

I would be stopping pulling out my hair around about now... if I had any!