cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fedir_Redko
Frequent Visitor

If statement problem for Patching of SharePoint

Hello everybody,
I have an app it's just one screen and it's main function is to create or update records in the SharePoint list so I can use that later in Power BI.

The list is simple consist of 6 fields:
1) UnitNumb - One line Text.
2) Line - Choice 
3) IN - Date (When Unit start to work) 
4) OUT - Date ( When Unit stopped to work)
5) WorkshopEnd - Date(RepirDate)
6) Comment - Lines of the text

In the app I have 5 fields: 
 1) Text Field for the data from the scanner - UnitNumb
2) Combobox1 for the choose of the Line
3) Combobox2 for the choose of the work type (IN, OUT, Workshop)
4) DatePicker for choose of the date
5) Comment Text input
 And I have a button with which I want to update list or create a new records in it. 
The logic about which I thought was next:

Layer 1: Check in the list if there is present a UnitNum with the name from TextInput field.
IF No: Create a new record with UnitNum,.
If Yes: Go to the Layer 2
Layer 2: Check SharePoint list if: UnitName is in the list (non mandatory because done on the layer 1), Present Record with Chosen in the Combobox Line, if this record have empty OUT and Workshop fields and IN field is populated.
If NO: Create a new records.
IF Yes: Update existing record. 
Precondition is that user know if the Unit is IN, OUT or goes to Workshop.
What my problem here is Layer 2 logic because patching isn't a problem for me. 
So can somebody help with this? 
Thank you in advance!

1 ACCEPTED SOLUTION

Accepted Solutions

@v-xida-msft  so the last formula which you send is worked but with the small adjustment from my side.
The correct answer if somebody will need it is next: 
If(
TextInput3.Text in 'Unit Tracker'.UnitNum,
If(
!IsEmpty( /* <-- Modify formula here. Type !IsBlank() rather than IsBlank() */
Filter('Unit Tracker', TextInput3.Text in UnitNum && IsBlank(OUT) && IsBlank(WorkshopEnd) && !IsBlank(IN))
),
Patch(
'Unit Tracker',
First(Filter('Unit Tracker', TextInput3.Text in UnitNum, IsBlank(OUT), IsBlank(WorkshopEnd), !IsBlank(IN))),
{
OUT: DatePicker1.SelectedDate
}
),
Patch(
'Unit Tracker',
Defaults('Unit Tracker'),
{
UnitNum: TextInput3.Text,
Line: ComboBox2.Selected,
IN: If(ComboBox1.Selected.Value = "IN", DatePicker1.SelectedDate, ""),
OUT: If(ComboBox1.Selected.Value = "OUT", DatePicker1.SelectedDate,""),
WorkshopEnd: If(ComboBox1.Selected.Value = "Workshop", DatePicker1.SelectedDate, ""),
Comment: TextInput2.Text
}
)
),
Patch(
'Unit Tracker',
Defaults('Unit Tracker'),
{
UnitNum: TextInput3.Text,
Line: ComboBox2.Selected,
IN: If(ComboBox1.Selected.Value = "IN", DatePicker1.SelectedDate, ""),
OUT: If(ComboBox1.Selected.Value = "OUT", DatePicker1.SelectedDate,""),
WorkshopEnd: If(ComboBox1.Selected.Value = "Workshop", DatePicker1.SelectedDate, ""),
Comment: TextInput2.Text
}
)
)

View solution in original post

8 REPLIES 8
v-xida-msft
Community Support
Community Support

Hi @Fedir_Redko ,

Based on the formula that you provided, I think there is something wrong with it. Please consider modify your formula as below:

If(
   TextInput3.Text in 'Unit Tracker'.UnitNum,
   If(     /* <-- Modify formula Start */
       IsBlank(LookUp('Unit Tracker', UnitNum = TextInput3.Text, OUT)) && IsBlank(LookUp('Unit Tracker', UnitNum = TextInput3.Text, WorkshopEnd)) && !IsBlank(LookUp('Unit Tracker', UnitNum = TextInput3.Text, IN)),
       Patch(
              'Unit Tracker',
              Last(Filter('Unit Tracker', UnitNum = TextInput3.Text && IsBlank(OUT) && IsBlank(WorkshopEnd) && !IsBlank(IN))),
              {
                OUT: DatePicker1.SelectedDate
              }
       ),              /* <-- Modify formula End */
       Patch(
               'Unit Tracker',
               Defaults('Unit Tracker'),
               {
                 UnitNum: TextInput3.Text,
                 Line: ComboBox2.Selected,
                 IN: If(ComboBox1.Selected.Value = "IN", DatePicker1.SelectedDate, ""),
                 OUT: If(ComboBox1.Selected.Value = "OUT",
DatePicker1.SelectedDate,""),
                 WorkshopEnd: If(ComboBox1.Selected.Value = "Workshop",DatePicker1.SelectedDate,""),
                 Comment: TextInput2.Text
              }
        )
     ),
     Patch(
            'Unit Tracker',
             Defaults('Unit Tracker'),
             {
                UnitNum: TextInput3.Text,
                Line: ComboBox2.Selected,
                IN: If(ComboBox1.Selected.Value = "IN", DatePicker1.SelectedDate, ""),
                OUT: If(ComboBox1.Selected.Value = "OUT", DatePicker1.SelectedDate,""),
                WorkshopEnd: If(
ComboBox1.Selected.Value = "Workshop", DatePicker1.SelectedDate, ""),
                Comment: TextInput2.Text
             }
      )
)

or

If(
   TextInput3.Text in 'Unit Tracker'.UnitNum,
   If(     /* <-- Modify formula Start */
       IsBlank(LookUp('Unit Tracker', UnitNum = TextInput3.Text && IsBlank(OUT) && IsBlank(WorkshopEnd) && !IsBlank(IN))),
       Patch(
              'Unit Tracker',
              Last(Filter('Unit Tracker', UnitNum = TextInput3.Text && IsBlank(OUT) && IsBlank(WorkshopEnd) && !IsBlank(IN))),
              {
                OUT: DatePicker1.SelectedDate
              }
       ),              /* <-- Modify formula End */
       Patch(
               'Unit Tracker',
               Defaults('Unit Tracker'),
               {
                 UnitNum: TextInput3.Text,
                 Line: ComboBox2.Selected,
                 IN: If(ComboBox1.Selected.Value = "IN", DatePicker1.SelectedDate, ""),
                 OUT: If(ComboBox1.Selected.Value = "OUT",
DatePicker1.SelectedDate,""),
                 WorkshopEnd: If(ComboBox1.Selected.Value = "Workshop",DatePicker1.SelectedDate,""),
                 Comment: TextInput2.Text
              }
        )
     ),
     Patch(
            'Unit Tracker',
             Defaults('Unit Tracker'),
             {
                UnitNum: TextInput3.Text,
                Line: ComboBox2.Selected,
                IN: If(ComboBox1.Selected.Value = "IN", DatePicker1.SelectedDate, ""),
                OUT: If(ComboBox1.Selected.Value = "OUT", DatePicker1.SelectedDate,""),
                WorkshopEnd: If(
ComboBox1.Selected.Value = "Workshop", DatePicker1.SelectedDate, ""),
                Comment: TextInput2.Text
             }
      )
)

 

Please consider take a try with above solution, then check if the issue is dolved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Great thank you I'll try your solution and let you know the result! 

Hi @Fedir_Redko ,

Have you taken a try with the solution I provided above?

Have you solved your problem?

 

If you have solved your problem, please go ahead to click "Accept as Solution" to identify this thread has been solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-xida-msft 
Unfortunately problem still not solved. Second variant is not working at all. The first one is working correctly only when I create a first unique unit number after that it's starts to duplicate records. Here is a screenshot.

Hi @Fedir_Redko ,

Based on the needs that you mentioned, I think the solution I provided above could achieve your needs. Please consider simplify your formula as below:

modify your formula as below:

If(
   TextInput3.Text in 'Unit Tracker'.UnitNum,
   If(     
       !IsBlank(      /* <-- Modify formula here. Type !IsBlank() rather than IsBlank() */
                LookUp('Unit Tracker', UnitNum = TextInput3.Text && IsBlank(OUT) && IsBlank(WorkshopEnd) && !IsBlank(IN))
       ),
       Patch(
              'Unit Tracker',
              LookUp('Unit Tracker', UnitNum = TextInput3.Text && IsBlank(OUT) && IsBlank(WorkshopEnd) && !IsBlank(IN)),
              {
                OUT: DatePicker1.SelectedDate
              }
       )
     ),
     Patch(
            'Unit Tracker',
             Defaults('Unit Tracker'),
             {
                UnitNum: TextInput3.Text,
                Line: ComboBox2.Selected,
                IN: If(ComboBox1.Selected.Value = "IN", DatePicker1.SelectedDate, ""),
                OUT: If(ComboBox1.Selected.Value = "OUT", DatePicker1.SelectedDate,""),
                WorkshopEnd: If(
ComboBox1.Selected.Value = "Workshop", DatePicker1.SelectedDate, ""),
                Comment: TextInput2.Text
             }
      )
)

 

Please consider take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-xida-msft  now it's not working at all, even with the first unique record.

Hi @Fedir_Redko ,

Have you typed proper formula within your app?

 

Also please take a try with the following formula:

If(
   TextInput3.Text in 'Unit Tracker'.UnitNum,
   If(     
       !IsEmpty(      /* <-- Modify formula here. Type !IsBlank() rather than IsBlank() */
                Filter('Unit Tracker', TextInput3.Text in UnitNum, IsBlank(OUT), IsBlank(WorkshopEnd), !IsBlank(IN))
       ),
       Patch(
              'Unit Tracker',
              First(Filter('Unit Tracker', TextInput3.Text in UnitNum, IsBlank(OUT), IsBlank(WorkshopEnd), !IsBlank(IN))),
              {
                OUT: DatePicker1.SelectedDate
              }
       )
     ),
     Patch(
            'Unit Tracker',
             Defaults('Unit Tracker'),
             {
                UnitNum: TextInput3.Text,
                Line: ComboBox2.Selected,
                IN: If(ComboBox1.Selected.Value = "IN", DatePicker1.SelectedDate, ""),
                OUT: If(ComboBox1.Selected.Value = "OUT", DatePicker1.SelectedDate,""),
                WorkshopEnd: If(
ComboBox1.Selected.Value = "Workshop", DatePicker1.SelectedDate, ""),
                Comment: TextInput2.Text
             }
      )
)

 

If the issue still exists, please consider re-generate a new app based on your data source, then try above formula again, check if the issue is fixed.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-xida-msft  so the last formula which you send is worked but with the small adjustment from my side.
The correct answer if somebody will need it is next: 
If(
TextInput3.Text in 'Unit Tracker'.UnitNum,
If(
!IsEmpty( /* <-- Modify formula here. Type !IsBlank() rather than IsBlank() */
Filter('Unit Tracker', TextInput3.Text in UnitNum && IsBlank(OUT) && IsBlank(WorkshopEnd) && !IsBlank(IN))
),
Patch(
'Unit Tracker',
First(Filter('Unit Tracker', TextInput3.Text in UnitNum, IsBlank(OUT), IsBlank(WorkshopEnd), !IsBlank(IN))),
{
OUT: DatePicker1.SelectedDate
}
),
Patch(
'Unit Tracker',
Defaults('Unit Tracker'),
{
UnitNum: TextInput3.Text,
Line: ComboBox2.Selected,
IN: If(ComboBox1.Selected.Value = "IN", DatePicker1.SelectedDate, ""),
OUT: If(ComboBox1.Selected.Value = "OUT", DatePicker1.SelectedDate,""),
WorkshopEnd: If(ComboBox1.Selected.Value = "Workshop", DatePicker1.SelectedDate, ""),
Comment: TextInput2.Text
}
)
),
Patch(
'Unit Tracker',
Defaults('Unit Tracker'),
{
UnitNum: TextInput3.Text,
Line: ComboBox2.Selected,
IN: If(ComboBox1.Selected.Value = "IN", DatePicker1.SelectedDate, ""),
OUT: If(ComboBox1.Selected.Value = "OUT", DatePicker1.SelectedDate,""),
WorkshopEnd: If(ComboBox1.Selected.Value = "Workshop", DatePicker1.SelectedDate, ""),
Comment: TextInput2.Text
}
)
)

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (19,578)