cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Continued Contributor
Continued Contributor

exit formula if a condition is true

I have asked this before, but still not working.

I have a screen.

I have a table -  "tbl_Tasks"

I have a dropdown  -  dwnChange_Status -   Text = ["Open", "Closed", "In Progress"]

 

 

What I need:

I need to make sure a person cant put 2 rows to an "Open" Status. if they choose  "Open" in the dropdown. and another row in the data already has a task in "Open" status.

The code continues to execute even after he Notify. I want it to exit the formula ?

 

For the "OnChange" event: i have:

Set(lastActivityTime, Now());
If(
!IsBlank(LookUp('[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]', Status = "Open" && lbl_Racfid_ms.Text = ID_Racfid)),
Notify("A Task is Already Open !", NotificationType.Warning,3000)
);
// otherwise lbl_OpenTasks_chk = 0 then perform code
UpdateContext({varDate: Now()});UpdateContext({varValue: ""});
Patch('[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]',LookUp( '[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]', NSC_Id = Value(txt_NextId.Text)),
{
Status:dwn_ChangeStatus.Selected.Value}
);

If(dwn_ChangeStatus.Selected.Value = "In Progress",
Patch(
'[dbo].[t_nsc_trackcode_trans_time_entry_pa]',
Defaults('[dbo].[t_nsc_trackcode_trans_time_entry_pa]'),
{
//NSC_Id_Time: IsNumeric(txt_NextId.Text),
NSC_ID_Ref: Value(txt_NextId.Text),
opened_at: DateTimeValue(txt_ReOpenedDate.Text),
inprogress_or_closed_at: Now()
}
));
If(dwn_ChangeStatus.Selected.Value = "Closed" ,
Patch(
'[dbo].[t_nsc_trackcode_trans_time_entry_pa]',
Defaults('[dbo].[t_nsc_trackcode_trans_time_entry_pa]'),
{
//NSC_Id_Time: IsNumeric(txt_NextId.Text),
NSC_ID_Ref: Value(txt_NextId.Text),
opened_at: Now(),
inprogress_or_closed_at: Now()
}
));
If(dwn_ChangeStatus.Selected.Value = "Open",Set(TheVar,Now());
Reset(txt_ReOpenedDate));
If(dwn_ChangeStatus.Selected.Value = "Open",Set(varCSPSALES, "YES"));
If(dwn_ChangeStatus.Selected.Value = "Closed",Set(varCSPSALES, "YES"));
If(dwn_ChangeStatus.Selected.Value = "In Progress",Set(varCSPSALES, "NO"));

 

This code only works one time, because if a person tries to change the "Status" from "Open" to "In Progress" it catches the check again and will not allow me to change another row again.

 

Thanks

Dave

 

 

 

 

 

powerapps status.jpg

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @DAVIDPOWELL ,

I forgot one "//" and ")" in my last reply.

Try this:

Set(lastActivityTime, Now());
//action1
If(
     !IsBlank(LookUp('[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]', Status = "Open" && lbl_Racfid_ms.Text = ID_Racfid)),
//condition
     Notify("A Task is Already Open !", NotificationType.Warning,3000),
//result1
     UpdateContext({varDate: Now()});UpdateContext({varValue: ""});Patch('[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]',LookUp( '[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]', NSC_Id = Value(txt_NextId.Text)),{Status:dwn_ChangeStatus.Selected.Value})
//result2
   );
//action2
If(dwn_ChangeStatus.Selected.Value = "In Progress",
//condition1
   Set(varCSPSALES, "NO");Patch(
        '[dbo].[t_nsc_trackcode_trans_time_entry_pa]',
         Defaults('[dbo].[t_nsc_trackcode_trans_time_entry_pa]'),
          {
           NSC_ID_Ref: Value(txt_NextId.Text),
           opened_at: DateTimeValue(txt_ReOpenedDate.Text),
           inprogress_or_closed_at: Now()
          }
                                   ),
//result1
    dwn_ChangeStatus.Selected.Value = "Closed" ,
//condition2
    Set(varCSPSALES, "YES");Patch(
          '[dbo].[t_nsc_trackcode_trans_time_entry_pa]',
           Defaults('[dbo].[t_nsc_trackcode_trans_time_entry_pa]'),
           {
            NSC_ID_Ref: Value(txt_NextId.Text),
            opened_at: Now(),
            inprogress_or_closed_at: Now()
            }
                                  ),
//result2
    dwn_ChangeStatus.Selected.Value = "Open",
//condition3
    Set(TheVar,Now());Reset(txt_ReOpenedDate);Set(varCSPSALES, "YES")
//result3
);
//action3

Do you get any error message?

Please check the logic in the formula that I provided.

I did not make any change about updating. So this should not cause the problem of not triggering.

 

 

Best regards,

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

View solution in original post

9 REPLIES 9
Highlighted
Community Support
Community Support

Hi @DAVIDPOWELL ,

Could you tell me the relationship between '[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]' and '[dbo].[t_nsc_trackcode_trans_time_entry_pa]'?

Based on your description, it seems like you want to update two tables in different situations. But I could not quite understand the logic about updating.

This is my understanding:

1)for '[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]' table:

if you select  "Open" in dwn_ChangeStatus , you will check whether there's already items with open status.

If yes, you will get notification. If not, you could update the selected item's status to open.

2)for '[dbo].[t_nsc_trackcode_trans_time_entry_pa]' table:

if you select  "Open" in dwn_ChangeStatus , you will not update table.

if you select  others in dwn_ChangeStatus , you will create a new item.

 

If so, you could try this formula:

Set(lastActivityTime, Now());
If(dwn_ChangeStatus.Selected.Value="Open" ,
   If(!IsBlank(LookUp('[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]', Status = "Open" && lbl_Racfid_ms.Text = ID_Racfid)),
      Notify("A Task is Already Open !", NotificationType.Warning,3000);UpdateContext({varDate: Now()});UpdateContext({varValue: ""}),   
//has item with open status
      Patch('[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]',LookUp( '[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]', NSC_Id = Value(txt_NextId.Text)),
            {
              Status:dwn_ChangeStatus.Selected.Value}
             )                           
//has no item with open status
        );Set(TheVar,Now();Reset(txt_ReOpenedDate);Set(varCSPSALES, "YES"),
   dwn_ChangeStatus.Selected.Value = "In Progress",
   Patch(
        '[dbo].[t_nsc_trackcode_trans_time_entry_pa]',
         Defaults('[dbo].[t_nsc_trackcode_trans_time_entry_pa]'),
         {
          NSC_ID_Ref: Value(txt_NextId.Text),
          opened_at: DateTimeValue(txt_ReOpenedDate.Text),
          inprogress_or_closed_at: Now()
         }
         );Set(varCSPSALES, "NO"),
   dwn_ChangeStatus.Selected.Value = "Closed" ,
   Patch(
         '[dbo].[t_nsc_trackcode_trans_time_entry_pa]',
         Defaults('[dbo].[t_nsc_trackcode_trans_time_entry_pa]'),
          {
           NSC_ID_Ref: Value(txt_NextId.Text),
            opened_at: Now(),
            inprogress_or_closed_at: Now()
           }
         );Set(varCSPSALES, "YES")
)

 

 

 

Best regards,

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

@v-yutliu-msft,

The '[dbo].[t_nsc_trackcode_trans_time_entry_pa]'  performs a time stamp of when they started or ended a task.

the  t_nsc_trackcode_assigned_DataEntry_pa is a distinct master  record for the task itself.

 

2)for '[dbo].[t_nsc_trackcode_trans_time_entry_pa]' table:

"if you select  "Open" in dwn_ChangeStatus , you will not update table".

Answer: Yes, it will update t_nsc_trackcode_trans_time_entry_pa in the "Status" field.

"if you select  others in dwn_ChangeStatus , you will create a new item."...

Answer: I have another routine that actually creates a master record.  the dwn_ChangeStatus, just updates the master record on the Status Field.

The time entry table gets a stamp anytime a "change" Status occurs.

 

my whole problem is after the warning, the other code still executes...

"if you select  "Open" in dwn_ChangeStatus , you will check whether there's already items with open status.

If yes, you will get notification. If not, you could update the selected item's status to open." 

My Answer:  Once the check occurs and its true, yes I already have a task in "Open" issue the warning and exit the function.

I have yet to figure this issue out...

Thanks

for responding.  Dave

Highlighted

Hi @DAVIDPOWELL ,

Do you mean that:
if "!IsBlank(LookUp('[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]', Status = "Open" && lbl_Racfid_ms.Text = ID_Racfid))" this return true,

this action will perform "Notify("A Task is Already Open !", NotificationType.Warning,3000)",

or else these actions will perform

"UpdateContext({varDate: Now()});UpdateContext({varValue: ""});
Patch('[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]',LookUp( '[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]', NSC_Id = Value(txt_NextId.Text)),
{
Status:dwn_ChangeStatus.Selected.Value}
);"?

 

If so, you just need to make a little change on your original formula. Please notice the differences between ";" and ",".

For If statement, the syntax is : If(condition,result1,result2)

If you use ";", this is  used to connect with action.

Try this:

Set(lastActivityTime, Now());
//action1
If(
     !IsBlank(LookUp('[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]', Status = "Open" && lbl_Racfid_ms.Text = ID_Racfid)),
//condition
     Notify("A Task is Already Open !", NotificationType.Warning,3000),
//result1
     UpdateContext({varDate: Now()});UpdateContext({varValue: ""});Patch('[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]',LookUp( '[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]', NSC_Id = Value(txt_NextId.Text)),{Status:dwn_ChangeStatus.Selected.Value})
//result2
   );
//action2
If(dwn_ChangeStatus.Selected.Value = "In Progress",
//condition1
   Set(varCSPSALES, "NO");Patch(
        '[dbo].[t_nsc_trackcode_trans_time_entry_pa]',
         Defaults('[dbo].[t_nsc_trackcode_trans_time_entry_pa]'),
          {
           NSC_ID_Ref: Value(txt_NextId.Text),
           opened_at: DateTimeValue(txt_ReOpenedDate.Text),
           inprogress_or_closed_at: Now()
          }
                                   ),
result1
    dwn_ChangeStatus.Selected.Value = "Closed" ,
//condition2
    Set(varCSPSALES, "YES");Patch(
          '[dbo].[t_nsc_trackcode_trans_time_entry_pa]',
           Defaults('[dbo].[t_nsc_trackcode_trans_time_entry_pa]'),
           {
            NSC_ID_Ref: Value(txt_NextId.Text),
            opened_at: Now(),
            inprogress_or_closed_at: Now()
            }
                                  ),
//result2
    dwn_ChangeStatus.Selected.Value = "Open",
//condition3
    Set(TheVar,Now();Reset(txt_ReOpenedDate);Set(varCSPSALES, "YES")
//result3
);
//action3

 

 

Best regards,

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

@v-yutliu-msft,

"Do you mean that:
if "!IsBlank(LookUp('[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]', Status = "Open" && lbl_Racfid_ms.Text = ID_Racfid))" this return true,"  this action will perform "Notify("A Task is Already Open !", NotificationType.Warning,3000)",

Answer: Yes   I should be able to exit out of the function.

 

fyi( Just like in standard vba coding  "Exit Sub" Instead of relying on  syntax and operators.)

 

Thanks again very much for the replying

 

 

Dave

 

Highlighted

@v-yutliu-msft 

I have all this code under the 

 

"onChange event , of the dropdown, now it will not fire, with your new code.

 

 

dave

Highlighted

Hi @DAVIDPOWELL ,

I forgot one "//" and ")" in my last reply.

Try this:

Set(lastActivityTime, Now());
//action1
If(
     !IsBlank(LookUp('[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]', Status = "Open" && lbl_Racfid_ms.Text = ID_Racfid)),
//condition
     Notify("A Task is Already Open !", NotificationType.Warning,3000),
//result1
     UpdateContext({varDate: Now()});UpdateContext({varValue: ""});Patch('[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]',LookUp( '[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]', NSC_Id = Value(txt_NextId.Text)),{Status:dwn_ChangeStatus.Selected.Value})
//result2
   );
//action2
If(dwn_ChangeStatus.Selected.Value = "In Progress",
//condition1
   Set(varCSPSALES, "NO");Patch(
        '[dbo].[t_nsc_trackcode_trans_time_entry_pa]',
         Defaults('[dbo].[t_nsc_trackcode_trans_time_entry_pa]'),
          {
           NSC_ID_Ref: Value(txt_NextId.Text),
           opened_at: DateTimeValue(txt_ReOpenedDate.Text),
           inprogress_or_closed_at: Now()
          }
                                   ),
//result1
    dwn_ChangeStatus.Selected.Value = "Closed" ,
//condition2
    Set(varCSPSALES, "YES");Patch(
          '[dbo].[t_nsc_trackcode_trans_time_entry_pa]',
           Defaults('[dbo].[t_nsc_trackcode_trans_time_entry_pa]'),
           {
            NSC_ID_Ref: Value(txt_NextId.Text),
            opened_at: Now(),
            inprogress_or_closed_at: Now()
            }
                                  ),
//result2
    dwn_ChangeStatus.Selected.Value = "Open",
//condition3
    Set(TheVar,Now());Reset(txt_ReOpenedDate);Set(varCSPSALES, "YES")
//result3
);
//action3

Do you get any error message?

Please check the logic in the formula that I provided.

I did not make any change about updating. So this should not cause the problem of not triggering.

 

 

Best regards,

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

View solution in original post

Highlighted

@v-yutliu-msft 

Scenario 1:

If all tasks in table are set to "In Progress"  and I change the status to "Open".

Code executes fine.

 

Scenario 2:

When I

try to change the Status back to "In Progress".

I get the error message stating I have a task in "Open" status.(I should not get a notification).

and

The code is not changing the Status to "In Progress"

powerapps open inprogress issue2 .jpg

 

Thanks again for all the help on this !!!!..

 

Dave

 

Highlighted

@v-yutliu-msft,

ok on action2   I added this part.

This working now.. just have to check the backend database and see if the tables a getting patched correctly.

 

 

//action2
If(dwn_ChangeStatus.Selected.Value = "In Progress",
//condition1
   Set(varCSPSALES, "NO");Patch(
        '[dbo].[t_nsc_trackcode_trans_time_entry_pa]',
         Defaults('[dbo].[t_nsc_trackcode_trans_time_entry_pa]'),
          {
           NSC_ID_Ref: Value(txt_NextId.Text),
           opened_at: DateTimeValue(txt_ReOpenedDate.Text),
           inprogress_or_closed_at: Now()
          }
                                   );Patch('[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]',LookUp( '[dbo].[t_nsc_trackcode_assigned_DataEntry_pa]', NSC_Id = Value(txt_NextId.Text)),{Status:dwn_ChangeStatus.Selected.Value}),

Highlighted

Hi @DAVIDPOWELL ,

So do you mean that your issue been solved?

If yes, could you mark my answer as solution?

Thanks!

 

 

Best regards,

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

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

Top Solution Authors
Top Kudoed Authors
Users online (13,244)