cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

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
Highlighted
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
Highlighted
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
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (6,888)