Defining an Error Message

[brzfan2]

I have a layout in my database that records deposits. On the top part I enter the total deposit amount. On the bottom thru a portal I record that deposit broken down into line item posting. I have a total field to sum the line items in the portal. What I would like to do is compare the deposit total to the line item total and if they don't match have an error statement appear. Is this possible to do? If so, where would the "script" go, in the total field or just a general script? If a general script what would trigger it. I don't want to leave the screen and move on it my totals do not match. Is there another way that would be better?

[-Queue-]

Assuming you have some sort of Continue or Post button available on the same screen, attach it to a script which includes

If [thisTable::totalAmount <> Sum(thatTable::Amount)]

Show Custom Dialog ["Your Error Message."]

Halt Script

End If

where thatTable is the relationship used for the portal and Amount is the individual total for each item in the portal.

[coconutt2000]

You could try validating the contents by calculation. I have a feeling that you're going to have to play with this to keep it from becoming a nuisance though when it evaluates the condition and tells you that the fields don't match.

But it should work. It is part of the "Validation" screen in the "Define Database" -> Field "Options" section.

Now, there is another mehod, that doesn't require stopping to click at a dialog box.

Create a calculation field and place it somewhere very obvious. Set it up to check to see if the two fields are equal. If they are, output should be "Valid" or some other kind of confirmation, or just empty if you don't care to see that the output is valid. But if you do, you can use the text formatting functions to color the confirmation text green. Now, if the conditional test returns that the two fields are not equal, you can output a message in the field with a descriptive error, and code it red. Like saying, "Deposit line items are greater than the total deposit", if the line item sum happens to be too high, or "Deposit line items are less than total deposit by " and then include by how much.

I bet there are tons of ways to do what you want, and these are just two of them.

(If you navigate your database using buttons only, one way to ensure that data is always valid is to include a calculation field in every layout that checks the data and sets itself to either "true" or "false" if the data is kosher or not. Then have your navigation scripts check the field before taking the user to the next record or layout. If you do this don't forget to write some kind of escape into the script so that the user can ignore the validation check and continue on. One bonus for the calculated validation field is that you can run a housekeeping script to check for records where the validation fails and report to you, or you can manually run the find for the failures, and the list of possibilities is really limited by imagination.)

[brzfan2]

Thanks for all your great ideas. If I have trouble with the script I will repost. I think I will probably go with the Valid/Invalid field. I have space for it and that should work the best.