x Want to participate? Join the group to interact
  • State Suggested Answer
  • Replies 19 replies
  • Answers 1 answer
  • Subscribers 78 subscribers
  • Views 2248 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
32
replies
General Discussion
6k
topics
18k
replies
Prabha

Date upload screwed up 2 fields on my company summary screen

SUGGESTED
Posted By

Hello everybody,

I imported some data into the company table via the data upload function. This CRM version 2020R2

I had 50 companies to import and I mapped the fields properly and it imported them in. 2 of the fields: account manager and SLA had to be imported as integer which I did but now I have lost the dropdown list on these 2 fields that were there before I did the upload.

I did take a backup before the upload but I do not want to revert to backup since I still need to import those 50 companies in.

For account manager all I see FOR ALL COMPANIES is a number when it should 1. show the name of the user 2. allow me to pick from a drop down list the name of a user

Same thing for SLA, it used to be a drop down list with the names of my agreements but now it just shows a number.

I tried to go on the table and change them back to "selection list" or "select user" but it is not saving my changes.

Yes I did do a metadata refresh.

In my cases screen, the SLA field shows up properly with a drop down list:

I am wondering if there is a custom table in CRM that can be edited to fix this issue. It only affects these 2 fields on the company screen.

Thank you!

Prabha

  • 0

    Hi Prabha

    On the SQL database see if there is a user with ID 53 (SELECT * FROM Users WHERE user_userid = 53) if it is displaying a number it seems to be suggesting that there is no user of that ID

    If there is a user with that number, then double check the company within the database that is wrong and see what it thinks has been imported. Could be that XLS was the problem and held the number as text (e.g. '53) and this has been imported and CRM doesn't understand it. This can be fixed with an Update script (this also applies to the SLA field as well) 

  • 0

    Prabha:.  I agree with Matthew, it sounds like the user id is being stored in that field.

    instead of a selection list, I am guessing the field was a user select field. It sounds like you will need to do an SQL update query to replace the imported names with their respective user Id.

    Hope this makes sense.

  • 0

    I am not sure what to look at. In the Users table there is a user with ID 53 which is fine. It has its user_lastname and user_firstname fine. That field Account Manager (comp_primaryuserid) is stored as an integer in the company table - has always been an integer. The table is fine, it is the field on the screen that is messed up.

    SLA is also and always has been an integer type in the TABLE; on the CRM screen however it was a "selection list". There is no such data type as selection list in SQL. There is nothing to update in the table itself - the data is correct.

    On the screen those 2 fields used to be "select user" for account manager and "selection list" for SLA. CRM would convert the integers into the specific description. 

    For the SLA I had "NA" in the field before the import which was a valid value but it complained that it needed an integer so I mapped it to the integer value.

    For the account manager I mapped the name according to the mapping tool provided:

    If the data type was wrong, it would not have imported at all; I would have received an SQL error. 

    I don't think it is a data issue. I think it is a CRM field display issue. Why would the import change the field type set on the screen to begin with?

    Thank you

    Prabha

  • 0 in reply to Prabha
    Prabha said:
    On the screen those 2 fields used to be "select user" for account manager and "selection list" for SLA.

    What are the field types now?

  • 0 in reply to Paul C

    Integer. And it is not letting me change them back. I change them, save it but it is staying as integer

  • 0 in reply to Prabha

    You can edit custom_edits directly and change the fields back - will need to do a metadata data fresh to update CRM front end

  • 0 in reply to Prabha

    Matthew beat me to it - I would run the following query first to see what's in Custom_Edits for those two fields:

    SELECT ColP_ColName, ColP_EntryType, ColP_DefaultType, ColP_DefaultValue, ColP_EntrySize, ColP_LookupFamily, ColP_CustomTableIDFK
    FROM Custom_Edits
    WHERE Colp_Entity='Company' AND ColP_Colname IN ('comp_primaryuserid','comp_SLAId')

  • 0 in reply to Paul C

    What should I be changing them to please?

    I had a copy of the database before the upload which I loaded. I tried the same query on that copy of the data and it looks exactly the same:

  • 0 in reply to Prabha

    Nothing - they are correct.

    I think this is a metadata issue.  Try running the metadata refresh again.

  • 0 in reply to Paul C

    Done. I did a meta data refresh after the upload too. No change.

  • 0 in reply to Prabha

    Are there any errors when you ran the meta refresh in the log files? ewaresystem.log and ewaresql.log.

  • 0 in reply to Paul C

    There are errors but nothing related to this issue. The errors are on views/tables that are no longer in use.

  • 0 in reply to Prabha

    So did the metadata refresh complete successfully?  In the ewaresystem.log there should be an entry for:

    Full metadata refresh requested from Admin screen. (assuming you are logged in as Admin)

    when you started it and another for:

    Refreshing metadata finish.

    when it ended.  In between these will be ~25 lines logging the progress.  There shouldn't be any errors apart from:

    ERROR, Value name DefaultDomain does not exist.
    ERROR, Value name EMPassword does not exist.
    ERROR, Value name EMPollInterval does not exist.

    These errors have been logged for as long as I can remember.

  • 0 in reply to Paul C

    Yes I see those errors and then there are other errors related to a product that we used to have but are not longer using. There is this:

    Other than that there are no errors.

  • 0 in reply to Prabha

    I'm not sure what to suggest.  I've seen the OpenThreadToken and ImpersonateLoggedOnUser errors before a long time ago.  I found the last email I received from Sage CRM support which said "this issue is fixed in CRM 7.2. scheduled for release later this year" - that was in January 2013, almost a year after I reported the issue.

    I think you will need to open a case with Sage Support via your Sage CRM business partner.

  • 0 in reply to Paul C

    LOL. I am the business partner. Actually I reached out to Sage Tech for this issue directly and they said to put in on Sage City just in case someone else has run into the same issue.

  • 0 in reply to Prabha

    I presume you've done the basics, like rebooting the CRM server?

  • 0 in reply to Paul C

    The server was rebooted on the week end. This issue happened on Friday the 8th.

  • 0
    SUGGESTED

    So guys. I fixed it!! The table is custom_edits. We were on the right path Paul C just had to look into other columns. I just compared the fields to the same fields in other entities which were working fine.

    I changed some values for the fields comp_primaryuserid and comp_slaid

    Set colp_deleted to NULL (was set to 1)

    Set colp_datatype to NULL (was set to 5)

    I also changed colp_datasize from 0 to NULL but I think the first 2 are the ones that did the trick.

    Did a metadata refresh and tada!!

*Community Hub is the new name for Sage City