Showing posts with label Switch. Show all posts
Showing posts with label Switch. Show all posts

Wednesday, September 21, 2022

Power Automate: Export To Excel For Dynamic View/Filter Selection In PowerApps

Hello Friends,

Welcome back with another post on Power Automate. We had already discussed about "Export To Excel" in our earlier posts. However, we are again going to discuss the same. This time, we are getting the data based upon the selection of View as well as filters applied in PowerApps. This post is also related to my previous two posts-
Let's start-
  1. Open the Power Automate maker portal and create a Instant cloud flow with trigger type as "PowerApps" and flow name as- "POC-DynamicExportToExcel".
  2. Now, we have 3 parameters to apply filter upon data-
    1. View Name
    2. Experience
    3. Free Text Search
  3. We will initialize 3 variables (using Initialize variable and value as Ask in PowerApps) that will communicate with PowerApps to provide these values to the flow-
  4. Variable1: ViewName
  5. Variable2: Experience
  6. Variable3: FreeTextSearch
  7. Now, we will define another variable named "strFilterCriteriaByViewName". This will be the first filter criteria.
  8. Keep saving your flow frequently.
  9. Here, we will use the same filter conditions as we applied in PowerApps. For that, we will apply a Switch-Case.
  10. Now, refer back to the previous post, where, we have defined the Views. Check the "Items" property of DataTable / Gallery.
  11. Please find below the function from PowerApps for ready reference.
    1. Switch(
          Dropdown_SelectView.SelectedText.ViewName,
          "All Employees",
          Filter(
              EmployeeInfo,
              Experience.Value in If(
                  Radio_SelectExperience.SelectedText.Experience = "All",
                  Experience.Value,
                  Radio_SelectExperience.SelectedText.Experience
              ) && (Trim(TextInput_Search.Text) in 'First Name' || Trim(TextInput_Search.Text) in 'Last Name' || 
      Trim(TextInput_Search.Text) in Designation.Value || Trim(TextInput_Search.Text) in Experience.Value || 
      Upper(Title) = Upper(Trim(TextInput_Search.Text))) || Text(
                  'Date Of Joining',
                  "m/d/yyyy",
                  "en-US"
              ) = Text(
                  Trim(TextInput_Search.Text),
                  "m/d/yyyy",
                  "en-US"
              ) || Text(Trim(Salary)) = Trim(TextInput_Search.Text)
          ),
          "Software Engineer",
          Filter(
              EmployeeInfo,
              "Software Engineer" in Designation.Value,
              Experience.Value in If(
                  Radio_SelectExperience.SelectedText.Experience = "All",
                  Experience.Value,
                  Radio_SelectExperience.SelectedText.Experience
              ) && (Trim(TextInput_Search.Text) in 'First Name' || Trim(TextInput_Search.Text) in 'Last Name' || 
      Trim(TextInput_Search.Text) in Designation.Value || Trim(TextInput_Search.Text) in Experience.Value || 
      Upper(Title) = Upper(Trim(TextInput_Search.Text))) || Text(
                  'Date Of Joining',
                  "m/d/yyyy",
                  "en-US"
              ) = Text(
                  Trim(TextInput_Search.Text),
                  "m/d/yyyy",
                  "en-US"
              ) || Text(Trim(Salary)) = Trim(TextInput_Search.Text)
          ),
          "Module Lead",
          Filter(
              EmployeeInfo,
              "Module Lead" in Designation.Value,
              Experience.Value in If(
                  Radio_SelectExperience.SelectedText.Experience = "All",
                  Experience.Value,
                  Radio_SelectExperience.SelectedText.Experience
              ) && (Trim(TextInput_Search.Text) in 'First Name' || Trim(TextInput_Search.Text) in 'Last Name' || 
      Trim(TextInput_Search.Text) in Designation.Value || Trim(TextInput_Search.Text) in Experience.Value || 
      Upper(Title) = Upper(Trim(TextInput_Search.Text))) || Text(
                  'Date Of Joining',
                  "m/d/yyyy",
                  "en-US"
              ) = Text(
                  Trim(TextInput_Search.Text),
                  "m/d/yyyy",
                  "en-US"
              ) || Text(Trim(Salary)) = Trim(TextInput_Search.Text)
          ),
          "Project Manager",
          Filter(
              EmployeeInfo,
              "Project Manager" in Designation.Value,
              Experience.Value in If(
                  Radio_SelectExperience.SelectedText.Experience = "All",
                  Experience.Value,
                  Radio_SelectExperience.SelectedText.Experience
              ) && (Trim(TextInput_Search.Text) in 'First Name' || Trim(TextInput_Search.Text) in 'Last Name' || 
      Trim(TextInput_Search.Text) in Designation.Value || Trim(TextInput_Search.Text) in Experience.Value || 
      Upper(Title) = Upper(Trim(TextInput_Search.Text))) || Text(
                  'Date Of Joining',
                  "m/d/yyyy",
                  "en-US"
              ) = Text(
                  Trim(TextInput_Search.Text),
                  "m/d/yyyy",
                  "en-US"
              ) || Text(Trim(Salary)) = Trim(TextInput_Search.Text)
          ),
          "Program Manager",
          Filter(
              EmployeeInfo,
              "Program Manager" in Designation.Value,
              Experience.Value in If(
                  Radio_SelectExperience.SelectedText.Experience = "All",
                  Experience.Value,
                  Radio_SelectExperience.SelectedText.Experience
              ) && (Trim(TextInput_Search.Text) in 'First Name' || Trim(TextInput_Search.Text) in 'Last Name' || 
      Trim(TextInput_Search.Text) in Designation.Value || Trim(TextInput_Search.Text) in Experience.Value || 
      Upper(Title) = Upper(Trim(TextInput_Search.Text))) || Text(
                  'Date Of Joining',
                  "m/d/yyyy",
                  "en-US"
              ) = Text(
                  Trim(TextInput_Search.Text),
                  "m/d/yyyy",
                  "en-US"
              ) || Text(Trim(Salary)) = Trim(TextInput_Search.Text)
          )
      )
  12. The same we are going to implement in flow. The primary filter is upon Designation-
    1. All Employees - No Filter Criteria
    2. Software Engineer - Filter Designation by "Software Engineer"
    3. Module Lead - Filter Designation by "Module Lead"
    4. Project Manager - Filter Designation by "Project Manager"
    5. Program Manager - Filter Designation by "Program Manager"
  13. We had completed first part.
  14. Now, we will fetch all such items from SharePoint list that matches this filter criteria. For that, we have to define three variables then use "Do Until" action to perform the activity.
  15. Now, we will use Do-Until action to fetch the items.
  16. Get Items Using ItemID variable & FilterCriteria
  17. Select the required fields
  18. Join the output with arrAllItems array
  19. Update the variable arrAllItems with the previous action result
  20. Update the intItemID with the last ItemID from the output of GetItems action. If GetItems action output is NULL, then assign it with 0.
  21. Now, check if the output of GetItems is empty or not. If empty, set the blnIsEmpty to true otherwise false.
  22. Now, we will set the "Configure Run After" condition for the action. For this, click on ellipses (3 dots) ahead to the Set blnIsEmpty action and choose "Configure run after".
  23. You will find that "is successful" is by default selected. Select "is failed" as well and click on Done.
  24.  
  25. This completes the second part.
  26. Now, the third part is to filter data by Experience choice. If Experience selected by user is not "All", then we have to apply additional filter of Experience upon the outcome of Do-Until action.
  27. Before that, we will execute this flow and get the schema so that we can parse the output to JSON as we cannot apply filter upon array.
  28. Then use Parse JSON action.
  29. Now add a "Condition" action and check if Experience is not selected as "All". If true, apply "Filter" action on output of ParseJSON action and set the output again to arrAllItems variable.
  30. This completes third part.
  31. Now, the fourth part is to apply filter using free text search.
  32. This is the main section. Here we will apply filter on each field separately and capture the outcome in an individual array.
  33. After then we will combine all these outcomes in single array. Thus, unique results will be obtained.
  34. As we can see that we had applied free text search upon all 7 fields. Thus, we have to create 7 array variables and then apply filter one by one.
  35. First define the arrays.
  36. Now, check if the FreeTextSearch variable is having any value or not. If Yes, then start filtering one by one.
  37. Apply Parse JSON upon arrAllItems so that filter can be applied.
  38. We will check if the FreeTextSearch value is contained (unless specified) in the item respective field.
  39. Filter for Title field.
  40. Remember, when you are applying a filter, you have to use the output (body) of Parse JSON action. When you are updating the respective array with the output of respective filter, then you have to use the output (body) of Filter action.
  41. Filter for First Name field.
  42. Remember, when you are applying a filter, you have to use the output (body) of Parse JSON action. When you are updating the respective array with the output of respective filter, then you have to use the output (body) of Filter action.
  43. Filter for Last Name field.
  44. Remember, when you are applying a filter, you have to use the output (body) of Parse JSON action. When you are updating the respective array with the output of respective filter, then you have to use the output (body) of Filter action.
  45. Filter for Date Of Joining field. Here, we will first format the date to "M/d/yyyy" format and then check if both are equal. Additionally, here we will use the "Configure run after" as well because in case the FreeTextSearch is not a date then the formatDateTime will throw an error and our further processing will get failed. So, we have to handle that case.
  46. Remember, when you are applying a filter, you have to use the output (body) of Parse JSON action. When you are updating the respective array with the output of respective filter, then you have to use the output (body) of Filter action.
  47. Filter for Salary field.
  48. Remember, when you are applying a filter, you have to use the output (body) of Parse JSON action. When you are updating the respective array with the output of respective filter, then you have to use the output (body) of Filter action.
  49. Filter for Designation field.
  50. Remember, when you are applying a filter, you have to use the output (body) of Parse JSON action. When you are updating the respective array with the output of respective filter, then you have to use the output (body) of Filter action.
  51. Filter for Experience field. However, we can avoid filter condition upon Experience as this is already present upon screen in the form of Radio Choice.
  52. Remember, when you are applying a filter, you have to use the output (body) of Parse JSON action. When you are updating the respective array with the output of respective filter, then you have to use the output (body) of Filter action.
  53. Now, we have individual filter results for each FreeText filter criteria. We have to combine these outcomes. Thus, we will now use Compose action to apply union function to combine these outcomes. The benefit of union is that it takes unique items. If any item is found duplicate, it keeps only one of those item instances. 
  54. Now, set the output of this Compose action to arrAllItems.
  55. This completes out fourth part.
  56. Now, the fifth & final part. Here, we will export this output to Excel. For this, you may refer my earlier blogs using OneDrive as well as SharePoint library.
    1. Using One Drive-
      1. Export To Excel Using Power Automate
    2. Using SharePoint Library-
      1. Power Automate: Export To Excel (SharePoint)
  57. This is how, we can Export data to excel.
With this, I am concluding this post.
Happy Coding !!!
Will see you again with some new topics.

Stay Safe !
Stay Healthy !

Wednesday, August 31, 2022

Power Automate: Use Of SELECT Function

Hello Friends,

Welcome back with another post on Power Automate. Today, we will discuss about another amazing function provided by Microsoft. This function is "SELECT". Let's start.

  1. The scenario is, you have to get the items from a SharePoint list based upon the IDs provided. These IDs are sent to the flow as comma separated. Usually, what we do is we convert these comma separate IDs into an array, create a filter query using "Apply to each" loop and then use Get Items action. Now, the same we will do using Select action.
  2. We had created a list named TestListSJ.
  3. This list has 25700 items starting from ID 401 upto 26100. We will use IDs from 401 upto 500 means 100 items.
  4. Let's create a flow in Power Automate.
  5. We are creating a "Manually trigger a flow" with name as "POC-SelectFunction".
  6. Click on Create.
  7. Declare a string variable "strItemIDs" to hold the comma separated Item IDs. Assign the comma separated IDs from 401 to 500.
  8. Now, we will initialize another variable "arrItemIDs" of type array which will hold these IDs in array form.
    1. split(variables('strItemIDs'),',')
      
  9. We will define another variable "intTotalItemIDsCount" of type Integer to capture the total count of Item IDs received.
    1. length(variables('arrItemIDs'))
      
  10. From here, the actual logic starts. Now, we will use "Select action and convert each item ID into individual filter query like from 401 to (ID eq 401).
  11. Click on the arrow marked icon. It will switch the Mapping window to basic mode.
  12. Now add the "Concat" function as below.
    1. concat('(ID eq ',item(),')')
      
  13. This concat function will convert each item id into individual filter query.
  14. Keep saving the flow frequently.
  15. Now we need to combine all these filter queries into one single string, separated with " or ". For this we will use "Compose" action and in compose action, we will use the "join" function. 
    1. join(body('Select_-_Individual_Filter_Query'),' or ')
      
  16. This is what we were looking for.
  17. Now, add "Get Items" action.
  18. Select the Site Address, List Name, click on "Show advanced options". In "Filter Query", select the output of Final Query Compose action.
  19. That's all we done.
  20. Now save the flow and Test it.
  21. If you see the execution time of each action. It all 0. It means they all individually too less than a second to execute.
  22. This is the power of "Select" action. It reduce the execution time. Now, to prove that, we will use tradition method in parallel as well. We will also capture Start/End time for each method and then we will compare the time consumption.
  23. For this, we will add a parallel branch just after the "Initialize variable - intTotalItemIDsCount" action. This variable will be named as "strCombinedFilterQuery".
  24. Add "Apply to each" action on the arrItemIDs. Inside the Apply to each action add another action called "Append to string variable". Here we will append the individual filter query to the variable "strCombinedFilterQuery" 
    1. concat('(ID eq ',item(),') or ')
      
  25. This step will give us a string like "(ID eq 401) or (ID eq 402) or ... (ID eq 500) or ". As we see, there is an extra " or " at the end, we need to scrap it out.
  26. We will add a Compose action and use "substring" function to scrap the last " or " piece.
    1. substring(variables('strCombinedFilterQuery'),0,add(lastIndexOf(variables('strCombinedFilterQuery'),')'),1))
      
  27. Now, use the Get Items action to get the filtered records.
  28. Save the flow and test it again. 
  29. If you see, the traditional approach took 26 seconds to prepare the filter query itself.
  30. Now, if we see the Run history.
  31. Here, we will find that the "Select" approach took only 31 milliseconds to complete the process, while the tradition approach took 27 seconds. It means the Select approach is 81 times faster than the traditional approach.
  32. This select approach is surprisingly useful, when you have to process data. The traditional approach will again add a "Apply to each" loop, while the same work will be done by Select action in fraction of time.
  33. For example, here after getting the data, if I have to add a column where, I need to fill in-
    1. "Apple" if GUID starts with "a"
    2. "Banana" if GUID starts with "b"
    3. "Citrus" if GUID starts with "c"
    4. "Others" for all other GUIDs
  34. Then I need to create HTML of this output. So, what I will do in traditional approach is, I will initialize a string variable to hold the HTML data.
  35. Then, I will use "Apply to each". In "Apply to each" either I will use the Condition (If-Else) or Switch-Case action. In that action, I will check the first character of GUID and based upon that character, I will append HTML code in the declared string.
  36. At the end, I will add another Append to String variable to complete the HTML.
  37. So, the final sequence in Traditional way is
  38. For new approach, I will use the Select action and add column "Flavour". In mapping, I will write the logic.
    1. if(
      	equals(toUpper(first(item()?['GUID0'])),'A'),
      	'Apple',
      	if(
      		equals(toUpper(first(item()?['GUID0'])),'B'),
      		'Banana',
      		if(
      			equals(toUpper(first(item()?['GUID0'])),'C'),
      			'Citrus',
      			'Others'
      		)
      	)
      )
      
  39. Lastly, I will add the Create HTML Table action and provide the output of previously added Select action as the input here.
  40. Now, Save the flow and execute it.
  41. Again, if you see, the new approach completed it process in less than a minute. While the traditional approach took around a minute to get the same completed.
  42. If we verify the same, this comes out to be "0" seconds (means less than a second) for New approach and 55 seconds for Traditional approach.
  43. Thus, you can see, how powerful is the "Select" action. So, friends, wherever possible, please use Select action and try to avoid "Apply to each" or "Switch" actions.
With this, I am concluding this post.
Happy Coding !!!
Will see you again with some new topics.

Stay Safe !
Stay Healthy !