Updating of parent record and checking if value is valid before updating

[Rodney_Reid]

Hi Guys,

A short summary of app:

Invoice lines get captured(INVOICE) - the customer makes one bulk payment(PAYMENT) and payment then needs to be split up between invoice lines(P.ALLOCATIONS). Payments can be made either in full or split payments per line

The issue I'm sitting with is that I need to check if the payment that gets allocated does not exceed the OUTSTANDING BALANCE. Sounds simple enough if one thinks that you could use the Valid_if function to determine if this is so - but the Valid_if function only updates once you Resync and not immediately when data is entered. So I tried to bypass this using a virtual column and using that to test the Valid_if function - but still Valid_if function doesn't work and it doesn't give an error when the data entered is incorrect and it captures the incorrect data in the virtual columns. I tried using the Valid_if function in both the INVOICE and PAYMENT tables at various stages of the process but just did not get it right

So my brains are fried and I have no further ideas and need a fresh perspective on this.

This is my current table structure - I left out the nonapplicable columns and tables

TABLE: INVOICE
RECORD NUMBER -TEXT - UNIQUEID()
CUSTOMER - TEXT - REF TO CUSTOMER TABLE
INVOICE - TEXT - MANUAL HUMAN INPUT
TOTAL.SELLING - NUMBER - UNRELATED FORMULATION
PAID - NUMBER - SUM(SELECT(P.allocations[Ammount],[Invoice Allocated] = [_THISROW].[Record Number]))
OUTSTANDING - NUMBER - [Total.Selling]-[Paid.virt]
RELATED P.ALLOCATIONS - LIST - REF_ROWS("P.Allocations", "Invoice Allocated")
PAID.VIRT - VIRTUAL COLUMN NUMBER - SUM(SELECT(P.allocations[Ammount],[Invoice Allocated] = [_THISROW].[Record Number]))
OUTS.VIRT - VIRTUAL COLUMN NUMBER - [Total.Selling]-[Paid.Virt]

TABLE: PAYMENTS
RECORD NUMBER - TEXT UNIQUEID()
CUSTOMER - TEXT - REF TO CUSTOMER TABLE
PAYMENT - NUMBER - MANUAL INPUT

TABLE: P.ALLOCATIONS
RECORD NUMBER - TEXT UNIQUEID()
PAYMENT.REC - REF TO PAYMENTS TABLE
INVOICE ALLOCATED - REF TO INVOICE TABLE
AMMOUNT - NUMBER - MANUAL INPUT

[baba_sawane]

Hi Rodney,

<< but the Valid_if function only updates once you Resync and not immediately when data is entered >>

I doubt this is true. What condition are you using for the valid_if field ?

[Rodney_Reid]

First one that came to mind

Valid_If

([Outstanding]-[Paid])>=0  - but then I realized that the paid column would only update once the data table P.Allocations syncs so I tried using a PAID Virtual column.

So I tried 

([Outstanding]-[Paid.Virt])>=0  

Tried different references that and [_thisrow] etc... but nothing... I've tried adding a Virtual column to P.Allocations which then gets referenced in the main table to check 

[Suvrutt_Gurjar]

You have really made a nice post of just sufficient details to narrate your issue. However, somehow it is still not exactly clear in which column and which table you are using the valid_if expression.

However to start with, how about a valid_if expression of 

[Outstanding]-([Paid]+[_THIS])>=0       assuming you are using valid_if in a column where the latest payment entry is made.

[Rodney_Reid]

Apologies Gents - my initial thought was to put it in the PAID.VIRT column seeing that there was the first interaction between the two tables relating to the payment allocation. Then I moved it to the OUTS.VIRT thinking that might I might be prematurely checking if the function is getting the correct data.

[Outstanding]-([Paid]+[_THIS])>=0       assuming you are using valid_if in a column where the latest payment entry is made. - I'll have a look into this but the possible problem with this is that payment entry is made in the Payment allocation table and not in the Invoice table where the valid_if needs to take place. I'm thinking possible ref lookup in the Payment Allocation table ?

[Suvrutt_Gurjar · Accepted solution]

---

@Rodney_Reid wrote:

but the possible problem with this is that payment entry is made in the Payment allocation table and not in the Invoice table where the valid_if needs to take place

---

Any specific reason you mention thevalid_if needs to take place in the invoice table? If Payment entry is made in the Payments allocation table, then I believe that it is best to stop any excess payment being added at the entry point itself.

If valid_if at the Payment Allocation table is fine, then you could possibly have a valid_if in the payment entry column in the Payment Allocation table itself.  An expression something like below that pulls necessary column values from the Invoices table by dereferencing expressions.

[INVOICE ALLOCATED].[Outstanding]- ([INVOICE ALLOCATED].[Paid] +[_THIS])

[Rodney_Reid]

Suvrutt to the rescue once more!

The suggestion put me on the right path and gave me a fresh view of the problem. The solution was to place the expression in the P.ALLOCATIONS Ammount field with a few tweaks:
[INVOICE ALLOCATED].[Total.Selling]>=([INVOICE ALLOCATED].[Paid.Virt]+[_THIS])

Thanks Suvrutt!

[Suvrutt_Gurjar]

You are welcome. Good to know the solution works. Thank you for posting the final solution with tweaks you did- it will help anyone reading the thread in future.