Microsoft Dynamics GP (Archived)

Key Segment Not Found error

(0) Share

Report

Posted on by mavrick5au

Hello,

I hope someone can help me.

We have a third party standalone application written in Dexterity.

I have created some views in SQL to use to access the Dynamics database. For example, I have a Supplier Enquiry screen within my application that uses a view to display the PM00200 table data. This all works fine and I have the same concept set up for a Debtors Enquiry screen.

My problem is with displaying the transactions. While I can happily display the RM transactions with my view of the RM20101 table, when I try to view the supplier transactions with a view to the PM20000 table, I am getting the following error:

Key Segment Not Found.

After clicking on OK, I then get the following error:

Error registering table PM_Trans_OPEN_VW, which is the new view I created.

I know the view is OK as I can select the data with no problems via SQL. It is also executing the pass through SQL statement.

Using debug, the error is occurring at the following statement:

'Vendor ID' of table PM_Trans_Open_VW = sVendorID;

As this is a view which I created manually in SQL and then set up the table within Dexterity, is there something I have done wrong or missed. As mentioned, I have done exactly the same for Receivables reading the RM20101 table without any errors.

Mark

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Mick Egan 3,561 on at

Like (0)

Report

Only at a guess, check no records with missing VendorID's in the Table, and DYNGRP has access to the View.

Mick

Was this reply helpful? Yes No
Almas Mahfooz 11,009 User Group Leader on at

Like (0)

Report

Could you share your view?

and how you setup table in GP? did you duplicate the GP PM20000 table and give it a name of your view?

Was this reply helpful? Yes No
Suggested answer

Tom Mathew 1,750 on at

Like (0)

Report

compare your view with the vendor master and look for the missing suppliers in your view. Basically just give a look into how you have joined(key) your view and the vendor master in your application and just check whether the error is occurring for all the suppliers or some.

Was this reply helpful? Yes No
Suggested answer

David Musgrave MVP ... 14,095 Most Valuable Professional on at

Like (0)

Report

Hi Mark

I am pretty sure that this error is being caused by you having a field in one of your keys that is no longer in the table definition.

This can be caused by removing the field from the table after creating a key and forgetting to update the key.

Please check each of your key fields by clicking on them. The field show be highlighted in the list of fields on the left hand side.

When a field is not highlighted, you have found the field causing the error.

Check all the keys defined.

David

Was this reply helpful? Yes No
mavrick5au 6 on at

Like (0)

Report

Thanks for all the replies.

Just for some background info, I am running this against a test system at the moment. It has 1 vendor that has 1 invoice. So I am pretty confident that I don't have any issues with missing vendor ids.

Here is the view, pretty straight forward stuff:

CREATE VIEW [dbo].[PM_Trans_OPEN_VW]
AS
SELECT t.VCHRNMBR, t.VENDORID, t.DOCTYPE, t.DOCDATE, t.DOCNUMBR, t.DOCAMNT, t.CURTRXAM, t.DSCDLRAM, t.DISAMTAV, t.BACHNUMB, t.BCHSOURC, t.DUEDATE, t.PORDNMBR, p.DOCDATE PODATE,
t.VOIDED, t.CHEKBKID, t.CURNCYID, t.PYMTRMID, t.TRXDSCRN, t.VADCDTRO, t.WROFAMNT, t.SHIPMTHD, t.TAXSCHID, t.PRCHAMNT, t.TRDISAMT, t.FRTAMNT, t.MSCCHAMT,
t.TAXAMNT, t.DISTKNAM, t.TaxInvReqd, t.DEX_ROW_ID
FROM PM20000 t
LEFT OUTER JOIN POP10100 p on p.PONUMBER = t.PORDNMBR

grant select on PM_Trans_OPEN_VW to DYNGRP

I can select from this view without any errors in SQL. It is Dex giving me the error. I know that I need to include the Dex row id to make the view usable within Dex and I have verified that the columns in my table definition match the view.

I will take a look at the keys defined for PM20000 and POP10100 and make sure I haven't caused an issue with my table definition in our standalone application.

Regards,

Mark

Was this reply helpful? Yes No
mavrick5au 6 on at

Like (0)

Report

David had the solution. I still had key8 as TRXSOURCE which is a field I removed from the view. Once I deleted this key definition from the view, it is all working.

Most of the views I have set up are direct copies of the existing Dynamics tables (I was trying to put a layer between our application and Dynamics. If something changes in Dynamics, I should only need to update my view if I need the new field).

Thanks for help all.

Mark Fraser

IT Applications Manager

TR Pty Ltd

Was this reply helpful? Yes No