cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gjakova
Helper III
Helper III

Data validation formulas

Hi there, I need to solve the following two things but I'm stuck on it...

1)
I have three DataCardValues and when I submit the form, the combination of these three values needs to be unique within my SQL database.

Example:
DataCardValue "Documentnumber" = 309
DataCardValue "ContractType" = External
DataCardValue "Amount" = 80
So if there is already a row in my SQL database which has 309, External and 80 within the same row, they are NOT allowed to submit the form (since it already exists). How can I solve this within PowerApps?

 

2)

I have two DataCardValues, one is called "Our Share", the other is called "Contract Value".

I want to make give a warning when Our Share is 80% of the value from Contract Value.

Example:

If the contract value is 100 dollar and our share is 79 dollar, the user won't get a warning (example, red border or something else). But if the contract value is 100 dollar and our share is 80 dollar, the user will get a warning that Our Share is 80% of the value of the contract.

 

I think it is easy to do, but I can't wrap my head around it. Someone who has an idea? I'm doing this inside a Form.

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@Gjakova 

For 1:

Set the DisplayMode property of your submit button to:

    If(LookUp(yourDataSource, yourUniqueColumn = DocumentNumberText & ContractTypeText & AmountText, true), Disabled, Edit)

 

For 2:

   Consider your red border...set the BorderThickness to 1 (or more) and set the BorderColor property to:

If(OurShareValue >= (ContractValue * .8), Red, Transparent)

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

7 REPLIES 7
RandyHayes
Super User
Super User

@Gjakova 

For 1:

Set the DisplayMode property of your submit button to:

    If(LookUp(yourDataSource, yourUniqueColumn = DocumentNumberText & ContractTypeText & AmountText, true), Disabled, Edit)

 

For 2:

   Consider your red border...set the BorderThickness to 1 (or more) and set the BorderColor property to:

If(OurShareValue >= (ContractValue * .8), Red, Transparent)

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

Hey @RandyHayes I can't test the 1st one yet, but I guess it seems logical. Only problem is that it is there is not a unique column in SQL. So in my database I have three columns just like in Power Apps (three DataCardValue boxes where users can type in the amount or text). I just need to check if for example: if row #207 contains the exact same values in SQL as the one I inserted in Power Apps, that it should not let PowerApps submit the data of my form to SQL.

I tried your second one but it says: Invalid argument type, expecting a number value

Probably easy to solve, but I'm still finding my way in PowerApps, sorry.

RandyHayes
Super User
Super User

@Gjakova 

Consider that the formula provided were theoretical as you did not supply in your original post, the exact names and types of controls you have.

So for the first one:

    If(LookUp(yourDataSource, yourUniqueColumn = DocumentNumberText & ContractTypeText & AmountText, true), Disabled, Edit)

The DocumentNumberText for example, was meant to be replaced with the control that has the actual document number text in it - i.e. DataCardValuex.Text

 

But I believe you are now stating that there are three specific columns in your SQL that contain these values rather than one that is a concatenated value.

If so, then simply break it out to the appropriate column names.

i.e.

If(
    LookUp(yourDataSource, 
        yourDocNumColumn = DocumentNumberText && 
        yourContractTypeColumn = ContractTypeText &&
        yourAmountColumn = AmountText, 
        true
    ), Disabled, Edit
)

Again, replace the "DocumentNumberText" etc in the above with the ACTUAL control names (with .Text) that contain these values.

 

For 2:

This is a good formula, but you need to compare values to values (which is why you are getting invalid argument).  Again, I have no idea what your control names are or where the value is coming from, so the formula was theoretical.  

What is the exact formula you have that is giving you the invalid argument type?

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Thanks @RandyHayes can't wait to try the first one.
Regarding #2:
Here is a picture (I anonymized it a bit) on how the app looks:

PAquestion.png

So DataCardValue13 = Our Share and DataCardValue14 = Contract Value. I don't have data yet, but once someone types in 80 at Our Share and 100 at Contract Value, they should get the error that it has exceeded 80%.

RandyHayes
Super User
Super User

@Gjakova 

Ah yes...here is the thing...DataCardValue13 is not a value, it is a control.  You need to reference the property of that control that you want to use.

So, if those are both text controls, then your formula would be:

    If(Value(DataCardValue.Text) >= (Value(DataCardValue14.Text) * .8), Red, Transparent)

 

Two things there...1) you must reference the property that contains the information you want and 2) you must convert it from text to value with the Value function.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Thanks! That seemed to solve the problem. Now I know for future things how this works. Have a great weekend!

RandyHayes
Super User
Super User

@Gjakova 

Very good!  You have a great weekend as well!

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

secondImage

Power Apps Community Call

Please join us on Wednesday, October 20th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,468)