Unanswered

Replace StartsWith with Search

(0) Share

Report

Posted on by Community Power Pla...

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

Whenever you search on the 'Prod. usage', it should show the Omschrijving in the dropdown from the same row. But you can also search on the Omschrijving.

There are around 1600 rows inside of my collection, so we can just use the search function.

Now this is my full code. How can I implement it in here? As I had the Filter & distinct function over it.

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

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (0)

WarrenBelz 153,084 Most Valuable Professional on at

Like (1)

Report
Hi @Anonymous ,

You can use the in filter,

Filter( Lijst_Eindp; TekstZoekVeld_1.Text in 'Prod. usage' || TekstZoekVeld_1.Text in Omschrijving )

which has the same affect

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
Thank you for your answer @WarrenBelz

Is there any way to fix the delegation on the following things;

1. https://gyazo.com/19cf42c0219e130ba4b3bd2fec9dbb15
2. https://gyazo.com/2fdf8161adbac840b0223b5ff1a895f5
3. https://gyazo.com/1bd6d1d50fc95145c58555b1f5274873

There might be more than 2000 items in the future. I am currently using a DropdownBox

More to the bottom, there is also a section with the "StartsWith" operator

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, TekstZoekVeld_1.Text in 'Prod. usage' || TekstZoekVeld_1.Text in Omschrijving ), 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" )

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
So whenever the textsearchbox is empty && Checkbox2_1.Value = false && Checkbox2.Value = true,

It displays this Omschrijving, which is correct; https://gyazo.com/adab9d4c73ed05aca5f2d7eb7aaaad39

Whenever I search for "L=" it displays nothing; https://gyazo.com/adfd59d095a4e756728707cb772d0a3c

The ''Prod. usage' for that row is "Bev M; Montage"

Code;

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, TekstZoekVeld_1.Text in 'Prod. usage' || TekstZoekVeld_1.Text in Omschrijving && 'Prod. usage' = "Bev; Montage" ), Omschrijving ) )

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, TekstZoekVeld_1.Text in 'Prod. usage' || TekstZoekVeld_1.Text in Omschrijving && 'Prod. usage' = "Bev; Montage" ), 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" )

Edit:

I tried to use the in function too but then it results all of the items in 'Prod. usage' with L=

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, "Bev; Montage" in 'Prod. usage' && TekstZoekVeld_1.Text in 'Prod. usage' || TekstZoekVeld_1.Text in Omschrijving ), Omschrijving ) )

Was this reply helpful? Yes No
WarrenBelz 153,084 Most Valuable Professional on at

Like (0)

Report

@Anonymous ,

I suspect that is why you were using StartsWith (Delegation).

The only way to "fix" Delegation is to either have a Delegable query or a dataset in a local collection (I believe we have discussed this one before). I have done a blog on Delegation and a supplementary item using the With () Statement.

If you find something in these that may suit you, I am happy to assist further with code you try on it.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Sadly there are more than 2000 rows in my database so With is not an option to use. I'll find another way. Thank you for your help, appreciate that!

Was this reply helpful? Yes No
WarrenBelz 153,084 Most Valuable Professional on at

Like (0)

Report

@Anonymous ,

There are options for large collections over 2,000 items in the blogs.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
I've read your blog. So basically you're explaining this;
ClearCollect( colMyColl, MyList ); If( CountRows(colMyColl) = 2000, Set( vID, Max(colMyColl,IDRef) ); Collect( colMyColl, Filter( MyList, IDRef>vID ) ) ); If( CountRows(colMyColl) = 4000, Set( vID, Max(colMyColl,IDRef) ); Collect( colMyColl, Filter( MyList, IDRef>vID ) ) ); Then keep going in batches of 2000

My list has 50.000 rows, should I be doing this in batches of 2000 untill 50.000 or is there an easier way? @WarrenBelz

Was this reply helpful? Yes No
WarrenBelz 153,084 Most Valuable Professional on at

Like (0)

Report

@Anonymous ,

We have gone from 1,600 to maybe over 2,000 earlier and now to 50,000 and I would not have suggested the collection method for this size list had I known it before.

You simply cannot effectively use Search or in filters on lists this big.

If you can filter the result to under 2,000 with a Delegable filter, then I have mentioned a possible workaround in my blog on using the With() statement , and I believe I have covered this one in a previous post of yours, however I suspect your list size precluded this.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No