i have an interface that displays a grid of data returned from a DB. One of the columns has a numerical ID column. Rather than show the id, i would like to show it's associated value which is in a different lookup table.
The interface has two main ruleslocal!xxx: gets the table datalocal!yyy: gets the lookup data.The lookup data is returned as :
[lookupId:123,value:Toys]; [lookupId:456,value:Games]In my grid field, i set the column as a text field with the following as value:
index(local!xxx,wherecontains(fv!item.id,local!yyy),{})However im' getting the message can only act on data of the same type (Text, Any Type)I've tried to enclose in touniformstring, but it has no effect. ps. whats with the tags ? i had to write this out 3 times before i reliased not to click on the tags function)
Discussion posts and replies are publicly visible
Hi Paul - Can you please try replacing index(local!xxx,wherecontains(fv!item.id,local!yyy),{}) with index(local!xxx,wherecontains(fv!item.id,apply(touniformstring(_),{local!yyy})),{}) and check if it works.
Hi pauls0010,
Try Using the displayvalue function - https://docs.appian.com/suite/help/19.4/fnc_conversion_displayvalue.html
displayvalue(fv!item.id,tointeger(index(local!yyy, "lookupId", null)),index(local!yyy, "lookupId", null), null)
still the same i'm afraid.
I'm not sure what your table data looks like or what the forEach items are, but I'm guessing you'd need something like:
index(local!xxx,wherecontains(fv!item.id,touniformstring(local!yyy.lookupId)),{})
1) index into local!yyy so the wherecontains is looking at the right fields
2) touniformstring (maybe) on the local!yyy[field] to ensure the Text->Text comparison
vimalkumars suggested you use the displayvalue() function which in effect is conducting a local lookup of the lookupId value and returns the correlating value (e.g. to substitute 123 for 'Toys'). This should work, but does result in your interface performing TWO round-trips to the database (one to get the data, a second to get the lookup data).
An alternate option would be to 'join' this data into a VIEW in the database so that when you retrieve the data the lookup has in effect already been performed. You'd then only be performing ONE round-trip to the database.
(it may be that you only gain a marginal performance benefit but you should be aware of this as an option)