cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Users online (7,268)