Filter not working for a Case statement in Datagrid

Hi Team ,

I have used an Column (timediff) which produces html content in datagrid that is used to show an indicator (Green/Red) in table based on the computed values in the query.We need a functionality that include a filter to filter this indicator.So i have used an extra column (timediffindicator) for this to work which sets a flag based on condition as given below

YAML

CASE WHEN TIMESTAMPDIFF(MINUTE, a.start, att.createdAt) > 30 THEN '<div style="width:15px;height:15px;border-radius:7px;line-height:20px;text-align:center;background:#ff4300;align:center;margin:0 auto"></div>' WHEN TIMESTAMPDIFF(MINUTE, a.start, att.createdAt) IS NULL THEN '<div>&nbsp;</div>'ELSE '<div style="width:15px;height:15px;border-radius:7px;line-height:20px;text-align:center;background:#bdff00;align:center;margin:0 auto"></div>' END as timediff

1

CASE WHEN TIMESTAMPDIFF(MINUTE, a.start, att.createdAt) > 30 THEN '<div style="width:15px;height:15px;border-radius:7px;line-height:20px;text-align:center;background:#ff4300;align:center;margin:0 auto"></div>' WHEN TIMESTAMPDIFF(MINUTE, a.start, att.createdAt) IS NULL THEN '<div>&nbsp;</div>'ELSE '<div style="width:15px;height:15px;border-radius:7px;line-height:20px;text-align:center;background:#bdff00;align:center;margin:0 auto"></div>' END as timediff

YAML

CASE WHEN TIMESTAMPDIFF(MINUTE, a.start, att.createdAt) > 30 THEN 1 WHEN TIMESTAMPDIFF(MINUTE, a.start, att.createdAt) IS NULL THEN 2 ELSE 3 END as timediffindicator

1	CASE WHEN TIMESTAMPDIFF(MINUTE, a.start, att.createdAt) > 30 THEN 1 WHEN TIMESTAMPDIFF(MINUTE, a.start, att.createdAt) IS NULL THEN 2 ELSE 3 END as timediffindicator

So As per my understanding when i filter the timediffindicator for value as 1 , it should display respective columns in timediff with #ff4300 indicators only displayed in table . But this filter is not working & an error is shown in Browser as shown :

Error during Filter

Please find the filter not working for the indicators shown below (Ecart Horaire? => timediff Column):

Screenshot of Indicator & Filter

Please find the attached Datagrid Code given below:

YAML

attendance-report: source: type: orm acl_resource: salesmentargets.target_index query: select: - cbu.name as BusinessUnitName - cbu.id as businessunitid - a.start - cntxt.name as Client - cntxt.clientnumber - TIMESTAMPDIFF(MINUTE, a.start, att.createdAt) as timediffvalue - CASE WHEN TIMESTAMPDIFF(MINUTE, a.start, att.createdAt) > 30 THEN '<div style="width:15px;height:15px;border-radius:7px;line-height:20px;text-align:center;background:#ff4300;align:center;margin:0 auto"></div>' WHEN TIMESTAMPDIFF(MINUTE, a.start, att.createdAt) IS NULL THEN '<div>&nbsp;</div>'ELSE '<div style="width:15px;height:15px;border-radius:7px;line-height:20px;text-align:center;background:#bdff00;align:center;margin:0 auto"></div>' END as timediff - CASE WHEN TIMESTAMPDIFF(MINUTE, a.start, att.createdAt) > 30 THEN 1 WHEN TIMESTAMPDIFF(MINUTE, a.start, att.createdAt) IS NULL THEN 2 ELSE 3 END as timediffindicator from: - { table: %oro_calendar.calendar_event.entity.class%, alias: a } join: inner: - { join: a.account_89f0f6f, alias: cntxt } - { join: cntxt.owner, alias: cu } - { join: cu.owner, alias: cbu } left: - { join: PGAClientVisitBundle:Attendance, alias: att, conditionType: WITH, condition: 'att.calendarEventId = a' } - { join: PGAClientVisitBundle:VisitReport, alias: vr, conditionType: WITH, condition: 'vr.calendarevent = a' } bind_parameters: - uri columns: BusinessUnitName: label: pga.clientvisit.visitreport.businessunitname Client: label: pga.clientvisit.visitreport.name clientnumber: label: pga.clientvisit.visitreport.clientnumber start: label: pga.clientvisit.visitreport.timeofmeeting frontend_type: datetime timediff: label: pga.clientvisit.visitreport.timedifference frontend_type: html properties: id: ~ filters: columns: BusinessUnitName: label: oro.business_unit.label type: string data_name: BusinessUnitName Client: type: string data_name: cntxt.name label: pga.clientvisit.visitreport.name clientnumber: type: number data_name: cntxt.clientnumber label: pga.clientvisit.visitreport.clientnumber start: type: date data_name: a.start label: pga.clientvisit.visitreport.timeofmeeting timediffindicator: label: pga.clientvisit.visitreport.timedifference type: choice data_name: timediffindicator options: field_options: choices: 1: 'Yes' 2: 'blank' 3: 'No' options: export: true

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73

attendance-report:         source:             type: orm             acl_resource: salesmentargets.target_index             query:                 select:                     - cbu.name as BusinessUnitName                     - cbu.id as businessunitid                     - a.start                     - cntxt.name as Client                     - cntxt.clientnumber                     - TIMESTAMPDIFF(MINUTE, a.start, att.createdAt) as timediffvalue                     - CASE WHEN TIMESTAMPDIFF(MINUTE, a.start, att.createdAt) > 30 THEN '<div style="width:15px;height:15px;border-radius:7px;line-height:20px;text-align:center;background:#ff4300;align:center;margin:0 auto"></div>' WHEN TIMESTAMPDIFF(MINUTE, a.start, att.createdAt) IS NULL THEN '<div>&nbsp;</div>'ELSE '<div style="width:15px;height:15px;border-radius:7px;line-height:20px;text-align:center;background:#bdff00;align:center;margin:0 auto"></div>' END as timediff                     - CASE WHEN TIMESTAMPDIFF(MINUTE, a.start, att.createdAt) > 30 THEN 1 WHEN TIMESTAMPDIFF(MINUTE, a.start, att.createdAt) IS NULL THEN 2 ELSE 3 END as timediffindicator                 from:                     - { table: %oro_calendar.calendar_event.entity.class%, alias: a }                 join:                     inner:                         - { join: a.account_89f0f6f, alias: cntxt }                         - { join: cntxt.owner, alias: cu }                         - { join: cu.owner, alias: cbu }                     left:                         - { join: PGAClientVisitBundle:Attendance, alias: att, conditionType: WITH, condition: 'att.calendarEventId = a' }                         - { join: PGAClientVisitBundle:VisitReport, alias: vr, conditionType: WITH, condition: 'vr.calendarevent = a' }             bind_parameters:                 - uri                                  columns:             BusinessUnitName:                 label: pga.clientvisit.visitreport.businessunitname             Client:                 label: pga.clientvisit.visitreport.name             clientnumber:                 label: pga.clientvisit.visitreport.clientnumber             start:                 label: pga.clientvisit.visitreport.timeofmeeting                 frontend_type: datetime             timediff:                 label: pga.clientvisit.visitreport.timedifference                 frontend_type: html                      properties:             id: ~         filters:             columns:                 BusinessUnitName:                     label: oro.business_unit.label                     type: string                     data_name: BusinessUnitName                 Client:                     type: string                     data_name: cntxt.name                     label: pga.clientvisit.visitreport.name                 clientnumber:                     type: number                     data_name: cntxt.clientnumber                     label: pga.clientvisit.visitreport.clientnumber                 start:                     type: date                     data_name: a.start                     label: pga.clientvisit.visitreport.timeofmeeting                 timediffindicator:                     label: pga.clientvisit.visitreport.timedifference                     type: choice                     data_name: timediffindicator                     options:                         field_options:                             choices:                                 1: 'Yes'                                 2: 'blank'                                 3: 'No'         options:             export: true

Please need some suggestion on how to make filter work for this situation explained above.

Thanks & Regards,
Shreyas S

Topic

[Yevhen ShyshkinParticipant]

Hello.

Probably, the easiest fix for this issue is to cast your string to integer, i.e.:

- CAST(... as integer) as timediffindicator

1	- CAST(... as integer) as timediffindicator

[shreyasParticipant]

Hi Yevhen,

Thanks a lot for the help. It solved my issue.

Thanks & Regards,
Shreyas S

[golriz.nouraniParticipant]

Hi
I have this problem for other type like string

YAML

- Case WHEN address.district IS NOT NULL THEN address.district ELSE customerAddr.district END as visitDistrict

1	- Case WHEN address.district IS NOT NULL THEN address.district ELSE customerAddr.district END as  visitDistrict

filter does not work for this column

YAML

filters: columns: visitDistrict: type: string data_name: visitDistrict

1 2 3 4 5

filters:             columns:                 visitDistrict:                     type: string                     data_name: visitDistrict

could you help me?
Thanks
Golriz

[Author]

Replies