I'm not sure if I'm missing something simple here - when creating a mobile app, how would I display a list of records with data from a database entity as well as a local entity? For example (keeping it simple): I may have a server entity with some general contact information, and a local entity with favourite contact info (i.e. ContactId and IsFavourite). How would I then only retrieve and display the "favourite" contacts in a list.
I suggest that you use a Data Action to fetch only the favorite contacts. For example: place a local variable on your screen having the list of favorite contact ids and use that list to filter the contacts returned by the data action. This way only the contacts you want will be returned (and travel in the network).
Late reply. I've sussed out what you've mentioned above but it isn't quite working with how I currently have the screen actions set up (which could be done wrong).
Aim: I want a list of Contacts from database entities displayed, and have the ability for the user to view only "favourite" contacts which would be stored in a local entity.
I am currently using 'Fetch data from other sources' with an SQL action to retrieve the Contact data I want from the server entities - that bit is easy. I can't seem to use a List type local variable within these SQL actions to then filter only the favourite.
If I haven't explained anything well enough or you need more context please advise.
So your problem is with the query.
Here you have 2 alternatives:
- Use and Aggregate to get all the records from the db and then use List Filter to filter only the records you want (the favorites' list) - this approach is more simple although not the best performance-wise
- Use and Advanced Query to get the records you want (using the SQL IN operator to filter records from the list of favorites) - more complicated but better performance-wise
I don't think I can pull the data from an aggregate as it comes from multiple database entities and Unions 2 sets of results - the SQL action was the only way I could figure out how to achieve this.
With the second option you have mentioned, is this within my "Fetch data from other sources" (is this called a screen action? I don't really know the lingo). I'm assuming when I mentioned the SQL action this is the same as the Advanced Query you mentioned? How do I use SQL IN operator? When writing the SQL for this it only allows me to use Server Entities and not Local Entities.
Aggregates support multiple entities with joins.
On server-side you can use Advanced Query. Check out here what I'm talking about: https://success.outsystems.com/Documentation/10/Developing_an_Application/Use_Data/Query_Data/Write_Your_Own_SQL_Queries
"Aggregates support multiple entities with joins." - I don't think I can perform Unions this way?
The advanced Query you mention is what I currently have, however you can only select server side entities - not server and local. Additionally, with the Query Parameters you can not have a List type parameter - so I would not be able to input a list of Favourite contact IDs for filtering via SQL.
The idea is to have a server action that receives a list of favorite Ids. Inside that action you convert that list into a comma separated string of Ids and pass it into an advanced sql node. In the node you should use that parameter in an SQL IN statement (check more https://msdn.microsoft.com/en-us/library/ms177682.aspx) to filter the Ids that you want.
Sorry, link does not work.
I didn't realise the link was just about SQL In function - I got SQL covered. How do I get a server action to receive the list of favourite IDs from local storage?
Create an input parameter of list type to receive the Ids from the client.
I've got that. But I can't use a list type parameter within the SQL tool in an action flow.
You should convert the list into a comma separated string of ids before passing to the Advanced SQL node.
Oh sorry, I get what you mean now. How do I convert the list into CSV?
Iterate over the list of ids (ListOfFavorites) and concat all the ids into a variable (FavoriteIds) using an assign node.
FavoriteIds = If(FavoriteIds <> "", ",", "") + ListOfFavorites.Current.Id