exit formula if a condition is true

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

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

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

v-yutliu-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Anonymous ,

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,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@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

Was this reply helpful? Yes No
v-yutliu-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Anonymous ,

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,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@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

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@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

Was this reply helpful? Yes No
Verified answer

v-yutliu-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Anonymous ,

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,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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

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

Dave

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
v-yutliu-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @Anonymous ,

So do you mean that your issue been solved?

If yes, could you mark my answer as solution?

Thanks!

Best regards,

Was this reply helpful? Yes No