Data validation in portals - problems

[dbanbury]

Hi guys and gals, I have a question relating to data verification in portal rows. I have a relationship of invoice -> invoice credit -> receipt.

The receipt table holds the details of a payment received by us from a customer. The amount of the customer payment can then be divided up into as many invoice credit records as desired. An invoice credit may only be attached to one invoice, but an invoice may have any number of credits attached. Similarly, any number of credits are allowed per receipt - so long as the numbers add up.

The invoice credits, not the receipts, affect the final invoice balance, which is a value copied from a calculated field to a non-calcualted field in a script at credit/invoice modifcation time to speed up non-zero balance invoice searches.

The problem is that this all works wonderfully until I don't commit each invoice credit record individually in the portal row.

A calculation validates that the amount of the credit isn't more than the remaining unallocated amount from the receipt used to fund the credit. Entering one record at a time and then comitting it, this verification works fine. But I can keep creating invoice credits in excess of the unallocated balance of the funding receipt if I don't commit the credit records one after another.

Does anyone know a way around this, another calculation or function I might use, or do I just have to write more scripts to make sure the data is valid? Help much apprecited. Best wishes, Dave.

Case ( Count ( Receipt::ID ) ≠ 0 and Amount > Receipt::AmountOfPaymentUNAllocated ; 0 ;

Amount = 0 ; 0 ;

Amount > Invoice::Balance ; 0 ; 1

)

[dbanbury]

Sorry, for clarity, I should also add that Receipt::AmountOfPaymentUNAllocated is a calculated field and it's the fact that this calculation, if there's a batch committal, doesn't seem to be performed after each portal record is committed that's leading to the failure of the data verification. dave.

Edited June 13, 2006 by Guest

[John Mark Osborne]

I have had the same experience when validating related records displaying in a portal using a calculation. Auto-enter calculations work just fine because they activate at a field level when one of the referenced fields is modified. Some validation features do work at a field level such as validating for a range of values. However, calculation validations work at a record level and will validate when the parent record containing the portal is committed.

I have used auto-enter calculation on occassion to warn users by changing the color of the text entered. Other times, I have used scripts to add related records to a portal so I can control the validation. Hopefully, others will offer their workarounds so you'll have a lot of options.

[dbanbury]

Thank you for the information. I think I'm getting used to some of the quirks of the software after developing with it for a few months. I'd also tried setting up a calculated validation on the invoice record itself using the balance field, to make sure it's never a negative number. But what happens then is that the portal records are still committed with the unwanted overallocation and then the balance field fails verification. But now I can't change any portal records because FM insists I correct the balance field, which isn't present on the layout and shouldn't be modified by a user anyway.

Of course with a script I can stop the user from exiting the edit layout and committing bad data if the numbers aren't adding up, but it would be nice, since all the fundamentals are already there in the database design, not to have to, to just be able to check whether there was a data validation error before attempting to close the edit window.

So it seems to me one way around this problem is to have an auto-enter calculation which overwrites existing data, set up the validation for it, and then set some data in a field for the purpose to force the auto calc field to calculate and which should then attempt to verify. If verification fails, that can be tested for and if true keep the user in a loop until they've deleted the portal lines that are causing the validation error from the perspective of the invoice.

Is that about the shape of it? Thanks again for your help. Regards, dave.

[Søren Dyhr]

I have some troubles in following the description, because it's termed completely different in danish, however does it seem like the validation is an attempt to prevent selling more than the clients credit allow.

If it so can it be solved via the above RG, because if the itemline we work in via the portal is the one made red, could following native validation be instated:

Sum ( ItemLines 2::lineTotal ) < 30 and not IsEmpty ( Invoice::ToClient )

The idea is then to make one-line invoices with negative a negative sum for each payment, or make a ranecheck with several of the itemlines again with negative figures.

Try to read the calc' above it says - the sum of all itemlines issued to this client including the present in this invoice can't go higher than 29, further more do we need to ensure our self that we get every invoice issued previously to this client by checking if a customerID is present in the invoice.

The invoice Sum( is not yet made but should be in the Invoice...

--sd