Unanswered

Filter sharepointlist into a collection and load old data

(1) Share

Report

Posted on by vidarv

Hi,

I have a SharePointlist that would increase and get larger. The sharepointlist is now 10 000.

In the app I have made a collection that will only show data from today split, into half year (H1, H2) depending on wich month we are in.

Onvisible

If(
 Month(Now()) >= 01 && Month(Now()) <= 06,
 ClearCollect(
 colKontaktMedArbeidsgivere,
 Filter(
 Kontakt_med_arbeidsgivere,
 År = Year(Now()) And Halvår = "H1"
 )
 ),
 ClearCollect(
 colKontaktMedArbeidsgivere,
 Filter(
 Kontakt_med_arbeidsgivere,
 År = Year(Now()) And Halvår = "H2"
 )
 )
)

The gallery code

SortByColumns(
 Filter(
 colKontaktMedArbeidsgivere,
 StartsWith(
 'Type henvendelse',
 ddTypeHenvendelseFilterArbG_1.Selected.Name
 ) && StartsWith(
 Kanal,
 ddKanalFilterArbG_1.Selected.Name)
 && StartsWith(
 'Arbeidsgivers landstilhørighet',
 ddArbeidsgiversLandFilterArbG_1.Selected.Name)
 && StartsWith(
 Nace,
 ddNaceFilterArbG_1.Selected.Name),
 GruppeNavn =varFylkeNavn Or varAdminNCO ="AdminNCO"
 ),
 "Oppdatert_dato",
 SortOrder.Descending
)

In the app you will have the ability to pick year from a dropdown and halfyear from a dropdown and then click on butten Load data
I cant get this code to work

 ClearCollect(
 colKontaktMedArbeidsgivere,
 Filter(
 Kontakt_med_arbeidsgivere,
 År = ddAar_data.Selected.Value And Halvår = ddHalvår_data.Selected.Value
 )
 )

If I choose 2023 it seems to work, but no other year.

Please help

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

hthpowerapper 364 Super User 2024 Season 1 on at

Like (1)

Report

The issue you may be encountering is with data types. Verify that you're not sending dates as texts or texts as dates. if the sharepoint list date column is a text column it may not filter correctly

Was this reply helpful? Yes No
poweractivate 11,078 Most Valuable Professional on at

Like (1)

Report
It seems like you may want to filter the SharePoint list data into a collection based on selected year and half-year (H1, H2). Your formula may be working when the app becomes visible and the formula for the gallery, but you're having trouble loading the data for other years.

It's hard to say without seeing the exact contents of your dropdown controls ddAar_data and ddHalvår_data, but you might want to ensure that the data types match correctly.

Assuming År in Kontakt_med_arbeidsgivere is a number and the value in ddAar_data.Selected.Value is a text, you'll want to convert one to match the other's data type.

Here's an updated formula example for the button OnSelect property:

ClearCollect( colKontaktMedArbeidsgivere, Filter( Kontakt_med_arbeidsgivere, År = Value(ddAar_data.Selected.Value) And Halvår = ddHalvår_data.Selected.Value ) )

I added the Value() function to convert the selected value from the dropdown control to a number, so that it can be compared with the År field in Kontakt_med_arbeidsgivere.

This formula should work for any year as long as the selected values from the dropdowns are consistent with the data in your SharePoint list.

If this doesn't resolve the issue, please provide more information about the structure of your SharePoint list and the values in the dropdown controls.

See if it helps @vidarv

Was this reply helpful? Yes No
vidarv on at

Like (1)

Report

Thank you for answering @hthpowerapper . The sharepoint coloumn is date

Was this reply helpful? Yes No
EddieE 4,641 Moderator on at

Like (2)

Report
@vidarv

Another option here may be to check that you are getting data back into the collection when selecting Year other than 2023. Try adding a label with this code

CountRows(colKontaktMedArbeidsgivere)

Then select different years from the dropdown. If you are getting data back - ie the label shows values >0 each time - then the issue is your gallery filter.

Was this reply helpful? Yes No
vidarv on at

Like (1)

Report
Thank you for answering @poweractivate I have to test it more before I accept. It now works on some of the data, not the early years. Mabye it is something with the data. The ddAar-data are setup as a value

["","2023","2022","2021","2020","2019","2018"]

The År are set as a text in the sharepoint list

Was this reply helpful? Yes No
Verified answer

poweractivate 11,078 Most Valuable Professional on at

Like (1)

Report
Seems like the År field in your SharePoint list is a text line, and the ddAar_data control has values that are also text. Therefore, you should not need the Value() function in your formula, as both sides are text and can be compared directly.

Here's an updated formula example for the button OnSelect property:

ClearCollect( colKontaktMedArbeidsgivere, Filter( Kontakt_med_arbeidsgivere, År = ddAar_data.Selected.Value && Halvår = ddHalvår_data.Selected.Value ) )

This formula should compare the text values without any conversion, and it should work for any year, including the early ones.

If you still experience issues with some specific years, please double-check the values in the SharePoint list for those years, especially the År and Halvår fields, and make sure they match exactly with the values in your dropdown controls.

Also, make sure there are no leading or trailing spaces or any other invisible characters that might be causing a mismatch.

See if it helps @vidarv

Was this reply helpful? Yes No
vidarv on at

Like (1)

Report

Thank you @poweractivate There was some bugs in the earlier data that resulted in no data. Te formula is now working.
Thank you @EddieE for your contribution.

Was this reply helpful? Yes No