Group by

  

Hi there, 

I have a pretty basic question. 

I need to perform a 'group by' on some data in an aggregate. However, when I do this I am no longer able to access the other attributes in the aggregate:

As such, all my links to that aggregate break:

I suspect there is a simple way around this?


Kind regards, 

Cole Noble

Hello Cole.

This is how Group By, be in an aggregate or in a SQL works. You can access ONLY what you grouped.
Remember that you can group more than one column. But than, if you group columns that have different values for others that have the same, they will be splitted. If you have information that will always be grouped together, you can group than and replace the source of the widgets in the Table Records with the fields now available.

Cheers. 

Eduardo Jauch wrote:

Hello Cole.

This is how Group By, be in an aggregate or in a SQL works. You can access ONLY what you grouped.
Remember that you can group more than one column. But than, if you group columns that have different values for others that have the same, they will be splitted. If you have information that will always be grouped together, you can group than and replace the source of the widgets in the Table Records with the fields now available.

Cheers. 


Hi Eduarado, 


Thank you for clarifying this. Hmmm, I'm going to have to rethink this then because I have a situation where I want to group by only the first part of a record because the later parts are indeed different (and so they would result in multiple records). 

To be specific, I want to group by Candidate ID, because I want to show a list of all unique candidates. However, I also want to be able to access the placed attribute from the aggregate because if any of jobs a candidate has applied for are ''placed'', the candidate should be shown as such in the table (there are other recruitment stages besides placed and so grouping by Recruitment stage returns multiple records).

Kind regards,

Cole

 

Solution

Hi,

You can have a calculated column with a simple IF, and check the condition, something like IF(RecruitmentStage = 'Placed', 1, 0) and than use an aggregate function like Max (that will return 1 if the candidate was placed or 0 if not), and in the table you use this to show Placed or Not Placed. The same idea could be used in other requirements.

Cheers.

Solution

Eduardo Jauch wrote:

Hi,

You can have a calculated column with a simple IF, and check the condition, something like IF(RecruitmentStage = 'Placed', 1, 0) and than use an aggregate function like Max (that will return 1 if the candidate was placed or 0 if not), and in the table you use this to show Placed or Not Placed. The same idea could be used in other requirements.

Cheers.

Perfect! Thank you so much Eduardo. I decided to separate the data into a separate Placements screen. 

All working now. But this would have solved my problem and I think the trick will be handy for other situations in the future! 


Kind regards,
Cole