Hi guys,

I have a group which sorting properties are based on 2 fields: 1st a date field, 2nd a time field,

It's sorting by date fine, but the time sorting is just random.

Has anyone had a similar problem?

Any ideas would be greatly appreciated! 

Thanks,

Regards,

Alex 

 

Hi Alex,

I seem to remember that until SLX version 7 or 7.2, you could only sort a lookup one field at a time. I may be entirely wrong, but without my support team handy (I'm onsite at the moment), that's the only thing I can think of.

Ben

If you have a time field then you need to be aware that there is actually a date portion to it anyway. All dates and/or times in slx are actually held internally as a datetime. In your case, you will have a time portion to the date you see and a date portion to the time you see.

That's why it's sorting oddly.

If you look at the data via SQL - you will find that the date/times for each "date" and the date/times for each "time" both have both components.

Your best best is a calculated field that adds one to the other to compose a single date/time. Or, fix the data. Or, consider using just one field as it's easier to extract the date from a date/time and the time from a date/time than it is to do what you are doing !

See - I told you I was probably completely wrong!

he he ! How's the new job going ? And, how's the travel ?!

Great! I'm staying with the mother-in-law in Battersea during the week if I'm not on site or in the Worcester office, so travel's not really a problem. We're selling our house, but no-one's even looking round houses at the moment - I wouldn't want to be an estate agent at just now!

Nope, just a bit of a nightmare !! Glad we never moved/sold in the end !

Hi,

Thank you very much for your answer!

The 2 fields come from 2 tables that aren't joined, Hum, I don't feel confident enough to create new joins.

The solution I am probably going to chose is to change the control property Date style from desTime to desDateTime (I'm working on a legacy form), and therefore ask the users to enter the date twice, to make sure the 2 dates match...

Unsless there's another way to bind the 2 controls than a calculated field, or get rid of the date on the time control?

regards,

Alex 

 

 

well, there is a way to do this via calculated field but, still suspect you won't end up with what you expect ! Your best (and easiest) option is to do as you describe !

ok, will opt for the solution I describe,

Thanks for your advice!

Alex

 

 

Hi again, 

I've replied a little bit too quickly!

Might be a good idea to look at calculated fields.

Actually, I'm not sure that my 2 tables are not joined.

The first one is the Opportunity one, the second one is a custom one.

The custom one is displayed in the query builder as a child table under the Opportunity table.

But this custom table is not displayed as a child table of the Opportunity table in the "calculated field management window", when I select the Opportunity table as the base table.

The only "child" tables that seem to be displayed in the calculated field window are the ones that have a 1:1 relationship to the main Opportunity table.

Is that correct? Can we create a calculated field from 2 tables that have a 1:many relationship?

Would there be a way to create a calculated field between 2 fields from these two tables anyway?

Thanks,

 Alex
 

Yes, you can - but it's tricky (based on aliases) and, also, you ignore what SLX does in the upper window and just type your query in in the bottom box. But,  to do this is a bit beyond this group (not in terms of intelligence or anything you understand!). But, would take an age to do back and forth - suggest you contact your BP and talk to them about derived calculated fields.

Thx
Mike

Ok, thanks,

Regards,

a.