Bootstrap excel file and use specific cells in calculation based on user input?

I'm trying to build an app that calculates a number based on the inflation index of a specific month and specific year.


My table looks like this:

Basically the user will enter a date, for example 24/12/2001 which is saved to a "DateOfPurchase" variable, and a number, for example 130, saved to a "NominalVal" variable. 


Then I will have an assign button in the calculate button action, which should look a bit something like this


RealResult = NominalVal*GetIndexInflationCell([YearEnteredByUser, MonthEnteredByUser]). 


I am just not familiar at all with the syntax of OutSystems, and when I try to use the sheet in my calculations in the assign expression, I don't see it anywhere. It doesn't show under "Entities" in the expression editor, even though I can see it under "Entities" in the "Data" tab of Outsystems. Could anyone explain in detail how this could be achieved?

Maria da Graça Peixoto wrote:

Hi! 

this course is very helpfull: 

https://www.outsystems.com/learn/courses/114/developing-web-apps-outsystems-11/?LearningPathId=2


Thanks for the link Maria, however due to a time constraint I can't go through the entire course to find the specific thing I am trying to achieve. It'd be something I'll do at a later point for sure, but right now I'm just looking to see if someone with more experience than me can explain how they would do the above step by step.

To retrieve data from an entity to use in an expression, you must use the aggregate element. You'll probably need to filter this to the year and/or month needed, I don't know what your entity looks like.

Zachary wrote:

To retrieve data from an entity to use in an expression, you must use the aggregate element. You'll probably need to filter this to the year and/or month needed, I don't know what your entity looks like.

Hi Zachary, the entity looks exactly like the picture I embedded above. It's a static entity with attributes as you see in the column names. The "Year" attribute is an integer and all other attributes are decimals, except "Id" which is always long integer. Also here's another view of the entity from inside outsystems


You have three options here then:

  1. Use an aggregate and filter by year to get the correct row. Then use a switch element to get the value for the right month.
  2. Use an advanced SQL query to feed the row selected by the user in as the column to get.
  3. Restructure the entity to just have Year, Month, and Value attributes (and id of course). Then you can use an aggregate and filter by the year and month supplied by the user.

Hello Akis,

There is no way to achieve what you want the way you are doing.

1. You need to fetch the desired information from your entity through an aggregate, using filters.
2. The result of the aggregate will be a list, with a single element (if the filters are done well)
3. You can then use the columns of this item to do it.

So, you need to filter the aggregate by Year.
After that, in your logic, you need to identify which month the user choose, and use the correct column from the List.Current.EntityName to do your calculation and assign the value to a variable, for example, to show in the screen through an expression.

You probably will need a switch or a series of IFs to choose the correct data.

Another approach to avoid the switch/IFs in logic would be to use a calculated column in the aggregate to choose the right column based on the month, with a series of nested IF functions.

Probably what you need would be better served in a different way, but as you are showing how you have things, this is probably what you need to do.

Cheers.

Zachary wrote:

You have three options here then:

  1. Use an aggregate and filter by year to get the correct row. Then use a switch element to get the value for the right month.
  2. Use an advanced SQL query to feed the row selected by the user in as the column to get.
  3. Restructure the entity to just have Year, Month, and Value attributes (and id of course). Then you can use an aggregate and filter by the year and month supplied by the user.

Thanks for the help. I think option 3 is what sounds like the easiest and most simple method for me right now. But could you explain exactly how to "use an aggregate and filter by year and month supplied by user"? 

Eduardo Jauch wrote:

Hello Akis,

There is no way to achieve what you want the way you are doing.

1. You need to fetch the desired information from your entity through an aggregate, using filters.
2. The result of the aggregate will be a list, with a single element (if the filters are done well)
3. You can then use the columns of this item to do it.

So, you need to filter the aggregate by Year.
After that, in your logic, you need to identify which month the user choose, and use the correct column from the List.Current.EntityName to do your calculation and assign the value to a variable, for example, to show in the screen through an expression.

You probably will need a switch or a series of IFs to choose the correct data.

Another approach to avoid the switch/IFs in logic would be to use a calculated column in the aggregate to choose the right column based on the month, with a series of nested IF functions.

Probably what you need would be better served in a different way, but as you are showing how you have things, this is probably what you need to do.

Cheers.


Hi Eduardo and thanks for the tips. I decided that I will first transform the entity to just have Year, Month, and Value attributes as Zachary suggested, to minimize the work needed to be done in Outsystems as I'm still a newbie. However, I don't know how to "fetch info from entity through an aggregate using filters". Could you explain how I can do that?

Solution
  1. Drag an aggregate into the action flow of your button
  2. Double click it to open it
  3. Click to add a source, select the entity you are using
  4. Switch to the filters tab of the aggregate
  5. Click add filter
  6. Type something along the lines of "inflationEntity.year=yearVariable and inflationentity.month=monthVariable (replace with the actual names of your variables etc. as appropriate)
  7. In your expression, access the result with something along the lines of "aggregate.list.current.inflationEntity.inflationValue"
Solution

Zachary wrote:

  1. Drag an aggregate into the action flow of your button
  2. Double click it to open it
  3. Click to add a source, select the entity you are using
  4. Switch to the filters tab of the aggregate
  5. Click add filter
  6. Type something along the lines of "inflationEntity.year=yearVariable and inflationentity.month=monthVariable (replace with the actual names of your variables etc. as appropriate)
  7. In your expression, access the result with something along the lines of "aggregate.list.current.inflationEntity.inflationValue"

Thanks for that Zachary, looks like exactly what I need. The only problem I have is I don't know where to find the aggregate to drag it into the action flow of the button :P Tried using the search bar and omnisearch too but no luck.

Ah, you're on reactive. In that case, right click the screen's entry in the interface section on the right and click Fetch Data from Database. In your action you can then drag in the Refresh Data element and point that at your aggregate.

Zachary wrote:

Ah, you're on reactive. In that case, right click the screen's entry in the interface section on the right and click Fetch Data from Database. In your action you can then drag in the Refresh Data element and point that at your aggregate.

Thanks for making all your instructions clear and easy to understand, I found it. Will report back tomorrow when I try this.


Zachary wrote:

Ah, you're on reactive. In that case, right click the screen's entry in the interface section on the right and click Fetch Data from Database. In your action you can then drag in the Refresh Data element and point that at your aggregate.

Works like a charm Zachary. Thank you very much for your time.