Read Contents of second related record

[LaRetta]

I have a Receipts table joined to Invoices on the Invoice Outstanding amount. When a payment is received (Net terms) and the check entered, it fills the Invoices portal with any unpaid Invoices which match the amount. User has other critera they can filter(using globals) which include -All- such as specifying a Chain (who pays 20 different store's invoices at a time). User then selects the proper invoice to pay. But Receipts also receives imports of payments from other sources (credit cards, COD and more). Most of these payments can be paid automatically (termed Auto-Pay) if the Receipt invoice number matches an Invoice AND the amount matches the outstanding Invoice amount. I want to use the same table occurrence group to auto-pay these payments instead of creating an entirely new TOG but the existing process CAN'T include the Invoice Number in the join because most Net payments don't include the invoice number AND even imported payments many times have incorrect invoice numbers because they are manually entered by people.

My Auto-Pay needs to include the invoice number in the test. Script loops through Receipts and tests for the following before it is considered a perfect match:

Receipts::Amount = Invoices::Balance

AND

Receipts::PayMethod = Invoices::PayMethod

AND

Receipts::InvoiceNumber = Invoices::InvoiceNumber

But script (while looping through payments in Receipts) can only *see* the first invoice and there can be many matches in that portal. If everything matches on the first related invoice, it pays it but if not it skips that payment and moves to the next one. We have 600 payments which will not auto-pay because the 'first' invoice (in this TOG) is NOT applicable. I need script to look at ALL related invoices.

I've tried using GetNthRecord() but I can't make it work right. Script should look at first related and see if InvoiceNumber, Outstanding Amount and PayMethod matches. If so, grab the InvoiceNumber of that record; if not, look at second related record, repeat the match test and grab THAT InvoiceNumber if perfect match (and run the Apply Payment script) and so on. By grab the invoice number, it needs to set a global Receipts::gInvoiceNumber which the Apply Payment process uses. I'm getting twisted in the loop within the loop and using GetNthRecord.

Is there any help for me or am I a lost cause?

LaRetta

[LaRetta]

By the way, there is a bit more to the script test than the three criteria I listed. It also makes sure they are within applicable date range (within 2 days for credit cards, within 14 days for COD etc) and other tests. This is in case the invoice number was entered wrong (and that invoice is also same PayMethod and Balance), it won't just pay them. I only gave 3 criteria to keep it simpler for discussion.

Edited December 2, 2006 by Guest

[Genx]

MOST of the above just went in one eye and out the other but... I think the below may solve your actual problem.

Script Section

If[ not IsEmpty( GetNthRecord(MyTable::MyField) ; 2 )]

Set Variable[ $result ; MyRecursiveCF( valueToTestFor ; MyTable::MyField ; 1 ) ]

Else

SetVariable[ $result ; MyTable::MyField = valueToTestFor]

End If

If[$result]

Perform Script[Apply Payment]

#Note that: GetNthRecord( MyTable::MyField ; $result ) returns your valid matching record ID so do with that what you have to.

End If

MyRecursiveCF( valuetotestfor ; field ; n )

Let([

x = valuetotestfor ;

y = GetNthRecord( field ; n );

z = GetNthRecord( field ; n + 1 )

];

Case( x = y ; n ; IsEmpty(z) ; 0 ; MyRecursiveCF( valuetotestfor ; field ; n + 1 ) )

)

Edited December 2, 2006 by Guest

[LaRetta]

Hi Genx!

Thanks for the idea. It was scripting the logic of exiting the loops that was hanging me up (please see attached test file). I needed to test when to exit the GetNthRecord portion of the loop but I didn't take into account that, as invoices in the portal were paid, it could change the related count.

This appears to work without custom function. This isn't exactly as I'm structured; I took shortcuts to show the process. The way I had it, it would continually loop because there might be related records NOT applied at all and script kept seeing them and trying. I needed (I think) a counter to tell me when I had gone through them all only once. I used Exit Loop If [ $CountRelated = 0 or not Count ( Invoices::InvoiceNo ) ]. By looping through the GetNthRecord number, we shouldn't need to recurse at all but I'm unsure on the speed comparison. I shall compare them.

One really needs Developer to watch the process using debug to see how it starts at the bottom (last related) and works up. If anyone knows how it might break or can help fine-tune it, please tell me! This is the first time I've used GetNthRecord() to walk each child record 'looking' for what I want. I considered using portal row as well but this script won't be ON a layout with the portal (and I wasn't sure it would grab the correct one without a portal). Does Portal Row number just mean the related record number? Anyway, I'm unsure if this is the best method. The whole thing feels klunky but, with inconsistent data coming in, I don't know how else to search for matches in the same TOG.

LaRetta

AutoPay.zip

[Genx]

Oh right, looping through the child records... Sorry, I've been dealing with CF's a lot lately and you seem to get one thing stuck in your head for a while.

Not sure why you start backwards but it's all good I can't see any flaw in the logic anyway :B

[LaRetta]

I'm not sure why I started at the last related either, Genx, except I wasn't clear on WHEN the Count(Invoices::InvoiceNumber) would change (depending upon commit). By counting backwards, zero is always zero.

And I didn't want to have to adjust the count depending upon whether an Invoice paid or not; it all feels funky, in my opinion. But then my work always feels funky to me.

LaRetta

[Genx]

Funky is fine, all though counting backwards is a bit ... backwards, it actually seems clearer, as you say, the exit condition is always going to be when $$count = 0.

[Søren Dyhr]

This is in case the invoice number was entered wrong (and that invoice is also same PayMethod and Balance)

But... Isn't it just as likely that payments covers more than one invoice?? I'm trying to understand why you can't make an even fatter chord (multicriteria relation) making this:

Receipts::Amount = Invoices::Balance

AND

Receipts::PayMethod = Invoices::PayMethod

AND

Receipts::InvoiceNumber = Invoices::InvoiceNumber

...the relational criteria, instead of looping - but it might be too tight?

Next thing I came to think of was how to optimize the scripting further by getting rid of the GetNthRecord( stuff... Take a look at the attached .jpg, if the relational stuff is done like this, will it always get the first related provided all paid invoices are stored as a multiline key - will the un-equal relation sift the irrelevant away.

There will be a sharing problem in a multiuser environment with the global as such, but making it a global calc'field making a List( over the ID's from say a carthesian related table ...well whatever...!

I think one should prioritize to get as much of the selections done via auxillary relations, then as next step seek shelter under some scripting and CF'ing later, and not as it seems to be done here the other way around ...I know it sometimes can feel hard to hessitate from scripting ...but eventhough Mike (Ender) here argues against the excessive use of CF's in this thread:

http://www.clevelandconsulting.com/support/viewtopic.php?p=1772&highlight=criteria#1772

...has it some bearing here as well!

--sd

[LaRetta]

Soren, thank you for responding! I've been considering your ideas. Yes, I prefer using relationship over script as well and my graph shows it. Ha ha. Straight Invoice Number in the multi-join is simply too tight. User has several globals (for filtering and finding). Then calculated keys make the match according to what is entered. Right-side keys only produce on unpaid invoices and produce multilines like:

cKeyExplode (exploded name and address and -All-)

cKeyAmounts (orig invoice amount, balance owing and -All-)

cChain (ChainID and -All-. One chain can pay 20 stores' invoices)

cKeySearch (InvoiceNumber, POnumber, VendorID, OnlineID and -All-)

PayMethod

ShipDate

Globals are gName, gChain, gFrom, gTo, gPayMethod (checkbox) and gAltAmount (allows for partial payment search - one check can pay multiple invoices and multiple stores' invoices). But the left-side keys are calculations which look at the global values and look at the Receipt Type (Net, COD, Credit Card) and date entered and produces the real keys depending upon the complete evaluation.

I realized that I can include Receipts::InvoiceNumber in my cSearch calc (which matches to cKeySearch) to isolate to one invoice number. I just added test to left-key calc - if InvoiceNumber not empty and gKeySearch empty. The calculated date keys will restrict the date range automatically depending upon the ReceiptType. Results? New import of 650 payments, 647 produced only ONE portal match and were paid correctly. Three wouldn't pay; two because two results were in portal. They incorrectly matched because the POnumber (on one invoice) was the same as a different InvoiceNumber (both online orders, same day, same pay method) in both instances. One had an invalid invoice number. I suppose I could isolate the invoice number in the right key to InvoiceNumber and -All- and maybe I should have originally. But when manually entering checks, they know who the check is from and which invoice is being paid and just click it so I included it in the search/filter option instead so they wouldn't have to also specify the invoice number.

Overall, I consider it a success, ie, filtering the relationship down to one entry instead of looping the related. Bottom line, unless I only produce ONE match, I don't want to match anyway - why loop them? Thanks for getting me back on track!

LaRetta

[Søren Dyhr]

If I were in your shoes, would I probably close as many of the oldest invoices by each payment instead of direct matching, because there might be another ROI when you can avoid your staff dealing with interrests calculated on overdues and the interests calculated on these as well. While this recursivity is easy peicy for a computer to handle, is it causing a lot of hardly productive phonecalls arguing what the balance actually is!

I just recalled a long line of calls made to former ISP'er where we somewhere along the line agreed about a sum that would free me from any commitments with them. But somehow didn't their system have an option to mark a payment as final settlement, getting new emplyee's on the line not getting the entire story from the one dealing with me when we seemed to settle our thing.

Neither I or they made any money during these bysanitne excursions into a Kafka'ish maze - I couldn't help feeling the eagerness both me and the ISP threw into this, candidates for a "Get a life" yell!

--sd