cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

Forall / If question?

I am trying to create a record if it doesn't exist but not create it if it already does. I have a ForAll() with If() inside. If I run it on same data set it keeps creating so my If check isn't working for some reason? Any suggestions?

ForAll(
    Roster,
    If(
        IsEmpty(
            Filter(
                '[dbo].[Activity Signup]',
                DailyActUID = 'User ID',
                'Date End' = 'Class End Date'.SelectedDate
            )
        ),
        Patch(
            '[dbo].[Activity Signup]',
            Defaults('[dbo].[Activity Signup]'),
            {
                DailyActUID: 'User ID',
                'Date Start': 'Class Start Date'.SelectedDate,
                'Date End': 'Class End Date'.SelectedDate
            }
        )
    )
)
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Power Apps
Power Apps

Re: Forall / If question?

Date comparisons with Filter for SQL have a problem (known issue in PowerApps, same problem with SharePoint as well). For SQL, you can work around this by having a calculated column that converts the date to a string value, and do the comparison against that column. For example, you can alter your table:

ALTER TABLE [dbo].[Activity Signup]
ADD [Date End String] AS FORMAT([Date End], 'yyyy-MM-dd')

And if you update your expression in PowerApps to the following:

ForAll(
    Roster,
    If(
        IsEmpty(
            Filter(
                '[dbo].[Activity Signup]',
                DailyActUID = 'User ID',
                'Date End String' = Text('Class End Date'.SelectedDate, "yyyy-mm-dd")
            )
        ),
        Patch(
            '[dbo].[Activity Signup]',
            Defaults('[dbo].[Activity Signup]'),
            {
                DailyActUID: 'User ID',
                'Date Start': 'Class Start Date'.SelectedDate,
                'Date End': 'Class End Date'.SelectedDate
            }
        )
    )
)

Hope this helps!

View solution in original post

3 REPLIES 3
Highlighted
Power Apps
Power Apps

Re: Forall / If question?

Date comparisons with Filter for SQL have a problem (known issue in PowerApps, same problem with SharePoint as well). For SQL, you can work around this by having a calculated column that converts the date to a string value, and do the comparison against that column. For example, you can alter your table:

ALTER TABLE [dbo].[Activity Signup]
ADD [Date End String] AS FORMAT([Date End], 'yyyy-MM-dd')

And if you update your expression in PowerApps to the following:

ForAll(
    Roster,
    If(
        IsEmpty(
            Filter(
                '[dbo].[Activity Signup]',
                DailyActUID = 'User ID',
                'Date End String' = Text('Class End Date'.SelectedDate, "yyyy-mm-dd")
            )
        ),
        Patch(
            '[dbo].[Activity Signup]',
            Defaults('[dbo].[Activity Signup]'),
            {
                DailyActUID: 'User ID',
                'Date Start': 'Class Start Date'.SelectedDate,
                'Date End': 'Class End Date'.SelectedDate
            }
        )
    )
)

Hope this helps!

View solution in original post

Highlighted
Post Patron
Post Patron

Re: Forall / If question?

Thank you so much! I would have beaten my head against the wall on that one forever. I am fairly new to PowerApps and still learning the quirks. Is there a list some where of these known issues so I don't run into one blindly again? Also a curious question when I pasted your code into PowerApps I got this line changed:

'Date End String' = Text('Class End Date'.SelectedDate, "[$-en-US]yyyy-mm-dd")

I removed the 

[$-en-US]

but I wasn't sure why it got insterted.

 

Thanks again. 

Highlighted
Power Apps
Power Apps

Re: Forall / If question?

I believe @LanceDelano was working on a list of the known issues regarding filtering issues (mostly related to delegation, but it should include those as well) - I'm not sure when this will be published.

And if you run into issues, please feel free to reach out to the community, we're always happy to help!

Regarding the "[$-en-US]" that was added, this is a default behavior - PowerApps will add your locale in the expression, so that if your app is opened in another locale then it will be interpreted as the original maker intended. For this format ("yyyy-mm-dd") it doesn't matter, but numbers, for example, have a different representation in different countries (such as the use of comma or period as the decimal separator).

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (4,786)