Good day to you all,

We were indeed very pleased to see some of the new functions in Gridview 9.0C especially those that let us emulate a SQL join.

I have been working with the GetLinkedValue() call and have it working but am at a loss as to which tables to use.

I am trying to create a view that would include invoices data and the corresponding payment data.

Fields such as Inv#, InvDate, InvAmount, PaymentDate, PaymentAmount etc...

I understand the limitation that only the first reference to a matched payment will appear and ponder if anyone has been down this path before and has come up with something reflective of the underlying data.

Any help / ideas would be appreciated!

Thank you and,

Cheers, JK

You would be better off getting the data from SalesAnalysis. It has a function that marks records with the date that the invoice was fully paid.

See
..\Softrak\System
for DataDictionaryExporter.exe and DataDictionaryExporter.chm

This utility sends all the tables for an application to an Excel spreadsheet where you can easily see the data elements from each table.

A great help for "what is where"

Good morning Andrew,

I have downloaded SalesAnalysis and have started working my way through the manual and gleam that SalesAnalysis will only work if the invoices were created in Adagio invoices and a flag set therein (actually the invoice files can be scanned to build the required files).

This is not the case for us - in most cases we import data directly into Adagio Receivables and as such am I correct in saying that this is not a path forward for us at this time?

Thank you and,

Cheers, JK

Hi John

You are correct that the invoices have to be created in either Invoices or OrderEntry.

This may sound surprising to you, but I have a client who implemented Invoices (and imports the invoices directly into that program) solely to be able to feed SalesAnalysis, in order to get the report similar to what you are looking for. They don't print their invoices out of Adagio.

I think you are going to have a hard time using the AR Transaction and Matching files in GridView to get what you want, but it probably can be done.

Steve

Hi Steve,

Thank you very much for confirming what I understood - you have saved me a considerable effort.

I have dumped the data dict down and have started poking around (iow: back where I was working before) but at least now I have a map of what is where.

If anyone is familiar with the data flow through these tables and could shed some light on what to join with what I could surely take it from there using the tools provided.

Again, I am trying to create a view that will show the first (or last preferably) payment date on an invoice bearing in mind that we are only using Adagio A/R.

Going with the last date will better reflect a customer's payment habits as using the first date would be misleading in the event numerous entries are done on the one invoice.

I have tried using the document number but that did not work and I know I've got the GetLinkedValue() call correct because I can relate the item to itself in another view.

Recapping: Is there a field which relates the invoice payment data (CA) to the original invoicing data (IN) and if there is such a key what general relations need to be used (high level only)..

Thanks in advance and,

Cheers, JK

The Transaction Matching file in AR contains a list of invoices and the dates they were paid (or credited or adjusted or applied by any document type). This should be an interesting file to look at, as Steve alluded to in a previous posting.

You might also want to watch the GridView 9.0C webinar, as one of the workspaces demonstrated was linking the AR transactions and matching file together in a very similar way to what you want - it's around the 28 minute mark of the video.

Hi John,

This is exactly the date recorded in SalesAnalysis. It might be easier to import your transactions through Invoices as Steve suggested. You'd also be able to get customer by product (or service) analysis as well.

But perhaps you'll manage to get the information for your needs from the Match file.

Good day to you and thank you!

I have been working in that file and did watch the video but did not put two and 2 together <grin>.

I will go and watch it again and post up as I progress in case someone else is trying to do the same thing.

Thank you and,

Cheers, JK

This join is exaclty what I am looking for but it relies on a calculated column "Inv #" on both sides to work.

The first column def (the one on the left) is seen for a brief moment in the video but the one on the right is never shown and has to resolve to "IN" & DOC# when the Trx type is "CA" so, clearly there is another formula hiding behind that one!

I am guessing at: IF( {Trx Type} ="CA","IN" ,"" ) & {Doc #} but it did not bind..

Thank you and,

Cheers, JK

PS: the guy who did the demo would know...