cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

Replace StartsWith with Search

Hello! How can I replace StartsWith with Search in this formula? 


SortByColumns(
If(
   Checkbox2_1.Value = false && Checkbox2.Value = true; 
  If(
    IsBlank(TekstZoekVeld_1.Text);
    Distinct(Filter(
        Lijst_Eindp;
        'Prod. usage'="Bev M; Montage"); Omschrijving
    );
    Distinct(
    Filter(
            Lijst_Eindp;
        StartsWith('Prod. usage';TekstZoekVeld_1.Text)|| StartsWith(Omschrijving;TekstZoekVeld_1.Text)
    );Omschrijving)
  );
   Checkbox2_1.Value = true && Checkbox2.Value = false; 
   If(
    IsBlank(TekstZoekVeld_1.Text);
    Distinct(Filter(Lijst_Eindp; 'Prod. usage'="Bev M; Montage" && Left(Bestandsnaam1; 5) = DataCardValue34.Text);Omschrijving);
    Distinct(Filter(Lijst_Eindp; 'Prod. usage'="Bev M; Montage" && Left(Bestandsnaam1; 5) = DataCardValue34.Text;StartsWith(Omschrijving;TekstZoekVeld_1.Text));Omschrijving)
   )
); "Result")
11 REPLIES 11
Highlighted
Community Support
Community Support

Re: Replace StartsWith with Search

 

Search(Lijst_Eindp;TekstZoekVeld_1.Text;"'Prod. usage'","Omschrijving")

 

 

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

 

Note: Search() has delegation limitations so depending on the size of the data the results may not be correct.

Highlighted
Post Prodigy
Post Prodigy

Re: Replace StartsWith with Search

Hey @haroldbk_msft 

Whenever I replace the following code with your code I get an error: 

 

StartsWith('Prod. usage';TekstZoekVeld_1.Text)

 

What am I doing wrong?

 

Search(Lijst_Eindp;TekstZoekVeld_1.Text; "Prod. usage";"Omschrijving")

 


In the following code I need StartsWith replaced with Search:

SortByColumns(
If(
   Checkbox2_1.Value = false && Checkbox2.Value = true; 
  If(
    IsBlank(TekstZoekVeld_1.Text);
    Distinct(Filter(
        Lijst_Eindp;
        'Prod. usage'="Bev M; Montage"); Omschrijving
    );
    Distinct(
    Filter(
            Lijst_Eindp;
        StartsWith('Prod. usage';TekstZoekVeld_1.Text) || StartsWith(Omschrijving;TekstZoekVeld_1.Text)
    );Omschrijving)
  );
   Checkbox2_1.Value = true && Checkbox2.Value = false; 
   If(
    IsBlank(TekstZoekVeld_1.Text);
    Distinct(Filter(Lijst_Eindp; 'Prod. usage'="Bev M; Montage" && Left(Bestandsnaam1; 5) = DataCardValue34.Text);Omschrijving);
    Distinct(Filter(Lijst_Eindp; 'Prod. usage'="Bev M; Montage" && Left(Bestandsnaam1; 5) = DataCardValue34.Text;StartsWith(Omschrijving;TekstZoekVeld_1.Text));Omschrijving)
   )
); "Result")
Highlighted
Community Support
Community Support

Re: Replace StartsWith with Search

HI,

I don't know what your data source is and that would be helpful.

If it's SharePoint then the column name with a space would be something like this:

Tree_x0020_Name - where in SharePoint it's displayed as Tree Name.

When you view the field in a gallery it looks like this ThisItem.'Tree Name'

Then the Search expression would you like this

Search(Justalist,"Maple","Tree_x0020_Name")

Looks like there is a space in  Prod. usage. 

 

I would suggest checking how Power Apps is handling the space and test your search in a more simplified expression to get it tweaked .

 

Highlighted
Post Prodigy
Post Prodigy

Re: Replace StartsWith with Search

Thank you for your answer @haroldbk_msft 

It is indeed a space in Prod. usage

And I am using SharePoint as a database. So what am I doing wrong? I have replaced the Prod. usage with "Prod_x002e__x0020_usage", which is now correct but it still gives errors

Are you maybe able to change it for me?

Here is the full code: 

 

SortByColumns(
If(
   Checkbox2_1.Value = false && Checkbox2.Value = true; 
  If(
    IsBlank(TekstZoekVeld_1.Text);
    Distinct(Filter(
        Lijst_Eindp;
        'Prod. usage'="Bev M; Montage"); Omschrijving
    );
    Distinct(
    Filter(
            Lijst_Eindp;
        StartsWith('Prod. usage';TekstZoekVeld_1.Text) || StartsWith(Omschrijving;TekstZoekVeld_1.Text)
    );Omschrijving)
  );
   Checkbox2_1.Value = true && Checkbox2.Value = false; 
   If(
    IsBlank(TekstZoekVeld_1.Text);
    Distinct(Filter(Lijst_Eindp; 'Prod. usage'="Bev M; Montage" && Left(Bestandsnaam1; 5) = DataCardValue34.Text);Omschrijving);
    Distinct(Filter(Lijst_Eindp; 'Prod. usage'="Bev M; Montage" && Left(Bestandsnaam1; 5) = DataCardValue34.Text;StartsWith(Omschrijving;TekstZoekVeld_1.Text));Omschrijving)
   )
); "Result")

 

Highlighted
Resolver II
Resolver II

Re: Replace StartsWith with Search

Hi Timo, do I understand you well if I think you want to filter your results not only when Omschrijving starts with the content of Tekstzoekveld_1, but also when it contains this text on another position then at start?

If so, the Search() function is not what you need. The Search function returns a table, a dataset.
The StartsWith() function returns a boolean, just True or False whether Omschrijving starts with that Tekstzoekveld_1 content.


If you want to accomplish what I expect, I think you need the Find() function, not Search().
Find() searches for a given (case sensitive!!!) text within another text and returns the starting position of it, or blank if it's not found.
Maybe your StartsWith(Omschrijving;TekstZoekVeld_1.Text) should be replaced with something like:

!IsBlank(Find(Lower(TekstZoekVeld_1.Text), Lower(Omschrijving)))

The Lower() function on both fields are needed because of the case sensitivity of the Find function.
The !IsBlank() function makes that the result is True when the text is found, or False when the text is not found. So in fact the result is like your current StartsWith() function. Please note the ! before IsBlank(), otherwise the True and False will be the other way around as your current StartsWith() usage.

I haven't tested it, so hope I did not mistype something.
Good Luck!
Maurits

Highlighted
Post Prodigy
Post Prodigy

Re: Replace StartsWith with Search

Hey there @Maurits 

Thank you for your answer!

I will try your solution now.

I want the following steps:

Being able to search for 'Prod. usage' & Omschrijving and then show the Omschrijving (I am using a dropdown)

Currently this is the code for this: 

 

   Distinct(
    Filter(
            Lijst_Eindp,
        StartsWith('Prod. usage',TekstZoekVeld_1.Text) || StartsWith(Omschrijving,TekstZoekVeld_1.Text)
    ),Omschrijving)

 

 

This is my full code:

SortByColumns(
If(
   Checkbox2_1.Value = false && Checkbox2.Value = true, 
  If(
    IsBlank(TekstZoekVeld_1.Text),
    Distinct(Filter(
        Lijst_Eindp,
        'Prod. usage'="Bev M; Montage"), Omschrijving
    ),
    Distinct(
    Filter(
            Lijst_Eindp,
        StartsWith('Prod. usage',TekstZoekVeld_1.Text) || StartsWith(Omschrijving,TekstZoekVeld_1.Text)
    ),Omschrijving)
  ),
   Checkbox2_1.Value = true && Checkbox2.Value = false, 
   If(
    IsBlank(TekstZoekVeld_1.Text),
    Distinct(Filter(Lijst_Eindp, 'Prod. usage'="Bev M; Montage" && Left(Bestandsnaam1, 5) = DataCardValue34.Text),Omschrijving),
    Distinct(Filter(Lijst_Eindp, 'Prod. usage'="Bev M; Montage" && Left(Bestandsnaam1, 5) = DataCardValue34.Text,StartsWith(Omschrijving,TekstZoekVeld_1.Text)),Omschrijving)
   )
), "Result")

 

With your code, I can only search on the Omschrijving (I think). Are you able to implement your code inside of my code so I can just copy & paste it? 

How could I fix this?


Highlighted
Resolver II
Resolver II

Re: Replace StartsWith with Search

Hi @TimoMigchielsen , 
when I just take your codepart below:

Distinct(Filter(Lijst_Eindp,
	StartsWith('Prod. usage',TekstZoekVeld_1.Text) || 
	StartsWith(Omschrijving,TekstZoekVeld_1.Text)
    	),Omschrijving)

 I expect that could be replaced by this:

Distinct(Filter(Lijst_Eindp,
        !IsBlank(Find(Lower(TekstZoekVeld_1.Text), Lower('Prod. usage'))) || 
	!IsBlank(Find(Lower(TekstZoekVeld_1.Text), Lower(Omschrijving)))
    ),Omschrijving)

 

Hope that helps.
If not, I'll read it over here.

Good luck!

 

Highlighted
Super User III
Super User III

Re: Replace StartsWith with Search

Hi @Maurits 

I would take a different approach depending on how many items you have (or anticipate having) in your list.  

0-500 no need to do anything, just use Search()

500-2000 increase your data row limit to 2000.  File>Settings>Advanced Settings>data row limit.

2000-4000 Create a collection 

 

With({a:Sort(SPlist,ID,Ascending),b:Sort(SPlist,ID,Descending)},
ClearCollect(colSPlist,Filter(b,Not(ID in a.ID))))
   

 

For more than 4000 items do something else.  

Highlighted
Community Support
Community Support

Re: Replace StartsWith with Search

First off find() is not the correct function. Find will locate the position within string.

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-find

Find returns the starting position of the string that was found. Position 1 is the first character of the string. Find returns blank if the string in which you're searching doesn't contain the string for which you're searching.

see if this works 

Search(Lijst_Eindp, TekstZoekVeld_1.Text, 'Prod. usage', Omschrijving)

This will search for the text value in the  TekstZoekVeld_1 in both the 'Prod. usage' and Omschrijving fields and return any records that contain that text.

See if this works.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (1,919)