Still having problem converting SQL to Aurelius

I still have problems to convert a 'normal' SQL query to an Aurelius one.
For instance, if I have this query:

SELECT orderid, MAX(tijdstip) FROM orderflow
WHERE NOT(status IN ('T999','S999','T998','S998'))
GROUP BY orderid
ORDER BY orderid

how would I convert that to Aurelius?
I tried something like this, but it does not seem to work:


you must ListValues instead of List because you are using projections.

And the Where part you can use SQL expression:

.Add(not TLinq.Sql('{status} IN (''T999'', ''S999'', ''T998'', ''S998'')')

You are right, I must use ListValues. However, it still does not work.
I now have this:

        Results := OM.Find<TOrderflow>.Select(TProjections.ProjectionList
                                             .Add(not TLinq.Sql('{status} IN (''T999'', ''S999'', ''T998'', ''S998'')'))

I know it is not correct but could you please tell me which order is correct?

I also keep getting another error: UnexpectedFieldType TOrders. That is because orderid is a Foreignkey to the table TOrders. I guess I will have to make an even more complex call then?
The TOrderflow class is like this in Aurelius, how can I perform a query like above on the orderflow table?

  [Id('Forderflowid', TIdGenerator.IdentityOrSequence)]
  Torderflow = class
    [Column('orderflowid', [TColumnProp.Required])]
    Forderflowid: Integer;
    [Column('tijdstip', [])]
    Ftijdstip: Nullable<TDateTime>;
    [Column('status', [], 7)]
    Fstatus: Nullable<string>;
    [Association([], [])]
    [JoinColumn('orderid', [], 'orderid')]
    Forderid: Torders;
    property orderflowid: Integer read Forderflowid write Forderflowid;
    property tijdstip: Nullable<TDateTime> read Ftijdstip write Ftijdstip;
    property status: Nullable<string> read Fstatus write Fstatus;
    property orderid: Torders read Forderid write Forderid;

I have figured it out! Sorry to have wasted your time. It's too bad I can't delete the last two posts above.
Anyway, the result is:

        Results := OM.Find<TOrderflow>
          .CreateAlias('orderid', 'or')
          .Add(not TLinq.Sql('{status} IN (''T999'', ''S999'', ''T998'', ''S998'')'))

Is it possible to do a more complex query in Aurelius, one like this? Or is Aurelius not meant for this:

select ofl.orderid, MAX(ofl.tijdstip) as tijdstip from
orderflow ofl left join statussen sta on ofl.statusid = sta.statusid where not
(sta.status IN ('T999','S999','T998','S998')) group by ofl.orderid order by
) as ofl
left join orderflow ofl2
on ofl.tijdstip=ofl2.tijdstip and
ofl.orderid=ofl2.orderid where ofl.orderid IS NOT NULL

Unfortunately currently there is no way to query a sub query (select from a subquery).

OK, thank you Wagner. I'll do it in 2 steps then.