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...

Both of the 'Inv #' calculated columns are the same in each view:
{Trx Type} & {Doc #}

You then could choose to do a filter on the Matching view to select only invoices as the document type and cash as the matching type, or whatever else is desired. The video workspace doesn't have this.

These are required in order to get the GetLinkedValue to work as desired, as you have to sort the Matching file by invoice number and then by date descending in order to get the most recent payment date per invoice.

PS: I am that demo guy...

Your Adagio Consultant knows as well...

John:

As your Adagio consultant, I would say this is a great project for us to work on togther.

Reading through this thread I would say that all of the useful information that can be provided in this forum has already been said.

Good day gents,

I spent the afternoon Friday and most of the day yesterday and have now viewed the video 3 times and still cannot make the nice easy join that was shown in the Gridview demo for version 9.0C.

Considering that I am a programmer way back from the Clipper days and now into C# - I do not understand why this process seems so unattainable - something is wrong!

Cheers, JK

Good day gentlemen,

Thank you very much for the information - for some reason Friday's posts just appeared on my systems in the last few minutes.

I agree with Doug in that there is nothing further that can probably be communicated on this forum and will make arrangements to work with him directly.

Cheers, JK

PS: thank you demo guy!

Good day everyone!

After following up with professional support I have been told that what we are trying to do will not work and that the only path forward for this kind of analysis is to send everything through Adagio invoices and then use the tools provided in Sales Analysis.

We are taking this under consideration and have asked our reseller to make a request to development that would allow for better database intelligence by carrying the required information into these tables as I am certain there are others who are trying to do the same thing.

Thank you and,

Cheers, JK