Hi Martijn,

In the OutSystems platform thats the default value for a null date (in case it's "Date Time": 1900-01-01 00:00:00), the same way that an Entity Identifier appears as 0 or "".
But when the platform communicates with the database, it translates those values to the equivalent for that database.

Hi Tiago,

Thanks for you quick responce.
So I get the following attribute from the database: RetireDate.
I only want the employee's who are not retired yet so I use the filter: RetireDate = NullDate
Because If an employee isn't yet retired this column in the database is empty.
The result of this filter is that the aggregate fetcheses no more records.

Hi Martijn,
try something like this (the bold part):

ENTITY.ENTRY_DATE <= OTHER_ENTITY.END_DATE or (ENTITY.END_DATE + "" + NullDate() = NullDate())

Hi Joao,
That works tnx alot.
Do you also have something for kind of the same problem but then with text.
I have an Textfield from the database with contains "N" if the Employee is active and null otherwise.
The filter: Employee.IsActive = "N" gives me indeed all the employees that arn't active annymore but something like:Employee.IsActive <> "N" ands up with no records found. 

In the database I can solve this problem with the Where Clause: WHERE IsActive IS null;

Being a text field I would say <> "N" should work...  is that also an imported table? Have you checked if that field has the correct type?

Yes it's an imported table.
The type in the Oracle database is an: varchar2 1 byte
The type in OutSytems is text 1 lenght.

Perhaps you must try (Employee.IsActive <> "N" or Employee.IsActive is null)

The second part how would you write that in OutSystems?
Because Employee.IsActive = "" isn't working

Are you using an aggregate? Can you post a printscreen of what you are trying?

Martijn,
If you are dealing with Oracle you should use Employee.IsActive = " "
Notice the space between double quotes

So I got the advanced query to work:

SELECT *
FROM {CAS_MEDEWERKERS}
WHERE {CAS_MEDEWERKERS}.[IND_UIT_DIENST] IS NULL AND
({CAS_MEDEWERKERS}.[DATUM_UIT_DIENST] IS NULL or 
{CAS_MEDEWERKERS}.[DATUM_UIT_DIENST] > sysdate)

But is this posible in an agregate. 

 

Sorry its in Dutch!

Martijn,
In that case, I would say you should use these 2 filters in your aggregate:

CAS_MEDEWERKERS.DATUM_UIT_DIENST = NullDateTime() or CAS_MEDEWERKERS.DATUM_UIT_DIENST > CurrDateTime()

CAS_MEDEWERKERS.IND_UIT_DIENST = NullTextIdentifier()

Tiago, 

When I use your fist filter. The CAS_MEDEWERKERS.DATUM_UIT_DIENST = NullDate() (NullDateTime isnt recognized) ends up giving no more records.

CAS_MEDEWERKERS.IND_UIT_DIENST = NullTextIdentifier() gives an error: Incompatible datatypes
And 

CAS_MEDEWERKERS.IND_UIT_DIENST = EntityRefTextToText(NullTextIdentifier())

also doenst return any records.

I think I will solve this just by using an advanced query :)

Ok... :)

But aren't the outsystems data types for those attributes "Date" (I thought it was "Date Time") and "Entity Identifer"? It should work... the only difference from my suggestion is to change NullDateTime() and CurrDateTime() to NullDate() and CurrDate() and NullTextIdentifier() to "", like so:

CAS_MEDEWERKERS.DATUM_UIT_DIENST = NullDate() or CAS_MEDEWERKERS.DATUM_UIT_DIENST > CurrDate()

CAS_MEDEWERKERS.IND_UIT_DIENST = ""

PS: Are you sure the conversion, through the extension, was correct?
(I edited this post, I had an incorrection here - it's not good idea to post while waiting for 1 click publish on the other window... fast and perfect doesn't exist :D)

Hi,
yea extension works perfect and published it multiply times without errors.
The CAS_MEDEWERKERS.DATUM_UIT_DIENST is indeed a "Date" but the CAS_MEDEWERKERS.IND_UIT_DIENST isn't a "Entity Identifer" but a "Text" type.

Indeed in your sugesstion was also my first thought but doesn't seem to work strangly. 

Strange indeed. Those filters should work fine... there must be something breaking the aggregate outside the filters scope.

Hi Martijn,
Did you find out how to edit the aggregate to get what you wanted?

No, just implemented an advanced SQL.
Dit also another Workaround in the entities. Still have no idee why the filters are acting so strange.