Answered

PVA Filter Excel Table - Search Employee Name based off Contains and Return Certain Columns

(0) Share

Report

Posted on by wherdzik

I am trying to create an action where I can ask the PVA bot to look inside of an excel sheet, filter the employee name and return the pay increase that was given to the employee. I would pick certain columns and then pass it to a markdown table so that it can be visually appealing. I am a part time user of PVA and I cannot figure out how to filter the excel table for employee name. Can someone please guide me how to do this effectively. Here is what I have now that is not working. It seems to be filtering the dat or doing something but not returning anything.

My starting flow

Categories:

General

All responses (7)

Answers (1)

Diegoaapm94 on at

Like (1)

Report

Re: PVA Filter Excel Table - Search Employee Name based off Contains and Return Certain Columns

thank you!
wherdzik on at

Like (1)

Report

Re: PVA Filter Excel Table - Search Employee Name based off Contains and Return Certain Columns

I changed this flow and moved it to a sharepiont list:
Under the Get Items I did a substgringof
Then I returned to a Mark Down table:
Diegoaapm94 on at

Like (0)

Report

Re: PVA Filter Excel Table - Search Employee Name based off Contains and Return Certain Columns

Hello, can you share your flow please

Thank you
wherdzik on at

Like (0)

Report

Re: PVA Filter Excel Table - Search Employee Name based off Contains and Return Certain Columns

Thank you for the suggestion.
Verified answer

Pstork1 66,477 Most Valuable Professional on at

Like (1)

Report

Re: PVA Filter Excel Table - Search Employee Name based off Contains and Return Certain Columns

1) Try putting an Upper() function around each field in the filter statement. That will convert everything to uppercase to do the compare.

2) The filter array will output all the columns. But you can use a Data Select just before the filter array to trim out columns you don't need. You can also use that to cast the employee name column to all caps so you don't have to do it in the filter. Then run the filter on the output of the data select. After that you may have to do a Parse JSON to get access to the individual fields as dynamic content again.
wherdzik on at

Like (0)

Report

Re: PVA Filter Excel Table - Search Employee Name based off Contains and Return Certain Columns

Thanks Psork1, I see what is going on. You are correct some of the employee names are in ALL CAPs. Is there a way to have to filter for just the text regardless of capitalizations? This table is all over the place and if I could filter without these criteria would be awesome. Also, how do I capture only certain columns from the Filter Array. I see the "Body" but how do I pick the columns from there as this is a long running table.
Pstork1 66,477 Most Valuable Professional on at

Like (0)

Report

Re: PVA Filter Excel Table - Search Employee Name based off Contains and Return Certain Columns

The way you are doing it looks right depending on things like capitalization and what is being passed as the Input. I would suggest adding a Compose statement just prior to the filter array and add the Input to that. Then you can compare that to the different rows output by the List Rows action to see why the filter isn't working.