Querying database into variable?

Querying database into variable?

  

I want to do the following:

I have a function with tho cases. Initial case is when I need a new record to be created in the database. When I create the record, one field has to stay empty, and has to be updated later. When I want to update the row in the database, I need to find the latest entry (biggest Id) for the user, and then update only that one field with a value. How can I do this? I tried to update a row, but I can't seem to aggregate (e.g. find the biggest Id for the user) into a variable. Also, I tried to solve it with a custom SQL query, but I'm stuck at step one: "Add a SQL tool to an action flow;" I can't find any kind of SQL tool to add to the action flow.


The empty field on the screenshot should be the highest Id for the user in the database.

Thank you!

Before the updating node, call an aggregate to retrieve the logger table.(Call it GetLoggerByUserId)
Create a filter in the aggregate where Logger.TheuserId = GetUser.Id ( from the first node the first node).

Since you want the latest logger id, select the primary key column from the aggregate (GetLoggerByUserId) click on the arrow, and select 'max'. It will get the highest id.
Example with a test table:

Alternatively, without resorting to Max, you can just sort by Id Descending, and limit the Aggregate to a single row. That will also get you the highest Id, with the benefit of having all the data (which seems what the OP wants).

As for the SQL query, it's this icon on the tool bar:

Kilian Hekhuis wrote:

Alternatively, without resorting to Max, you can just sort by Id Descending, and limit the Aggregate to a single row. That will also get you the highest Id, with the benefit of having all the data (which seems what the OP wants).

As for the SQL query, it's this icon on the tool bar:


Yeah, that's exactly the button I'm missing. I'm using OutSystems 10. 

Csanád,

Are you developing a mobile app? Because there's no SQL for mobile:

Niels Favreau wrote:

Before the updating node, call an aggregate to retrieve the logger table.(Call it GetLoggerByUserId)
Create a filter in the aggregate where Logger.TheuserId = GetUser.Id ( from the first node the first node).

Since you want the latest logger id, select the primary key column from the aggregate (GetLoggerByUserId) click on the arrow, and select 'max'. It will get the highest id.
Example with a test table:


Problem is I can't get to this view... I'm using a Mac, so I can only use the online editor (devenv), and if I drag and drop any entity to the aggregation table, nothing happens at all. Tried on Safari and Chrome too, Chrome in addition also shows a crossed black circle, showing me I can't drop the entity there. 

Kilian Hekhuis wrote:

Csanád,

Are you developing a mobile app? Because there's no SQL for mobile:

Yes! That might be the issue. I still should be able to do aggregations right?

Solution

Hi Csanád,

Yes, with aggregates this is easy, see my post above (sorting on Id descending, limit to 1 row*). Note that on mobile, there's two ways to use aggregates:

  1. On screen level. Right click the screen in the tree, select "Fetch Data from Local Storage" or "Fetch Data from Database":
  2. Add an Aggregate to the flow of an event handler (e.g. On Ready).

Note that method 1 will run asynchronous, while method 2 will run synchronous. So depending on how fast it is, and what your use case is exactly, make the right choice :).

*Not needed per se, but it'll be much faster.

Solution