Key Component - Choose a Group Field?


Hey Folks,

Looking to add in the SQL Date/Time fields into Keys (as they are stated in the OVER’d Group), but can’t select them when choosing components.

The docs say that a DIM field can’t be used, but nothing stated about a group field.

Any ideas?


Just to be clear. In a record such as:

SqlDateTimeStr STRING(8)
SqlDateTimeGrp GROUP,OVER(SqlDateTimeStr)
SqlDate DATE

Do you want to include SqlDateTimeStr in the key?


Working with the standard templates can sometimes provide a problem, especially range limiting on a browse. The GROUP construct is a Clarion tool that delivers something like DATEPART() or TIMEPART() but the actual stored data on the server is still a single STRING, so you cannot easily break those components into a Key that can be saved on the server. On the server side you could have a separate DATE and a TIME field, that would then easily allow components to be in the key.

I did once added another DATE field on the server, filled at runtime as an exact copy of the DATE in the group, that gave me a template range limit on a key easily but that is a dirty fix since we now have duplicate data. A better alternative that I have done since is to manually control the filter in code at the “ValidateRecord” embed. e.g. do your test on the component and set the [BRW1:RecordStatus] = RecordFiltered. [You need to substitute the correct template generated name with Record:Status]


Nope, I want to include the “SqlDate” DATE field in the key, and the TIME one as well if required.


Right yeah, I guess I was hoping clarion would let me create keys that it understands even if SQL wouldn’t (ie using the DATE and TIME fields in the group).


For sorting, using SqlDateTimeStr in a Clarion Dct defined key should not be a problem. If you want SqlDate in the key for range purposes, I believe you can essentially achieve the same results using a filter defined as:
SqlDateTime>=QueryBeginDate AND SqlDateTime<(QueryEndDate+1).