x Want to participate? Join the group to interact
  • State Not Answered
  • Replies 3 replies
  • Subscribers 74 subscribers
  • Views 226 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
32
replies
General Discussion
6k
topics
18k
replies
IDLSystems

Sage CRM Address_Link Table has bad records, how can they be repaired.

Posted By
  • A company record has the following;
  • One address record
  • Two Person records
  • The Address_Link table has SIX entries - all of them with Adli_deleted = NULL.
  • I changed the address record and TWO of these records date stamp was updated.
  • The other FOUR records should NOT be there, or should at least have the Adli_Deleted field flagged with a "1".
  • I have a Person Address Search and a Company Address Search added component.
  • When I run these searches (Find) it picks up all of these records as it is looking for adli_deleted = NULL.
  • There are hundreds of companies that have this problems that involves thousands of address link records.
  • Has anybody got a routine / process / fix that can repair this table.

Much appreciated.

  • 0

    FURTHER INVESTIGATION:

    - I obtained the component (Sage CRM v7.2: Component: Person Address Search Screen) from the downloads section.

    When I set it up I found that on the display grid there were many duplicates, as stated in my first section.

    - Having carried out more investigation there may not be an error in the address_Link table, but in the view supplied within the component.

    - I installed this same component at Three Clients and all of them show duplicates and when delving into the address_link table they are all the same.

    Every time you have a different address "Type", this will create an additional records in the address_link table.

    - Conclusion: It would then appear that the table is not wrong but the vPersonAddressSearch view table supplied.

    - So my question is has anybody else found this problem and if so have you managed to modify the view so as not to pick up these duplicates !!!!!

    - SAGE CRM: Do you have any fix for this ????

    Much appreciated.

  • 0

    Ian

    The search facility works this way because you need to be able to search for the address type which belongs to the address_link table. The multiple occurances of the address are returned by design.

  • 0

    Hi Jeff,

    Thanks for this explanation.

    - if we want the list just to list the persons and the address without having each type displayed as well, as they are all the same address record, what do we need to do with the view.

    ALSO Please help:

    - I have also manually created to list all company addresses.

    It works fine except that it picks up all records that are in the address_link table.

    Obviously it is my view that I have - is there any way that this could be refined to just pick up the address without all of its duplicates - I am sure with your extensive knowledge of views this will be very simple (I am surprised that the CRM system can not find all address records, but only the default one).

    This is the view that I am using

    CREATE VIEW vCompanyAddressSearch

    AS SELECT Address.*, Address_Link.*, vCompanyPE.*, Account.*, vPersonPE.* FROM Address_Link LEFT JOIN Address ON AdLi_AddressId = Addr_AddressId LEFT JOIN vCompanyPE ON AdLi_CompanyId = Comp_CompanyId LEFT JOIN Account ON AdLi_AccountId = Acc_AccountId LEFT JOIN vPersonPE ON AdLi_PersonID = Pers_PersonId WHERE AdLi_Deleted IS NULL

    Any help will be most appreciated.

*Community Hub is the new name for Sage City