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 topics

I have the same question (0)

All responses (7)

Answers (1)

Pstork1 68,707 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
wherdzik on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

Pstork1 68,707 Most Valuable Professional on at

Like (1)

Report

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.

Was this reply helpful? Yes No
wherdzik on at

Like (0)

Report

Thank you for the suggestion.

Was this reply helpful? Yes No
Diegoaapm94 on at

Like (0)

Report

Hello, can you share your flow please

Thank you

Was this reply helpful? Yes No
wherdzik on at

Like (1)

Report

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:

Was this reply helpful? Yes No
Diegoaapm94 on at

Like (1)

Report

thank you!

Was this reply helpful? Yes No