Filter sharepointlist into a collection and load old data

[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

[hthpowerapper]

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

[poweractivate]

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 

[vidarv]

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

[EddieE]

@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.

[vidarv]

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

[poweractivate · Accepted solution]

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 

[vidarv]

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.