Attempting Action Similar to Index/Match or vllookup

Good Day Everyone,


I am attempting to pull some data from a static entity (item Category) when a particular item(Drop down) is selected; similar to an index/match or vllookup on Excel.

This is taking place in and Editable Table Form.

I successfully managed to pull data from the Item Category static entity via a join however the data doesnt change dynamically based on the the Item chosen. It only pulls the first category in the list.

Formula here:

SyntaxEditor Code Snippet

If(OrderItemsTable.List.Current.ItemName<>"",GetItems2.List.Current.ItemCategory.Label,"")

I also tried to get the data by setting an On change event on the item input however i am lost as to how to reference the Item Category cell.


Any help given would be appreciated.


Pics are attached for your reference. Highlighted areas are where i want to pull values.

Hi Jurian,

Thanks for a well explained question and the picture.

It will help the community to help you if you could tell us which type of web application you are working with.  I am assuming you are working with a Traditional Web application and not a Reactive Web Application.

Now, for a query to retrieve a different value based on the selection of a drop down changes, you are right to use the on change event.  The change event must do 2 things, re-run the query for the static entity (Refresh Data) and update the part of the page display the data from the query (Ajax Refresh).

In the on change event, use a Refresh Data widget and select your static entity query to executed it again.  Then use a Ajax Refresh widget to update a part of the page.  To see the part of the page in the Ajax Refresh widget's Widget list, it must have a name.  Either give the component or a parent container a name, and it will appear in the list to select.

So then when the drop down changes, the data will be refreshed and the user interface updated to show the new value.

As you are using a Form, you will might need to make sure the static entity query uses the Form's Record data in its filter instead of the source data.  This will ensure the filter value is the one modified by the dropdown.

It should look something like this

The online courses have a great module on this

https://www.outsystems.com/learn/lesson/1790/ajax-interactions/?LearningPathId=2

Find the "Developing Web Apps (OutSystems 11)" course, then the "AJAX and Reusable UI" module, then the "AJAX Interations" tutorial.

I hope this helps!

Kind regards,

Stuart


Hi Jurian Dejean,

What I understand from your questions you wants to refresh value of other column in same grid when you are changing the first column of same grid.
If its correct then just you need to refresh the aggregate for the value in second column and refresh widgets in second column which I guess is expression

Stuart, Lovely

Thank you very much for the response.


The application is indeed Traditional Web. The above suggestions were implemented on a test Show Record Table and they worked. However it does not work on the Editable Table. I am getting the attached error when running "Detected a recursive iteration over the same list" .


I believe it may be because the Item ID widget which is he local variable by which the data is filtered  is apart of the form which is being refreshed. But its just my theory.

Would appreciate any help that can be given.


Solution

Hi Jurian,

I made a little oml based on the answers of Stuart and Lovely, but I'm using get actions to retrieve the desired static entity label, instead of an aggregate.  

I find this fairly simple, but I'm not sure what it means for performance compared to other solutions.


See attached oml, on the demo screen, whenever changing the item of a given orderitem, it retrieves the itemgroup label that selected item is in, with an onchange event.


I think this comes close to what you're looking for, but again, might not be the most efficient pattern, since there is a server call every time another item is selected.


Good luck with it,

Dorine

Solution

hi Jurian Dejean,

I agree with dorine. You need to fetch data from static entity. but in your case you are fetching data from the same record list that used in table records

1. just fetch data from static entity by item id. assign label and value into current row of column
2. refresh only column widget not whole table. No need to do so

Lovely Priyadarshini wrote:

hi Jurian Dejean,

I agree with dorine. You need to fetch data from static entity. but in your case you are fetching data from the same record list that used in table records

1. just fetch data from static entity by item id. assign label and value into current row of column
2. refresh only column widget not whole table. No need to do so

Hi Lovely,

one small remark here, the Assign should be on the aggregate that is the source of the table, rather than on the table itself, since the Refresh OrderItem action will copy aggregate data over the table data.


I wasn't really happy with doing Individual Get actions for each label, so inspired by your answer I did something similar, retrieving all info in the original preparation Aggregate, and another Aggregate in the OnChange where you have your Get.  I'm just a beginner with OS, but as I understand it, you leave more options open for the platform to optimize the code when you use an aggregate rather than a get.

See changed oml attached here.


Dorine


Hi Dorine,

yes its true dorine to assign in aggregate when we are refreshing whole table.

I suggest you to refresh only widget inside  table cell so assign table column intead of aggregate column.

The reason is if we refresh only widget that time we are not refreshing source of aggregate so widget value will be updated from table.

Also I agree with your point using single get I also not prefer most of time as when id is null then it will throw error so best way to do this is
1. Add aggregate at preperation with filter

SyntaxEditor Code Snippet

Item.Id = OrderItemTable.List.Current.OrderItem.ItemId


2. and on onchange action just refresh the aggregate and assign in tablerecords column and also add maxrecord of this aggregate to 1.

Hope this will make sense to you. 

Lovely Priyadarshini wrote:

Hi Dorine,

yes its true dorine to assign in aggregate when we are refreshing whole table.

I suggest you to refresh only widget inside  table cell so assign table column intead of aggregate column.

The reason is if we refresh only widget that time we are not refreshing source of aggregate so widget value will be updated from table.

Also I agree with your point using single get I also not prefer most of time as when id is null then it will throw error so best way to do this is
1. Add aggregate at preperation with filter

SyntaxEditor Code Snippet

Item.Id = OrderItemTable.List.Current.OrderItem.ItemId


2. and on onchange action just refresh the aggregate and assign in tablerecords column and also add maxrecord of this aggregate to 1.

Hope this will make sense to you. 


Nope it doesn't make sense, and it doesn't match what I see when testing.  I added the option to the demo screen to either update the value of the Table or of the underlying Aggregate, Aggregate always works, Table mostly not, except sometimes the first time but not always correct value.(don't understand the reason for that)


So can you make an oml that demonstrates, since I may be overlooking something ?

see attached oml


Hi Dorine,

I have attached an OML to describe what I am trying to tell and I always use this method only and it works really well. And In oml you have attached we really don't need to assign anything because we don't need to store groupid just need to show.  but Let me know if you wants to tell something else.


Hi,

But for anyreason we wants to save groupid and label then to describe that I have attached second OML

Lovely Priyadarshini wrote:

Hi Dorine,

I have attached an OML to describe what I am trying to tell and I always use this method only and it works really well. And In oml you have attached we really don't need to assign anything because we don't need to store groupid just need to show.  but Let me know if you wants to tell something else.


Oke,

i see what you are doing.

So at no point do you assign values to the table fields directly, as you suggested before, and which I think wouldn't work. 

You have bound all group labels in all rows to one and the same aggregate retrieving a single label for a given item, instead of binding it to one of the attributes coming out of the source aggregate for the table.

When you run the aggregate for retrieving a label for a first time in the Preparation, it results in no records found, because you filter on 

Item.Id = OrderItemTable.List.Current.OrderItem.ItemId

and in the preparation, OrderItemTable list is nog populated yet.  

So in the OnChange, rerunning the aggregate only works because you are only refreshing one single group label in the current row.

However, your oml as is, doesn't work to show the labels when you open this screen for an already existing order with existing order items.  As is, since aggregate2 doesn't result in any rows in the preparation, they will all be empty, if you would change the aggregate to get a label back in the preparation, all your rows would have same value for the label after running the preparation.

So between the 2 of us, we have offered Jurian enough ideas to work out a solution, I think.

Dorine

Hi dorine,

You are correct my way will not work for preexisting data but if you go through the second oml and manipulating little bit in join it works for existing data also.

But yes our discussion will help jurian a lot in understanding where he was mistaking.

Lovely

 Hey Guys,

Your inputs have been so fruitful and eyeopening as to various methods available. I took a few hours to analyze and read up on certain aspects which were new to me.

I have been exposed to outsystems platform for only two weeks with no prior developing experience. I am also enrolled in the web developer course so i am applying the principles in the movie application to another application which i am building alongside.

I could not open Lovelys Test file because of the differences in theme. I installed the missing theme (Lisbon) however it still gives me an error.

Dorine file concept worked well. It was exactly what i was trying to accomplish.

I only have 2 more issues arising from same:

1) When i go to the next row, my quantity and Warehouse inputs are cleared out

2) I tried a Quantity * Value calculation but it refuses to work. I am using an onchange event in the Quantity input and Ajax Refresh on the Total Value Widget

I am not sure if i should open another post but heres my logic

1):


2) Total Value Expression formula: 

SyntaxEditor Code Snippet

TextToInteger(If(OrderItemsTable.List.Current.Id<> NullIdentifier(),GetItem(OrderItemsTable.List.Current.Id).Item.ItemValue,"")) * OrderItems.Current.Quantity

Also i should mention that the data source for my table is a local variable whose Data Type is Order Items List. This is so because when the order is complete with the list of items and the user clicks Save, Both the Order and Order Items Entities are Updated.


Hey Guys

Afer 3 hours of troubleshooting I managed to fix Issue no 1 - Had my inputs assigned to local variables and not the current list. Issue no 2 is still outstanding though- (calculation of total value )