cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BrianHFASPS
Level 8

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
PowerApps Staff CarlosFigueira
PowerApps Staff

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
PowerApps Staff CarlosFigueira
PowerApps Staff

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
BrianHFASPS
Level 8

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. 

PowerApps Staff CarlosFigueira
PowerApps Staff

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 157 members 5,220 guests
Please welcome our newest community members: