cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DAVIDPOWELL
Post Prodigy
Post Prodigy

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

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
v-yutliu-msft
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.

@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

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.

@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

 

@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

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

@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

 

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

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
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

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

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

Top Solution Authors
Top Kudoed Authors
Users online (44,644)