SharePoint Online, Viva , Teams, Power Platform, Azure, Identity

MSFlow | Best Practice | Lookup Threshold Issue

Here, we are going to discuss a very common problem statement faced during the runtime of Microsoft Flow:

The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator.

This problem statement occurs whenever Microsoft Flow executes over SharePoint Online list or library where the Lookup column exceeds the limit of 12 columns. The administrator cannot change the threshold limit at SharePoint Online, either at the Tenant or Site Collection level.

Why does Microsoft set the magic number 12 as the threshold limit?

Reason

Each lookup column creates a join with other tables. So, Microsoft decided to set the limit at 12 to avoid performance degradation.

Let's get started with detailed resolution.

Custom lookup column can be created using the below types.

Standard Lookup column
People Pickers
Managed Metadata
Workflow
Share With

OOTB Lookup columns

Created by (both for List & Library)
Modified by (both for List & Library)
Name (linked to Document)
Link (Edit to edit item)
Name (linked to Document with edit menu)
Type (icon linked to document)

Lookup column is quite useful but we should be cautious about the threshold limit while designing the schema.

I created a list with 13 lookup columns. See the below list for your reference.

Problem statement with Get Items

When we run flow to get an item from a List or Library having more than 12 lookup columns, it throws the following error.

Resolution with Get Items

Create a list or library view with 12 or fewer than 12 lookup columns and set the view at the Get Items action. During a custom View creation, we can ignore the OOTB column or the ones that are not required for this operation.

After setting the list View with 12 lookup columns, once I execute the flow, it succeeds.

Problem statement with Update Item

A similar issue occurs when we want to update the items using MS Flow.

Resolution with Update Item

I created a View with 12 lookup columns and set “Limit Column by View” with a newly created View.

Once I executed the flow with the View which is having 12 lookup columns, it succeeded and updated the item.

I hope you have learned something new in this article. Stay tuned for related articles to get more insights and learning.

Speaking at C# Corner Annual Conference 2019

Automate the Business Process using AI Services and O365 Suites.

I am very excited to going as speaker at C # corner Annual conference 2019 this Saturday at Delhi

Address:-
The Leela Ambience Convention , 1,CBD, Near Yamuna Sports Complex,
Maharaja Surajmal Marg, Delhi, 110032

Definitely you folks will learn a good use case for this.

More

Annual conference 2019 : https://conference.c-sharpcorner.com/

Happy Coding.

Strategy Design Pattern Using .Net (C#)

In this article, we will understand what Strategy Pattern is and when we actually need to use it, along with a practical example and real-life use case.

This pattern falls under the category of behavioral pattern and as the name suggests, it allows clients to choose an algorithm from a set of algorithms at run time.

Strategy pattern defines a family of algorithms, encapsulates each one of them and makes them interchangeable at run time within that family.

Now let's understand what each one of them actually means.

Family of Algorithms:
This means this pattern provides a set of algorithms using one of which at run time you can achieve the desired output.

Encapsulate each one of the:
This pattern allows you to place your algorithms in different classes (encapsulate them).

Makes the algorithm interchangeable:
The beauty with strategy pattern is that we can select at run time which algorithm we want to apply to our object and can also replace them with one another.

These are the three main points of Strategy pattern. I hope we are clear with the above- discussed points. So, let's understand one real -world scenario where it can be really helpful to use.

Example

Suppose you want to order food online and apply one of the available coup. In this case you can implement the Strategy Pattern.

As per our example, IApplyCoupon is the interface which looks something like,

1. public interface IApplyCoupon
2. {
3. string applycoupon(string couponType);
4. }

We have 2 Concrete coupon classes which implement IApplyCoupon interface and these classes can have their own logic to calculate the time.

1. public class FamilyHours :IApplyCoupon
2. {
3. public string applycoupon(string couponType)
4. {
5. return "You applied family hours coupon";
6. }
7. }

1. public class FestivalOffer :IApplyCoupon
2. {
3. public string applycoupon(string couponType)
4. {
5. return "You applied festival offer coupon";
6. }
7. }

Lastly, we have one Context class called TravelStrategy using which a client can select any strategy at run-time.

1. public class Coupon
2. {
3. private IApplyCoupon _applycoupon;
4. public Coupon(IApplyCoupon applycoupon)
5. {
6. _applycoupon = applycoupon;
7. }
8. public void applycoupon(string couponType)
9. {
10. var result = _applycoupon.applycoupon(couponType);
11. Console.WriteLine(result);
12. }
13.
14. }

Our main program looks like:

1. class Program
2. {
3. static void Main(string[] args)
4. {
5. Console.WriteLine("Hello!, Please select the coupon for Dinining \n1.FamilyHours \n2.FestivalHours");
6. var userStrategy = Console.ReadLine().ToLower();
7. Console.WriteLine("\nUser has selected " + userStrategy + " as dining coupon\n");
8. switch (userStrategy)
9. {
10. case "FamilyHours":
11. new Coupon(new FamilyHours()).applycoupon("FamilyHours");
12. break;
13. case "FestivalHours":
14. new Coupon(new FestivalOffer()).applycoupon("FestivalHours");
15. break;
16. default:
17. Console.WriteLine("You didn't select any coupon");
18. break;
19. }
20. Console.ReadLine();
21. }
22. }