CounterSoft User Forums

I'm trying to use CustomFieldsService.CreateCustomFieldLookupData to create a new lookup value for my custom field:

ServiceManager oSvcMgr = new ServiceManager(LogServerUrl, LogUserName, LogPassword, String.Empty, false);

UserEN oUser = oSvcMgr.AdminService.WhoAmI();
CustomFieldEN oCustomField = oSvcMgr.CustomFieldsService.GetCustomField(6);

CustomFieldLookupPopulateEN oLookupData = new CustomFieldLookupPopulateEN();
oLookupData.TableName = oCustomField.LookupName;
oLookupData.DescriptionValue = "NEW LOOKUP VALUE";
oLookupData.KeyColumnName = oCustomField.LookupValueField;
oLookupData.DescriptionColumnName = oCustomField.LookupTextField;
oLookupData = oSvcMgr.CustomFieldsService.CreateCustomFieldLookupData(oLookupData);

My custom table exists and the custom field is defined with table name, key field name / display field name set.

Each call results in returns (500) Internal Server Error exceptions.

Can you provide a sample code of how to do this the right way?

Whcih version of Gemini? Please check Gemini's error log (system log).

---

v3.6.0

Any errors in the log?

---

There are no Gemini errors in event viewer.
Does Gemini use any other logging target? If so where is it?

Log in as administrator to Gemini and go to the Administration -> System Log page.

---

I was not aware of this log. Anyway, here is an error message related to this:

Cannot insert explicit value for identity column in table 'mt_issuereportedby' when IDENTITY_INSERT is set to OFF. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at CounterSoft.Gemini.DataProvider.x4f26f7a726757349.xa8e79932adeefce0(String x09dba7c746667f19, String x9f89cba595365e97, String x140352814b747e6a, Int32 xc9ee5fcb0d5c1829, String xa17c122aa20097e5) at CounterSoft.Gemini.Business.xa07794653f9cfd6c.xa8e79932adeefce0(CustomFieldLookupPopulateEN xf5a53cafd026a015) at CounterSoft.Gemini.Presenter.CustomFieldsPresenter.PopulateCustomFieldLookup(CustomFieldLookupPopulateEN lookup) at CounterSoft.Gemini.Web.Api.CustomFieldsRestHandler.CustomFieldLookupPopulate(RequestDetails rd)

----------------------

The key column is an identity column with auto-generating value.

I got it to work (kind of) for now:

1. Disabled identity value on the key field in the table
2. I generate the next ID by getting max ID+1 in code (from existing lookup values.

The Web Service should generate the next id automatically. So is this a bug?

This method will not work with identity fields. What you can do is add another column to the table and pass that as the "Key".

---

Thanks for all your input. Here is the working solution I have in place:

---------------------------------------------

Custom lookup table definition:

CREATE TABLE [dbo].[mt_issuereportedby]
(
[id] [int] NOT NULL,
[name] [nvarchar](100) NOT NULL,

CONSTRAINT [PK_mt_issuereportedby] PRIMARY KEY CLUSTERED
(
[id] ASC
)
WITH
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 20
)
ON [PRIMARY]
)
ON [PRIMARY]

---------------------------------------------

Add the code:

ServiceManager oSvcMgr = new ServiceManager(
"ServerUrl", "UserName", "Password", String.Empty, false);

UserEN oUser = oSvcMgr.AdminService.WhoAmI();
ProjectEN oProject = oSvcMgr.ProjectsService.GetProject("PROJECT CODE");
CustomFieldEN oCustomField = oSvcMgr.CustomFieldsService.GetCustomField(6);

IssueEN oIssue = new IssueEN();
oIssue.IssueType = 1;
oIssue.IssueSeverity = 2;
oIssue.IssuePriority = 1;
oIssue.RiskLevel = 1;
oIssue.IssueStatus = 1;
oIssue.IssueResolution = 1;
oIssue.ReportedBy = oUser.UserID;
oIssue.ProjectID = oProject.ProjectID;
oIssue.Visibility = GeminiConstant.ISSUE_VISIBILITY_PRIVATE;
oIssue.VisibilityMemberType = GeminiConstant.SecurityMemberType.GlobalGroup;
oIssue.IssueSummary = "ISSUE SUMMARY TEXT";
oIssue.IssueLongDesc = "ISSUE LONG DESC TEXT";
oIssue = oSvcMgr.IssuesService.CreateIssue(oIssue);

oComment.UserID = oUser.UserID;
oComment.IssueID = oIssue.IssueID;
oComment.ProjectID = oProject.ProjectID;
oComment.Comment = "COMMENT TEXT";
oComment.Visibility = GeminiConstant.ISSUE_VISIBILITY_PRIVATE;
oComment.VisibilityMemberType = GeminiConstant.SecurityMemberType.GlobalGroup;
oComment = oSvcMgr.IssuesService.CreateComment(oIssue.IssueID, oComment);

Int32 iNextKey = 1;
GenericEN oGeneric = null;

foreach (GenericEN oLookup in oCustomField.LookupData)
{
Int32 iCurrKey = Int32.Parse(oLookup.GenericKey);
if (iCurrKey >= iNextKey) { iNextKey = iCurrKey + 1; }

if (oLookup.GenericValue.Equals("NEW LOOKUP VALUE"))
{
oGeneric = oLookup;
break;
}
}

if (oGeneric == null)
{
CustomFieldLookupPopulateEN oLookupData = new CustomFieldLookupPopulateEN();
oLookupData.TableName = oCustomField.LookupName;
oLookupData.KeyColumnName = oCustomField.LookupValueField;
oLookupData.DescriptionColumnName = oCustomField.LookupTextField;
oLookupData.KeyValue = iNextKey;
oLookupData.DescriptionValue = "NEW LOOKUP VALUE";
oLookupData = oSvcMgr.CustomFieldsService.CreateCustomFieldLookupData(oLookupData);

oGeneric = new GenericEN();
oGeneric.GenericKey = oLookupData.KeyValue.ToString();
oGeneric.GenericValue = oLookupData.DescriptionValue;
}

if (oGeneric != null)
{
CustomFieldDataEN oCustomFieldData = new CustomFieldDataEN();
oCustomFieldData.UserID = oUser.UserID;
oCustomFieldData.IssueID = oIssue.IssueID;
oCustomFieldData.ProjectID = oProject.ProjectID;
oCustomFieldData.CustomFieldID = oCustomField.CustomFieldID;
oCustomFieldData.CustomFieldData = oGeneric.GenericKey;
oCustomFieldData.CustomFieldDataID = Int32.Parse(oGeneric.GenericKey);
oIssue = oSvcMgr.CustomFieldsService.SaveCustomFieldData(oCustomFieldData);
}