If statement problem for Patching of SharePoint

[Fedir_Redko]

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!

[v-xida-msft]

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,

[Fedir_Redko]

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

[v-xida-msft]

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,

[Fedir_Redko]

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.

[v-xida-msft]

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,

[Fedir_Redko]

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

[v-xida-msft]

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,

[Fedir_Redko · Accepted solution]

@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
}
)
)