Creating Collection - Filtering Calculated Column

[TomiJoe]

Hi!

I'm completely out of ideas right now. Wasted the last two days figuring this one out.

Here's my problem:

I'm trying to create a Collection and filtering it to only Collect rows where the Title is the current user and a calculated column is equal to a variable I declared:

ClearCollect(collGeleisteteStunden;Filter(Zeiterfassung;varKW=Kalenderwoche;User().Email=Titel));;

However, it seems like the collection has some problems with my "varKW" variable (This variable represents the current calendar week) because the collection remains empty when I try to filter "varKW=Kalenderwoche".

BUT! here's the really, really strange part. When I change my Browser language back to english again (I had to change to german because some problems with another part of the App) IT WORKS JUST FINE!?

This doesn't make any sense to me....

Any help is greatly appreciated!

[rebeccas]

When you are looking at the collection and you say it isn't showing anything...Click up top on View, Variables and see what is the value showing for the variable varKW that you are trying to look to?

[TomiJoe]

I already checked that. The variable should be 7 and it is.

I also checked that there are items in the list where calendar week = 7

🙂

[AGroegelich]

Hi @TomiJoe 

Have you already tried to set you variable with an string type instead of a numeric type. So something like:

Kalenderwoche="7"

I am a newbe in PowerApps, but one strange thing I have seen so far is that PowerApps sometimes threats numeric columns as a string. Or at least when an Excel sheet is the data source. Maybe this helps

[TomiJoe]

 @AGroegelich Thanks for contributing to my question 🙂

That also came to my mind and I tried different variations of this. But then I figured it doesn't matter because as soon as I set English as my preferred visual language - it works. And I never get an error or something. It simply does not create the collection if german is the preferred language.

[v-yutliu-msft]

Hi @TomiJoe ,

Do you mean that your formula works when your browser's language is English, but doesn't work when your browser's language is German?

Could you tell me how do you set varKW and the data type of Kalenderwoche?

If you set varKW by using this formula: Set(varKW,Weekday(Today())), then varKW is a number value.

In this situation, you need to make sure Kalenderwoche field is number data type too. Or else, the formula will not work.

Please check these things:

1)set a gallery's Items:

Filter(Zeiterfassung;varKW=Kalenderwoche;User().Email=Titel)

If this work, then the filter part has no problem.

That should be ClearCollect function not works in German language.

2)set a gallery's Items:

Filter(Zeiterfassung;Kalenderwoche=7;User().Email=Titel)

If this work, then Kalenderwoche is number type. Please check whether varKW is number type.

3)set a gallery's Items:

Filter(Zeiterfassung;Kalenderwoche="7";User().Email=Titel)

If this work, then Kalenderwoche is text type. Please check whether varKW is text type.

To sum up, if formula1 works that should be ClearCollect problem.

If formula2 or formula3 works that should be variable problem. 

If formula1, formula2,formula3 all not works, that should be Filter function problem.

Firstly,find the where the key problem is. Then modify your formula based on this.

If the problem still exists, the problem maybe not about your formula, maybe the region server.

In this situation, I suggest you ask for help here:

https://powerapps.microsoft.com/en-us/support/pro/ticket/

Best regards,

[TomiJoe · Accepted solution]

@v-yutliu-msftThanks to you as well for your answer!

I found the "solution" now while I was messing around.

This is the initializing of the "varKW" variable:

Set(varKW;RoundDown((RoundUp(Now()-Date(Year(Now()-Weekday(Now()-1)+4);1;3);0)+Weekday(Date(Year(Now()-Weekday(Now()-1)+4);1;3))+5)/7;0));;

The variable now is a number

Now I added another variable directly under "varKW":

Set(varKWtext;varKW&".00000000000000");;

The reason behind this is, that the value "Kalenderwoche" is shown as a 7.00000000000000. It seems like that this is no problem in english but in german powerapps uses an "," instead of "." as decimal point.

Sorry for the bad explanation but I'm not exactly sure what happened here. Thanks for al your contributions!