Replace StartsWith with Search

[Anonymous]

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")

[haroldbk_msft]

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.

[Anonymous]

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")

[haroldbk_msft]

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 .

[Anonymous]

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")

[Maurits]

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

[Anonymous]

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?

[Maurits]

Hi @Anonymous , 
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!

[Drrickryp]

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.  

[haroldbk_msft]

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.