Market Value on Fund

Bob Rickards

Folks…need some help. I have designed an adaptive card that shows up on the fund page. I am able to pull basic fund data but am looking to pull market value from an outside source (excel, sharepoint) by matching on the fund id number. The problem I am having finding the corresponding row in the external sheet. Any suggestions on how to pull this data? Thx

Alex Wong

@Bob Rickards
if the external source is an excel file that you have access to modify, make the range in the excel as table, then you can get row in a table by a “key”, which is your fund id.

if the external source is sharepoint, you can use ODATA Filter.

Bob Rickards

@Alex Wong Thanks for the help…I have connected to the SharePoint list…can pull back the column with the corresponding data but am having a formatting issue. Data shows up like this:

I have tried to format by saving as a string then format as a number (Currency) but then I get this issue.

I have tried several different ways to work around this with no avail. Is it something on the sheet side? I know its going to be some simple solution. Any help is appreciated.

Alex Wong

@Bob Rickards
show a screenshot of the output from the action which you are getting the “number” that you are trying to do formating, so I can see what the data type is you are working with before suggesting what expression to use.

I'm assuming your data type is probably string and the dollar sign is part of the string, if that's the case, you may want to update your SP list data type if you want better handling.. or leave it but will need to do a “replace” first. But let's start with a screenshot

Bob Rickards

@Alex Wong Thx…here is the top part of the flow.

I have tried saving as a variable then saving that variable as a integer to no avail. I even tried adding the “=” before the number on my excel sheet. I have tried formatNumber(), int(). Its a string I just can get it to go to a number. The first compose shows this…

Second compose fails.

Alex Wong

@Bob Rickards
need the screenshot for the Get a row action's output where the 30533.42 came from

Bob Rickards

@Alex Wong

Alex Wong

@Bob Rickards
need the output body. your screenshot cuts off with only 4 lines shown..

this is where the new “view” doesn't do well.. can't display the output of the action well..

once back to the “old view”, click on “Show raw outputs” and screenshot where the “body” begins

Bob Rickards

@Alex Wong Sorry…here you go…

Alex Wong

@Bob Rickards
given that MarketValue is a string, the expression is:

if(empty(outputs('Get_a_row')?['body/MarketValue']), '', formatNumber(float(outputs('Get_a_row')?['body/MarketValue']), 'c'))

this takes care of IF the MarketValue is blank (but not take care of if MarketValue is not a number.

this use float() b/c your MarketValue can be decimal, as int() will not do decimal.

Alex Wong

oh..by the way.. no need to put the expression in compose, just add it directly where you want to show the currency. (i.e. Email body)

Bob Rickards

@Alex Wong That worked! Thanks…so If I want to grab another column from the same sheet, do I follow the same process or can I just grab that date value?

Alex Wong

@Bob Rickards
the expression is specifically to format a decimal number as currency (locale specific). So if you want to format a Date, you will not use the same expression. If you want to format another decimal number as currency, then use the same expression, and replace the “outputs('Get_a_row')?['body/MarketValue']” with the other column's dynamic content.