Field validation via Selfjoin - Help!

[carlsson]

I have a number of records and I want the sum of them to be "100" (ie percent). If not, the user shall be prompted.

So, I built a selfjoin based on a Key field and built a validation like this:

sum(selfjoin) ≠ 100

But, I cannot in my life get this thing to work. I have used if, case, and other things to get it to work, but it won't.

How should a proper validation on a selfjoin be built?

[Ender]

This would not be an appropriate place for a field validation. Instead use a scripted process.

You could use something like this:

If [ sum(selfjoin::field) < 1 ]

Show Custom Dialog [ "The field should total 1, your current total is " & sum(selfjoin::field) & "." ]

End If

If you can give more specifics about the process, we may be able to come up with a better solution. Since percents are just a fraction of the total, it may be possible to just calculate the Total and the Percent for each record based on the Value, and not bother the user trying to calculate the Percent.

[mr_vodka]

Something doesnt sound right. What happens to the first record then? If the user doesnt enter the value of 100 then the user would be prompted.

[carlsson]

Thanks for the answers.

A script would definitely work, but that is no suitable for this solution.

Background:

This is a sales-system.

For this description we have two tables, "Order" and "Commission". The person(s) who have sold the Order is entered in the Commisson table.

Sometimes there are more than one seller on the same order. In that case the sellers shall split on the commission.

The total commission must always be 100%, and it's usually splitted 50/50, but not always. Sometimes it can be 20/80 or any other percent.

To garantuee that the total percent always will be "100" I want some kind of validation in the Commission::Percent field.

There must be a way to solve this without the need of a script!?

Tia,

/andreas