Hi Roberto,

Well, the "Not in" or "except" instructions do not exist in our Simple Queries, so without knowing the underlying data model and what you're looking for in it, it's hard for us to pass judgement. Most likely, an Advanced Query would be the way to go, unless there's a different way to design the query you want.

An alternative, though not a good one in terms of performance if you have more than just a few records, would be for you to iterate through the list and create a new list based only on the records you want to keep. This might be useful if you have just a small set of records in the list.

Regards,

Paulo Tavares

Hello Roberto,

Yes you can use Not In on a simple query, you just need to create a sub query outside of the primary query and next build a string of Ids, so on the primary query you will use:

 

Is more complex to do on a simple query, I prefer use advanced query on this cases but is your choise.

I hope thats help

Regards
Hugo Pinheiro

Wow.

That blew my mind - kudos for that. :)

Still, if I read correctly, what you're doing is creating a string of IDs and then searching inside of it for the Id you're looking for, is that it?

In all honesty, that does feel quite dangerous and error-prone, I'd assume you'd have to go a bit farther than the condition you're using.

For instance, let's assume my sub query returns the following list

12,13,17,22,110,210

If I search in it for 12, 13, 17, 22, 110 or 210, they'll all be found, which is neat.

The problem is searching for numbers that aren't there, but that the INDEX condition also finds, such as:

1, 2, 3, 7, 10, 11 and 21

Probably, if you want to use that suggestion, I would recommend making sure that each number has a SPACE character and a COMMA after it. The list would then be

_12,_13,_17,_22,_110,_210,

where _ are spaces, and the query condition would be

Index(SubQueryResultsIds, " " + File.FileIds + ",") = -1

and then maybe the results would be the ones you expect :)

Still, kudos for the suggestion - I love to see the creativity of our community :)

Regards, and keep sharing!

Paulo Tavares

Hello Roberto

So what I understand is you want to exclude records from a record list, query, by not using an advance query,

In this case, you might be able to use a simple query, to get the entire record list, then use a foreach action to cycle through the record list, processing the list to exclude all the unwanted records, then assign the required records into a new record list variable to produce your desired result.

Actually that seems like a great idea, te database model I have is as following
Invoice (Entity used for selling products to a customer)
Payments(Entity used to record the payments done by the customer)

I wanted to create a page where only the invoices who hasnt beign payed appears, I know how to do that with a view or advanced query, but actually now that I use Outsystems, long and complex queries ara not as fun, so I was planning to accomplish the same thing but with simplier methods

Hi Roberto,

all the suggestions do what you want, but with the success of your business the number of invoices will grow a lot, and then you will have performance issues.

So you can actually have several options. First of all, even in outsystems when its more performant we use an advance query, we have to face the facts, if you want performance, and scale, in this case, it would be faster to do it an advanced query and more performance then the for each iteration appointed.

But...there's always a but, you can  make it simple. Why not have a status on the invoice? Because a invoice usually have a life cycle (open, cancelled, suspended and even sometimes payed). Do a static entity named Invoice_Status and make a reference attribute in the Invoice entity.

Based on this last solution you can easily  do your screen and other screens with a simple search parameter.

Cheers, 
RNA

The life cycle is the solution i have right now, but in other solutions I used to have a view with not in included, and I always depend of that query, but in this case I will try this new aproach

Thanks a lot

@Roberto

You can design the system with a simple query and you can always always change the simple query into an advance query later on, It would be recommended that you try and solve the problem with a simple query, and only when it is completely necessary use an advance query (ie when you find that performance is an issue or other requirements that you can not achieve with a simple query) 

 

The most important part of your application above everything else is getting your database designed correctly/in the right way.

 

Ricardo: "But...there's always a but, you can  make it simple. Why not have a status on the invoice? Because a invoice usually have a life cycle (open, cancelled, suspended and even sometimes payed). Do a static entity named Invoice_Status and make a reference attribute in the Invoice entity.INVOICE"

I would have to agree with ricardo, it would be an ideal choice to have an invoice status in your INVOICE table.

Therefore your invoice tables should look something like this

INVOICE

Id 

DateTime

InvoiceNumber
Comments

StatusId

UserMasterId 
 

INVOICE_LINE

Id 

InvoiceId

ItemId

 

ITEM

Id

Code

Name

 

STATUS (Static Entity)

Id 

Name 

 

In the invoice status table, you might have status for "Draft, Sent, Pending, Cancelled, Paid, Paid Partial"

Now you can query your INVOICE table and filter it by status for all paid, all paid partial, all draft, all send, all pending, all cancelled etc.

By the way: you might find the following article useful, Database design with point in time architecture and soft deletes.
https://www.outsystems.com/NetworkForums/ViewTopic.aspx?Topic=[[]TIP]-Database-Design:--Point-in-Time-Architecture-and-Soft-Deletes


hope that helps.