Data validation formulas

[Gjakova]

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.

[RandyHayes · Accepted solution]

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

[Gjakova]

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]

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

[Gjakova]

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:

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]

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

[Gjakova]

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

[RandyHayes]

@Gjakova 

Very good!  You have a great weekend as well!