Is there a table that can be filtered by customer number and show line items of OE invoices and credit notes and also show the AR original and current amounts?

Hi Susan

What you want, which is to link transaction tables across different modules, is generally not a GridView function - it is a Crystal Reports function. GridView, when it links tables, almost always links one transaction table to one or more master tables.

On the other hand, if you are determined to use GridView, you could create a Workspace with multiple Views and then use the /XDW parameter to send the workspace results to Excel, where you can then link them together in Excel. I do it frequently, and it works nicely.

Steve

Hello Susan:

There is no composite table that would do that. The alternative would be to have 2 linked Views. One would be AR Invoices showing the Original and Current amounts. The 2nd would be the Invoice and Credit Note details. When you select a specific AR invoice, it would look up and the display the details in the linked View.

This works well for on-screen inquiries. If you are interested in a printed report, you could do this with Crystal Reports.

A screen view is what we're after. I created a view with OE Order Detail w/header and another view with AR Transactions. Both tables have the field Doc #. I linked OE view to the AR view. When I dbl click a doc # in the OE view, the AR view loads.. but comes up blank. I know the document is in there because I can see it when I refresh the AR view by itself. I can't figure out what I'm doing wrong.

The only thing I can guess at that might be causing the problem is that the OE view stores the Doc # field as "'___12345" (3 spaces in front of 5 numbers) and the AR view stores the Doc # field as "'_______12345" (7 spaces in front of 5 numbers). Would this cause the documents not to link? Is there a way around this?

Please help... my Gridview link won't work!

OE Order Detail w/Head table containing field Doc #.

is linked to:

AR Transactions containing field Doc #.

The 2nd view loads but comes up blank! In Field Properties both are called Doc # and neither have a Link Name. The Field Length is 8 in the OE table and 12 in the AR table.

Please help me link these views!!! I really need to get this working.

Thank-you.

Hi Susan,

That is indeed the reason why the two 'document #' fields are not linking - because one is 8 characters and the other is 12. GridView linking requires exact matching, including the spaces.

You need to create a calculated column in each view that trims the spaces, using the TRIM function. So it would look something like TRIM({Doc #}). The calculated columns in each view need the same title so that they can be linked.

Sometimes GridView is not happy about trying to link on a numeric value. If you find that even after creating the calculated columns that the linking still is not working, you need to 'convert' the value to text, by appending the document number with a character. Perhaps an I for invoice, or even the document type so that invoices and credit notes can be kept separate. Something like:

"I" & TRIM({Document #}) or {Doc Type} & TRIM({Doc #})

If you want to use I or C depending on the document type, the formula will be different in OE as compared to AR because of available information. Lets say you want IN123 to show invoice 123, or CN123 if it's a credit note, assuming that Orders are filtered from the OE view:

OE:
IF({Doc Type}="I","IN"&TRIM({Doc #}), "CN"&TRIM({Doc #}))

AR:
{Trx Type} & TRIM({Doc #})

I did have to convert the values to text by using the second method you described. Showing IN or CN in front of the document number improves the view even more! This is a big help to us. Thank-you so much.

Thanks for the feedback and letting us know that this worked for you.

Just to give a little more enticement, but it is possible to add another view based on the AR Transaction Matching file in order to link to the payments (or credits or adjustments) against invoices where the current and original amounts aren't the same...