Validation on Form Submission

(0) Share

Report

Posted on by simms7400

829

Hi Folks -

I was wondering the best way to go about establishing a validation in PowerApps? Allow me to give the requirements.

I have a table called "rdInvestments" and for purposes of this dicussion, here are the 3 columns from my table and sample data:

AssetID is the PARENT to InvestmentID - think of tree form if this was a hierarchy in a system somewhere.

Now, the Column "PR_Lead_indcation" is where the action happens. Ultimately, there can be ONLY 1 value of "TRUE" for "PR_Lead_Indication" for sibling data elements. And obviously, to determine siblings, you can use the "AssetID" column.

So in the above example, all of the those PFI data elements are siblings with 1 like parent. I need to enable a validation where on both new requests and edit requests, if someone tries to use a TRUE value when it already exists in it's sibliing group, to not allow it.

I'm not sure if this can be done on the actual field itself or on the Submit button. Ultiately, I have a validation setup on my target financial application it gets imported into, however I'd prefer to catch the issue in the begining rather than getting a failure to import when I go to load this..Then, I need ot backtrack and then hunt down the end user to get clarity. Enabling the validation on the PowerApps side would save lots of time!

Please let me know your thoughts!

Thank you!

Categories:

Building Power Apps

All responses (12)

Answers (1)

Sort by

Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@simms7400

Yes, this is no problem to do in PowerApps.

I would consider simply making the the option to set True when one of the other items is set to True to be not available to the user (rather than allowing them to set it and then giving them some validation error or notification).

So, in that case, disabling the control that you use to set true or false would seem the obvious target.

On the DisplayMode property of that control (I'm assuming it's probably a toggle control) - set the property to something similiar to the following:

If(
CountRows(
Filter(yourDataSource, AssetID=ThisItem.AssetID && !(PR_Lead_Indication=false))
) >=1, Disabled,
Edit)

Couple things here. 1 - yourDataSource -replace with your datasource (obviously). 2 - I believe this is a SharePoint list?? If so, the double negative filter will resolve an issue with a problem on true/false, yes/no columns.

So, this will enable or disable the ability for a user to set true on a record if there is already a true in the AssetID group of records.

I hope this is clear and helpful.
simms7400 829 on at

Like
a
(0)

Report
Copy link

Link copied!
@RandyHayes

Hi Randy, thank you!

"PR_Lead_Indication" is a Choice field with "TRUE" or "FALSE" so I needed to amend the formla like such:
If( CountRows( Filter(rdInvestments, CompoundCode=ThisItem.CompoundCode && !(DataCardValue47.Selected.Value="FALSE")) ) >=1, Disabled, Edit)
It validates, but getting delegation warning on CountRows which is normal.
However, I'm still seeing "TRUE" in the Choice selection of my "PR_Lead_Indication" field. I'll keep poking around, thanks!

PS. AssetID = CompoundID.
simms7400 829 on at

Like
a
(0)

Report
Copy link

Link copied!
@RandyHayes Got it!

Needed to change the property to:

PR_Lead_Indication.Value
Thank you so much, this is working great!
simms7400 829 on at

Like
a
(0)

Report
Copy link

Link copied!

@RandyHayes

Actually Randy, using the screen shot of my table above, if I check the data element sibling that is TRUE, I would expect that to be enabled so I can change the drop down to false is need be, but it's not. It's also disabled.

Let me play around with the formula.
simms7400 829 on at

Like
a
(0)

Report
Copy link

Link copied!
This does the trick!

If( CountRows( Filter(rdInvestments, CompoundCode=ThisItem.CompoundCode && !(PR_Lead_Indication.Value="FALSE")) ) >=1 && PR_Lead_Indication.Value="FALSE", Disabled, Edit )
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@simms7400

Perfect!! Was just going to reply with that suggestion.

You're on a roll!
simms7400 829 on at

Like
a
(0)

Report
Copy link

Link copied!

@RandyHayes

Hi Randy - sorry to bring ressurect this, but had a quick question!

This solution is working great for Edit requests, end users are very happy with this approach. However, I"m running into an issue with new requests when someone selects "TRUE" and then when i go to process in my financial system, I get an import error because there is sometimes a data element with TRUE already. The validation doesn't seem to fire for new requests and its because that "TRUE" value is not yet available in the TABLE where the formula is checking, obviously.

Is there a way to incorporate this into new requests? Ultimately, using the conditions mentioned in the original post up top, I need to add in a peice to the syntax where if its a new request and a TRUE value is already selected on a sibliing to the new data element, then either grey out the box or remove the TRUE selection all together.
Is that possible?

Thanks!
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@simms7400

I think we can get where you want, but I have some questions:

However, I"m running into an issue with new requests when someone selects "TRUE" and then when i go to process in my financial system, I get an import error because there is sometimes a data element with TRUE already. The validation doesn't seem to fire for new requests and its because that "TRUE" value is not yet available in the TABLE where the formula is checking, obviously.

So...what exactly does this mean? What is not in the table already? Are you saying that none of the sibling records are there in the table? And to be clear, when you say "new" are you stating that a new AssetID is being created, or that a new InvestmentID related to an AssetID is being created?

With the formula you have now, if there is no True value in the table, then the option is available (DisplayMode.Edit). It would seem to me when creating a new AssetID, there would be no True value, so the control to allow the user to set the True value would be available. As well, when a new InvestmentID is being created, any other sibling records would already be there, and thus the formula should work just as well.

Is there a way to incorporate this into new requests? Ultimately, using the conditions mentioned in the original post up top, I need to add in a peice to the syntax where if its a new request and a TRUE value is already selected on a sibliing to the new data element, then either grey out the box or remove the TRUE selection all together.

This is what the formula *should* be doing - regardless of Edit or New.
simms7400 829 on at

Like
a
(0)

Report
Copy link

Link copied!
@RandyHayes

Here I go again, confusing you, I'm sorry! LOL

Let me walk through it. To recap, here is my formula:
If( CountRows( Filter(rdInvestments, CompoundCode=ThisItem.CompoundCode && !(PR_Lead_Indication.Value="FALSE")) ) >=1 && PR_Lead_Indication.Value="FALSE", Disabled, Edit )
For this example, let us use this dataset for the discussion:

Now, if I edit any of the PFI data element above with a value of "FALSE", the "Lead Indication" is greyed out - which is expected:
If I edit the PFI with the TRUE value, its editable obviously, which is expected.

Now, let's move on to NEW requests. If I inirtiate a NEW request for a PFI to be a sibling to the data set we are talking about, the field is Editable which shouldn't be the case:

You'll notice it says false as I have this property display a default value ("FALSE") for new requests but that shouldn't matter.

I would expect this field to be greyed out as well. Essentially, "Lead Indication" field should be greyed out for ALL new requests unless a value of TRUE is not found amongst the sibling list. Does that make sense? Thank you, Randy let me know if you need any other detail.
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@simms7400

Ah yes...the pictures are worth a thousand words!!

So, I believe here is the issue you are seeing. When you are creating a new item, ThisItem is blank for the CompoundCode.

So, what you need to do is grab the value that is in the AssetID and not the ThisItem (I think this is the Asset Alias field on your form?).

So, let's say that's a text control...then you would alter the formula to this:

If( CountRows( Filter(rdInvestments, CompoundCode=DataCardValuexx.Text && !(PR_Lead_Indication.Value="FALSE")) ) >=1 && PR_Lead_Indication.Value="FALSE", Disabled, Edit )

If it's a drop-down or some other place that the current CompoundCode is coming from, then just substitute that in the above formula.

This should work equally well on new and edits.