How can I trigger an error message for negative quantity totals

[tcanelli (Tammi Canelli)]

I am trying to make sure quantities assigned don’t exceed the current inventory.

Have 2 tables:
Donations List (Parent)
Donations Assignments (Child)

The Qty Given and Current Qty columns (image) are virtual columns in the Donations List table. The actual data entry for assignments is happening in the Donations Assignments table.

I tried using the data validity to set the [Current Qty] >-1 but this doesn’t work. I’m guessing because the form being completed is using a different table. So I’m not sure what expression to use in the assignments table that will do the math to see if there is sufficient quantity available in the parent table - donations list.

[WillowMobileSystems (John Baer)]

Data Validity checks are only performed on Forms when data is being entered.

The best option is to prevent the negative values by performing the Data Validity check where “Qty Given” is entered. In this columns’ Form entry, you would use the Data Validity expression:

[Qty Given] > [Qty]

---

If it’s the case that the Donations List values are NOT entered in the app but some external method, then either you need to try to prevent negative values at that external source OR flag the erroneous values in the app. For example, you could use Format Rules to highlight the rows that have negative values. Or you could use a Slice and create another view listing the “bad” records.

Doe this help?

[tcanelli (Tammi Canelli)]

I’m using formatting rules right now to flag the negatives.

The data validity just isn’t working. Is it because the total qty is a virtual column?

[WillowMobileSystems (John Baer)]

Yes, the Data Validity is meant to verify ENTERED values. Virtual Columns are determined through App Formulas and should be using already validated values.

Is “Qty Given” entered in the app? Or is supplied some other way?

[tcanelli (Tammi Canelli)]

Qty Given is entered in the assignment sheet (child tbl), whereas the original qty is entered in the donation list tbl (parent).

I did create a virtual column in the assignment sheet to show current qty/inventory. But that relies on the person completing the form to pay attention - may not happen.

In the parent table there is a virtual column to calculate the current qty.

[WillowMobileSystems (John Baer)]

I’ll assume you have a Ref to the Parent record then?

If so, then in the Data Validity property for “Qty Given” you’ll want to use an expression like this:

[_THIS] <= [Parent Ref Column].[Qty]

I actually gave an incorrect formula before. You want the formula that specifies what values are VALID.

(As a side note, I used [_THIS] instead of the column name but you could use either in this instance.)

---

Lastly, From an app usage perspective, it is always preferred to prevent the entry of invalid values when possible. It forces clean data in the app and prevents issues later. So I would stick with getting the Data Validity to work - there is a way - it will be better for you and the app in the long run.

[tcanelli (Tammi Canelli)]

That did it! Thank you!!!

I agree - Data Validity is best - when I can figure it out