Options
  • State Suggested Answer
  • Replies 2 replies
  • Answers 1 answer
  • Subscribers 7 subscribers
  • Views 1042 views
  • Users 0 members are here
Related

External Object - Update Query - Customization?

KageDev

Hi,

We recently added a field to an external object and included that field in the update statement. The field we created in M-Files is a Boolean, the field in the external source is numeric, but truly is a Boolean (I don't have control over the external system data types). When going updating the new field, it throws an error about the data types. The format of the update statement in the external connection is below:

 

Update ExampleTable Set ExampleField1 = ?, ExampleField2 = ? where ExampleID = ?

Can I customize the update statement to be something like this? My attempts to do this don't seem to be working.

Update ExampleTable Set ExampleField1 = ?, ExampleField2 = iif(? = True, 1 , 0) where ExampleID = ?

Parents
  • 0

    You can include logic in the update statement as long as you only use the "?" placeholder once for each property.

    I cannot determine whether your example with "iif" will work. That depends on the receiving external system's ability to process the code.

  • 0 in reply to bright-ideas.dk

    This is the error i get doing that:

    <MetadataEditor>, -, Updating the external database failed. (0x800400CD)
    ScriptErrorHelper.cpp, 96, Updating the external database failed. (0x800400CD)
    MDispatchExImpl.h, 668, Updating the external database failed. (0x800400CD)
    MDispatchExImpl.h, 830, Updating the external database failed. (0x800400CD)
    MetadataCardAction.cpp, 386, Updating the external database failed. (0x800400CD)
    MetadataCardAction.cpp, 570, Updating the external database failed. (0x800400CD)
    MetadataEditor.cpp, 2971, Updating the external database failed. (0x800400CD)
    MetadataModel.cpp, 4398, Updating the external database failed. (0x800400CD)
    MetadataModel.cpp, 4849, Updating the external database failed. (0x800400CD)
    ElectronicSignatureUIHelper.cpp, 239, Updating the external database failed. (0x800400CD)
    MetadataModel.cpp, 12505, Updating the external database failed. (0x800400CD)
    CoVaultMountingDocumentOperations.cpp, 3241, Updating the external database failed. (0x800400CD)
    CoVaultMountingDocumentOperations.cpp, 3329, Updating the external database failed. (0x800400CD)
    DocumentCache.cpp, 11813, Updating the external database failed. (0x800400CD)
    DocumentCache.cpp, 11924, Updating the external database failed. (0x800400CD)
    DocumentCache.cpp, 20380, Updating the external database failed. (0x800400CD)
    RPCMethodCallWithRetry.h, 36, Updating the external database failed. (0x800400CD)
    RPCMethodCallWithRetry.h, 36, Updating the external database failed. (0x800400CD)
    RPCDocumentOperations.cpp, 12618, Updating the external database failed. (0x800400CD)
    RPCDocumentOperations.cpp, 7174, Updating the external database failed. (0x800400CD)
    MCallInLoop.h, 521, Updating the external database failed. (0x800400CD)
    RPCDocumentOperationsHelper.cpp, 4255, Updating the external database failed. (0x800400CD)
    RPCDocumentOperationsHelper.cpp, 3813, Updating the external database failed. (0x800400CD)
    RPCDocumentOperationsHelper.cpp, 9589, Updating the external database failed. (0x800400CD)
    RPCDocumentOperationsHelper.cpp, 10813, Updating the external database failed. (0x800400CD)
    RPCDocumentOperationsHelper.cpp, 10959, Updating the external database failed. (0x800400CD)
    RefreshExternalObjects.cpp, 944, Updating the external database failed. (0x800400CD)
    RefreshExternalObjects.cpp, 944, Processing of the external object type or value list "320-19664, ExtID: {81ED70BC-2438-4EB1-86AD-F2B4EE8050DE}" failed. (0x8004011F)
    RefreshExternalObjects_Private.cpp, 1671, Processing of the external object type or value list "320-19664, ExtID: {81ED70BC-2438-4EB1-86AD-F2B4EE8050DE}" failed. (0x8004011F)
    RefreshExternalObjects_Private.cpp, 1671, Setting the value of the column "cmcInactive" failed. (0x800400DA)
    ExtDBObjectTypeDS.cpp, 859, Setting the value of the column "cmcInactive" failed. (0x800400DA)
    ExtDBObjectTypeDS.cpp, 859, The data type conversion "Boolean -> DBTYPE_NUMERIC" is not supported. (0x800400C7)
    ExtDBObjectTypeDS.cpp, 1934, The data type conversion "Boolean -> DBTYPE_NUMERIC" is not supported. (0x800400C7)
    SQLHelper.cpp, 1704, The data type conversion "Boolean -> DBTYPE_NUMERIC" is not supported. (0x800400C7)
    (M-Files 23.6.12695.4 2023-07-24T13:07:35.553Z)

    Actual Update Statement:

    UPDATE OrganizationContacts SET cmcOrganizationID = ?, cmcContactID = ?, cmcContactTitleID = ?, cmcName = ?, cmcPhoneNumber = ?, cmcMobileNumber = ?, cmcEMailAddress = ?, ucmcHowDidYouHearAboutUs = ?, cmcInactive = iif(? = 'True',1,0) WHERE cmcuniqueid = ?

Reply
  • 0 in reply to bright-ideas.dk

    This is the error i get doing that:

    <MetadataEditor>, -, Updating the external database failed. (0x800400CD)
    ScriptErrorHelper.cpp, 96, Updating the external database failed. (0x800400CD)
    MDispatchExImpl.h, 668, Updating the external database failed. (0x800400CD)
    MDispatchExImpl.h, 830, Updating the external database failed. (0x800400CD)
    MetadataCardAction.cpp, 386, Updating the external database failed. (0x800400CD)
    MetadataCardAction.cpp, 570, Updating the external database failed. (0x800400CD)
    MetadataEditor.cpp, 2971, Updating the external database failed. (0x800400CD)
    MetadataModel.cpp, 4398, Updating the external database failed. (0x800400CD)
    MetadataModel.cpp, 4849, Updating the external database failed. (0x800400CD)
    ElectronicSignatureUIHelper.cpp, 239, Updating the external database failed. (0x800400CD)
    MetadataModel.cpp, 12505, Updating the external database failed. (0x800400CD)
    CoVaultMountingDocumentOperations.cpp, 3241, Updating the external database failed. (0x800400CD)
    CoVaultMountingDocumentOperations.cpp, 3329, Updating the external database failed. (0x800400CD)
    DocumentCache.cpp, 11813, Updating the external database failed. (0x800400CD)
    DocumentCache.cpp, 11924, Updating the external database failed. (0x800400CD)
    DocumentCache.cpp, 20380, Updating the external database failed. (0x800400CD)
    RPCMethodCallWithRetry.h, 36, Updating the external database failed. (0x800400CD)
    RPCMethodCallWithRetry.h, 36, Updating the external database failed. (0x800400CD)
    RPCDocumentOperations.cpp, 12618, Updating the external database failed. (0x800400CD)
    RPCDocumentOperations.cpp, 7174, Updating the external database failed. (0x800400CD)
    MCallInLoop.h, 521, Updating the external database failed. (0x800400CD)
    RPCDocumentOperationsHelper.cpp, 4255, Updating the external database failed. (0x800400CD)
    RPCDocumentOperationsHelper.cpp, 3813, Updating the external database failed. (0x800400CD)
    RPCDocumentOperationsHelper.cpp, 9589, Updating the external database failed. (0x800400CD)
    RPCDocumentOperationsHelper.cpp, 10813, Updating the external database failed. (0x800400CD)
    RPCDocumentOperationsHelper.cpp, 10959, Updating the external database failed. (0x800400CD)
    RefreshExternalObjects.cpp, 944, Updating the external database failed. (0x800400CD)
    RefreshExternalObjects.cpp, 944, Processing of the external object type or value list "320-19664, ExtID: {81ED70BC-2438-4EB1-86AD-F2B4EE8050DE}" failed. (0x8004011F)
    RefreshExternalObjects_Private.cpp, 1671, Processing of the external object type or value list "320-19664, ExtID: {81ED70BC-2438-4EB1-86AD-F2B4EE8050DE}" failed. (0x8004011F)
    RefreshExternalObjects_Private.cpp, 1671, Setting the value of the column "cmcInactive" failed. (0x800400DA)
    ExtDBObjectTypeDS.cpp, 859, Setting the value of the column "cmcInactive" failed. (0x800400DA)
    ExtDBObjectTypeDS.cpp, 859, The data type conversion "Boolean -> DBTYPE_NUMERIC" is not supported. (0x800400C7)
    ExtDBObjectTypeDS.cpp, 1934, The data type conversion "Boolean -> DBTYPE_NUMERIC" is not supported. (0x800400C7)
    SQLHelper.cpp, 1704, The data type conversion "Boolean -> DBTYPE_NUMERIC" is not supported. (0x800400C7)
    (M-Files 23.6.12695.4 2023-07-24T13:07:35.553Z)

    Actual Update Statement:

    UPDATE OrganizationContacts SET cmcOrganizationID = ?, cmcContactID = ?, cmcContactTitleID = ?, cmcName = ?, cmcPhoneNumber = ?, cmcMobileNumber = ?, cmcEMailAddress = ?, ucmcHowDidYouHearAboutUs = ?, cmcInactive = iif(? = 'True',1,0) WHERE cmcuniqueid = ?

Children
No Data




              

© M-Files Corporation 2024 | Privacy Policy