cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Dudditz
Level 8

Default value based upon first letter within range

Helllo, we have built a Powerapp solution that consumes data from Dynamics such as Customer ID & Customer name and creates a folder for each customer as needed to store customer related data within Sharepoint, etc.

 

Due to the 5000 limit threshold within Sharepoint and the 20,000+ customers we are working with I don't have the option of creating a customer folder for all customers over time in the root of a document library.  I am testing the idea of having a series of parent folders within the Doc library suchs as:

A-E

F-J

etc.,  So I am testing out how I could apply some logic within the powerapps to write to a field a default value such as A-E or F-J based upon the first character within the Customer.  Basicallly, I am trying to acomplish if customer Burger King starts with B than default A-E into a powerapp field.  If I can accomplish this I already have the rest taken care of on teh Sharepoint side, I simply need to capture this value upstream within the Powerapps.  Obviously I would have to this for all ranges within the alphabet.  If anyone has any ideas how to accomlish this I would appreciate it.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Default value based upon first letter within range

Hi @Dudditz 

One way to do this is to create a collection of start letters and folder names. Here's a formula that you can add to the OnStart property of your app.

ClearCollect(FolderNames,
{Letter:"A", Folder:"A-E"},
{Letter:"B", Folder:"A-E"},
{Letter:"C", Folder:"A-E"},
{Letter:"D", Folder:"A-E"},
{Letter:"E", Folder:"A-E"},
{Letter:"F", Folder:"F-L"},
{Letter:"G", Folder:"F-L"},
{Letter:"H", Folder:"F-L"},
{Letter:"I", Folder:"F-L"},
{Letter:"J", Folder:"F-L"},
{Letter:"K", Folder:"F-L"},
{Letter:"L", Folder:"F-L"},
{Letter:"M", Folder:"M-S"},
{Letter:"N", Folder:"M-S"},
{Letter:"O", Folder:"M-S"},
{Letter:"P", Folder:"M-S"},
{Letter:"Q", Folder:"M-S"},
{Letter:"R", Folder:"M-S"},
{Letter:"S", Folder:"M-S"},
{Letter:"T", Folder:"T-Z"},
{Letter:"U", Folder:"T-Z"},
{Letter:"V", Folder:"T-Z"},
{Letter:"W", Folder:"T-Z"},
{Letter:"X", Folder:"T-Z"},
{Letter:"Y", Folder:"T-Z"},
{Letter:"Z", Folder:"T-Z"}
)

This formula creates a collection called FolderNames that looks like this.

 

image.png

 

To find the folder name, you can then lookup your target name with a formula that looks like this:

LookUp(FolderNames, 
Letter=Left("Burger King", 1)).Folder

View solution in original post

1 REPLY 1
Super User
Super User

Re: Default value based upon first letter within range

Hi @Dudditz 

One way to do this is to create a collection of start letters and folder names. Here's a formula that you can add to the OnStart property of your app.

ClearCollect(FolderNames,
{Letter:"A", Folder:"A-E"},
{Letter:"B", Folder:"A-E"},
{Letter:"C", Folder:"A-E"},
{Letter:"D", Folder:"A-E"},
{Letter:"E", Folder:"A-E"},
{Letter:"F", Folder:"F-L"},
{Letter:"G", Folder:"F-L"},
{Letter:"H", Folder:"F-L"},
{Letter:"I", Folder:"F-L"},
{Letter:"J", Folder:"F-L"},
{Letter:"K", Folder:"F-L"},
{Letter:"L", Folder:"F-L"},
{Letter:"M", Folder:"M-S"},
{Letter:"N", Folder:"M-S"},
{Letter:"O", Folder:"M-S"},
{Letter:"P", Folder:"M-S"},
{Letter:"Q", Folder:"M-S"},
{Letter:"R", Folder:"M-S"},
{Letter:"S", Folder:"M-S"},
{Letter:"T", Folder:"T-Z"},
{Letter:"U", Folder:"T-Z"},
{Letter:"V", Folder:"T-Z"},
{Letter:"W", Folder:"T-Z"},
{Letter:"X", Folder:"T-Z"},
{Letter:"Y", Folder:"T-Z"},
{Letter:"Z", Folder:"T-Z"}
)

This formula creates a collection called FolderNames that looks like this.

 

image.png

 

To find the folder name, you can then lookup your target name with a formula that looks like this:

LookUp(FolderNames, 
Letter=Left("Burger King", 1)).Folder

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,141)