Item Lot Summary Explore - Source / Target natural column lists are not valid.

Just upgraded from 7.3 to 7.5, and running into this message when opening the Explore - Item Lot Summary. The summary still runs if the user clicks through, but it's a bit annoying. 

I've done some poking around in the tsmdataview tables, and found a duplicate key in: tsmdataviewlinkcolumn

dataviewlinkkey SourceDataViewLInkColumn TargetDataViewLInkColum
2614 ItemID ItemID
2614 LotNo LotNo

Now, I'm not really sure of the proper fix for this. I've deleted the record with LotNo and the error message goes away, but I'm not sure if this is the proper fix. 

Any insights would be appreciated! Thanks



Parents
  • 0

    Have you customized this explorer view? It seems like this isn't a problem with the explorer view, but with the preview explorer view. For me, my default preview is the Lot Detail explorer view. If you go to View -> View Lot Detail on the menu and turn off the preview window on the bottom, I'll bet that you don't get the error message. This is because it isn't trying to pull up the record in the preview explorer view when it's turned off.

    I just checked the Sage 500 demo company and there is also a duplicate link key. This isn't a problem because it just means that you need more than one column to form the link. If you remove the LotNo column link, then you will have all records show up in the Lot Detail preview for the item, not just the ones with matching lots.

    I think your problem might have to do with TargetDataViewKey=240. Maybe that record doesn't exist? You would know if you run the following query and you don't get any records:

    SELECT *
    FROM dbo.tsmDataView
    WHERE DataViewKey=240

    Here is another query that might give you some insight into the problem:

    SELECT Source.Caption,Target.Caption,Target.DataViewID,dbo.tsmDataViewLink.*
    FROM dbo.tsmDataViewLinkColumn
    	JOIN dbo.tsmDataViewLink ON dbo.tsmDataViewLinkColumn.DataViewLinkKey = dbo.tsmDataViewLink.DataViewLinkKey
    	LEFT OUTER JOIN dbo.tsmDataView Source ON dbo.tsmDataViewLink.SourceDataViewKey = Source.DataViewKey
    	LEFT OUTER JOIN dbo.tsmDataView Target ON dbo.tsmDataViewLink.TargetDataViewKey = Target.DataViewKey
    WHERE Source.Caption='Lot Summary'
Reply
  • 0

    Have you customized this explorer view? It seems like this isn't a problem with the explorer view, but with the preview explorer view. For me, my default preview is the Lot Detail explorer view. If you go to View -> View Lot Detail on the menu and turn off the preview window on the bottom, I'll bet that you don't get the error message. This is because it isn't trying to pull up the record in the preview explorer view when it's turned off.

    I just checked the Sage 500 demo company and there is also a duplicate link key. This isn't a problem because it just means that you need more than one column to form the link. If you remove the LotNo column link, then you will have all records show up in the Lot Detail preview for the item, not just the ones with matching lots.

    I think your problem might have to do with TargetDataViewKey=240. Maybe that record doesn't exist? You would know if you run the following query and you don't get any records:

    SELECT *
    FROM dbo.tsmDataView
    WHERE DataViewKey=240

    Here is another query that might give you some insight into the problem:

    SELECT Source.Caption,Target.Caption,Target.DataViewID,dbo.tsmDataViewLink.*
    FROM dbo.tsmDataViewLinkColumn
    	JOIN dbo.tsmDataViewLink ON dbo.tsmDataViewLinkColumn.DataViewLinkKey = dbo.tsmDataViewLink.DataViewLinkKey
    	LEFT OUTER JOIN dbo.tsmDataView Source ON dbo.tsmDataViewLink.SourceDataViewKey = Source.DataViewKey
    	LEFT OUTER JOIN dbo.tsmDataView Target ON dbo.tsmDataViewLink.TargetDataViewKey = Target.DataViewKey
    WHERE Source.Caption='Lot Summary'
Children
No Data