Solved

Conversion from character string to date failed


Userlevel 2
Badge

Received the following message: 'Conversion failed when converting date and/or time from character string'. My GI used two date fields; ARInvoice.DueDate and ARPayment.DocDate, I don't believe either of these data elements is stored as a Text string that would require conversion to be used in a GI. 

Is there more to this story?

icon

Best answer by Naveen Boga 13 August 2021, 16:28

View original

11 replies

Userlevel 7
Badge +17

Hi @mpeck12  The both “ ARInvoice.DueDate and ARPayment.DocDate” field types stored date format and NOT required any conversion to these fields.

If possible, can you please share your GI, will check and let you know what causing this issue, if I can.

Userlevel 5
Badge +1

Yes, there could be something else is causing this issue. If could be easy to identify if you can share more details about the GI. 

 

Happy Coding!

Userlevel 2
Badge

What is the best way to share the GI details?

(I am new to Acumatica, so there a couple of things I need to figure out)

I am looking for an option to print out the GI details, but I don’t see one. The options tor export as a report all require getting past the conversion error.

If there is a preferred way for you to receive this information, please let me know.

Userlevel 5
Badge +1

Go to Generic Inquiry screen and open the required GI and then try Export as XML.

 

Userlevel 2
Badge

 

Userlevel 2
Badge

Output of Export to XML

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<data-set>
<relations format-version="3" relations-version="20201113" main-table="GIDesign" stable-sharing="True" file-name="(Name)">
<link from="GIFilter (DesignID)" to="GIDesign (DesignID)" />
<link from="GIGroupBy (DesignID)" to="GIDesign (DesignID)" />
<link from="GIMassAction (DesignID)" to="GIDesign (DesignID)" />
<link from="GIMassUpdateField (DesignID)" to="GIDesign (DesignID)" />
<link from="GINavigationScreen (DesignID)" to="GIDesign (DesignID)" />
<link from="GINavigationParameter (DesignID, NavigationScreenLineNbr)" to="GINavigationScreen (DesignID, LineNbr)" />
<link from="GINavigationCondition (DesignID, NavigationScreenLineNbr)" to="GINavigationScreen (DesignID, LineNbr)" />
<link from="GIOn (DesignID, RelationNbr)" to="GIRelation (DesignID, LineNbr)" />
<link from="GIRecordDefault (DesignID)" to="GIDesign (DesignID)" />
<link from="GIRelation (DesignID, ParentTable)" to="GITable (DesignID, Alias)" />
<link from="GIRelation (DesignID, ChildTable)" to="GITable (DesignID, Alias)" />
<link from="GIResult (DesignID)" to="GIDesign (DesignID)" />
<link from="GIResult (ObjectName, DesignID)" to="GITable (Alias, DesignID)" />
<link from="GISort (DesignID)" to="GIDesign (DesignID)" />
<link from="GITable (DesignID)" to="GIDesign (DesignID)" />
<link from="GIWhere (DesignID)" to="GIDesign (DesignID)" />
<link from="SiteMap (Url)" to="GIDesign (DesignID)" type="WeakByUrl" linkname="toDesignById" baseurl="~/GenericInquiry/GenericInquiry.aspx" paramnames="id" />
<link from="SiteMap (Url)" to="GIDesign (Name)" type="WeakByUrl" linkname="toDesignByName" baseurl="~/GenericInquiry/GenericInquiry.aspx" />
<link from="ListEntryPoint (ListScreenID)" to="SiteMap (ScreenID)" />
<link from="SiteMap (ScreenID)" to="GIDesign (PrimaryScreenIDNew)" linkname="to1Screen" />
<link from="FilterHeader (ScreenID)" to="SiteMap (ScreenID)" />
<link from="FilterRow (FilterID)" to="FilterHeader (FilterID)" />
<link from="PivotTable (NoteID)" to="FilterHeader (RefNoteID)" />
<link from="PivotField (ScreenID, PivotTableID)" to="PivotTable (ScreenID, PivotTableID)" />
<link from="MUIScreen (NodeID)" to="SiteMap (NodeID)" />
<link from="MUIWorkspace (WorkspaceID)" to="MUIScreen (WorkspaceID)" type="FromMaster" linkname="workspaceToScreen" split-location="yes" updateable="True" />
<link from="MUISubcategory (SubcategoryID)" to="MUIScreen (SubcategoryID)" type="FromMaster" updateable="True" />
<link from="MUITile (ScreenID)" to="SiteMap (ScreenID)" />
<link from="MUIWorkspace (WorkspaceID)" to="MUITile (WorkspaceID)" type="FromMaster" linkname="workspaceToTile" split-location="yes" updateable="True" />
<link from="MUIArea (AreaID)" to="MUIWorkspace (AreaID)" type="FromMaster" updateable="True" />
<link from="MUIPinnedScreen (NodeID, WorkspaceID)" to="MUIScreen (NodeID, WorkspaceID)" type="WeakIfEmpty" isEmpty="Username" />
<link from="MUIFavoriteWorkspace (WorkspaceID)" to="MUIWorkspace (WorkspaceID)" type="WeakIfEmpty" isEmpty="Username" />
<link from="GIDesign (NoteID)" to="Note (NoteID)" type="Note" />
<link from="GIFilter (NoteID)" to="Note (NoteID)" type="Note" />
<link from="GIFilter (NoteID)" to="GIFilterKvExt (RecordID)" type="RowKvExt" />
<link from="GIGroupBy (NoteID)" to="Note (NoteID)" type="Note" />
<link from="GIOn (NoteID)" to="Note (NoteID)" type="Note" />
<link from="GIRelation (NoteID)" to="Note (NoteID)" type="Note" />
<link from="GIResult (NoteID)" to="Note (NoteID)" type="Note" />
<link from="GIResult (NoteID)" to="GIResultKvExt (RecordID)" type="RowKvExt" />
<link from="GISort (NoteID)" to="Note (NoteID)" type="Note" />
<link from="GITable (NoteID)" to="Note (NoteID)" type="Note" />
<link from="GIWhere (NoteID)" to="Note (NoteID)" type="Note" />
<link from="FilterHeader (NoteID)" to="Note (NoteID)" type="Note" />
<link from="FilterHeader (NoteID)" to="FilterHeaderKvExt (RecordID)" type="RowKvExt" />
</relations>
<layout>
<table name="GIDesign">
<table name="GIFilter" uplink="(DesignID) = (DesignID)">
<table name="Note" uplink="(NoteID) = (NoteID)" />
<table name="GIFilterKvExt" uplink="(NoteID) = (RecordID)" />
</table>
<table name="GIGroupBy" uplink="(DesignID) = (DesignID)">
<table name="Note" uplink="(NoteID) = (NoteID)" />
</table>
<table name="GIMassAction" uplink="(DesignID) = (DesignID)" />
<table name="GIMassUpdateField" uplink="(DesignID) = (DesignID)" />
<table name="GINavigationScreen" uplink="(DesignID) = (DesignID)">
<table name="GINavigationParameter" uplink="(DesignID, LineNbr) = (DesignID, NavigationScreenLineNbr)" />
<table name="GINavigationCondition" uplink="(DesignID, LineNbr) = (DesignID, NavigationScreenLineNbr)" />
</table>
<table name="GIRecordDefault" uplink="(DesignID) = (DesignID)" />
<table name="GISort" uplink="(DesignID) = (DesignID)">
<table name="Note" uplink="(NoteID) = (NoteID)" />
</table>
<table name="GITable" uplink="(DesignID) = (DesignID)">
<table name="GIRelation" uplink="(DesignID, Alias) = (DesignID, ParentTable)">
<table name="GIOn" uplink="(DesignID, LineNbr) = (DesignID, RelationNbr)">
<table name="Note" uplink="(NoteID) = (NoteID)" />
</table>
<table name="Note" uplink="(NoteID) = (NoteID)" />
</table>
<table name="GIResult" uplink="(Alias, DesignID) = (ObjectName, DesignID)">
<table name="Note" uplink="(NoteID) = (NoteID)" />
<table name="GIResultKvExt" uplink="(NoteID) = (RecordID)" />
</table>
<table name="Note" uplink="(NoteID) = (NoteID)" />
</table>
<table name="GIWhere" uplink="(DesignID) = (DesignID)">
<table name="Note" uplink="(NoteID) = (NoteID)" />
</table>
<table name="SiteMap" uplink="(DesignID) = (Url)" linkname="toDesignById">
<table name="ListEntryPoint" uplink="(ScreenID) = (ListScreenID)" />
<table name="FilterHeader" uplink="(ScreenID) = (ScreenID)">
<table name="FilterRow" uplink="(FilterID) = (FilterID)" />
<table name="PivotTable" uplink="(RefNoteID) = (NoteID)">
<table name="PivotField" uplink="(ScreenID, PivotTableID) = (ScreenID, PivotTableID)" />
</table>
<table name="Note" uplink="(NoteID) = (NoteID)" />
<table name="FilterHeaderKvExt" uplink="(NoteID) = (RecordID)" />
</table>
<table name="MUIScreen" uplink="(NodeID) = (NodeID)">
<table name="MUIPinnedScreen" uplink="(NodeID, WorkspaceID) = (NodeID, WorkspaceID)" />
</table>
<table name="MUITile" uplink="(ScreenID) = (ScreenID)" />
</table>
<table name="SiteMap" uplink="(Name) = (Url)" linkname="toDesignByName">
<table name="ListEntryPoint" uplink="(ScreenID) = (ListScreenID)" />
<table name="FilterHeader" uplink="(ScreenID) = (ScreenID)">
<table name="FilterRow" uplink="(FilterID) = (FilterID)" />
<table name="PivotTable" uplink="(RefNoteID) = (NoteID)">
<table name="PivotField" uplink="(ScreenID, PivotTableID) = (ScreenID, PivotTableID)" />
</table>
<table name="Note" uplink="(NoteID) = (NoteID)" />
<table name="FilterHeaderKvExt" uplink="(NoteID) = (RecordID)" />
</table>
<table name="MUIScreen" uplink="(NodeID) = (NodeID)">
<table name="MUIPinnedScreen" uplink="(NodeID, WorkspaceID) = (NodeID, WorkspaceID)" />
</table>
<table name="MUITile" uplink="(ScreenID) = (ScreenID)" />
</table>
<table name="SiteMap" uplink="(PrimaryScreenIDNew) = (ScreenID)" linkname="to1Screen">
<table name="ListEntryPoint" uplink="(ScreenID) = (ListScreenID)" />
<table name="FilterHeader" uplink="(ScreenID) = (ScreenID)">
<table name="FilterRow" uplink="(FilterID) = (FilterID)" />
<table name="PivotTable" uplink="(RefNoteID) = (NoteID)">
<table name="PivotField" uplink="(ScreenID, PivotTableID) = (ScreenID, PivotTableID)" />
</table>
<table name="Note" uplink="(NoteID) = (NoteID)" />
<table name="FilterHeaderKvExt" uplink="(NoteID) = (RecordID)" />
</table>
<table name="MUIScreen" uplink="(NodeID) = (NodeID)">
<table name="MUIPinnedScreen" uplink="(NodeID, WorkspaceID) = (NodeID, WorkspaceID)" />
</table>
<table name="MUITile" uplink="(ScreenID) = (ScreenID)" />
</table>
<table name="Note" uplink="(NoteID) = (NoteID)" />
</table>
<table name="MUIWorkspace">
<table name="MUIFavoriteWorkspace" uplink="(WorkspaceID) = (WorkspaceID)" />
</table>
<table name="MUISubcategory" />
<table name="MUIArea" />
</layout>
<data>
<GIDesign>
<row DesignID="00e23895-8349-4389-a5e5-3d57b7662299" Name="AR-AverageDaysToPay" FilterColCount="3" PageSize="0" ExportTop="0" NewRecordCreationEnabled="0" MassDeleteEnabled="0" AutoConfirmDelete="0" MassRecordsUpdateEnabled="0" MassActionsOnRecordsEnabled="0" ExposeViaOData="0" ExposeViaMobile="0" ShowDeletedRecords="0">
<GISort LineNbr="1" IsActive="1" DataFieldName="ARInvoice.refNbr" SortOrder="D" />
<GITable Alias="ARPayment" Name="PX.Objects.AR.ARPayment">
<GIResult LineNbr="13" SortOrder="7" IsActive="1" Field="=DateDiff( 'd', [ARInvoice.DueDate],[ARPayment.DocDate])" Caption="Days to Pay" IsVisible="1" DefaultNav="0" QuickFilter="0" FastFilter="0" RowID="a0a21697-669f-4b91-bdd5-00d5b02b1aee" />
<GIResult LineNbr="11" SortOrder="6" IsActive="1" Field="docDate" SchemaField="ARPayment.DocDate" Caption="Document Amount" IsVisible="1" DefaultNav="0" QuickFilter="0" FastFilter="0" RowID="c993a58a-c620-4e2b-a9b1-db9515aca73c" />
</GITable>
<GITable Alias="ARInvoice" Name="PX.Objects.AR.ARInvoice">
<GIResult LineNbr="4" SortOrder="3" IsActive="1" Field="dueDate" SchemaField="ARInvoice.DocDate" IsVisible="1" DefaultNav="0" QuickFilter="0" FastFilter="0" RowID="1f09adc2-29a7-44eb-8b10-6349e3383e13" />
</GITable>
<GITable Alias="ARAdjust" Name="PX.Objects.AR.ARAdjust">
<GIRelation LineNbr="2" ChildTable="ARPayment" IsActive="1" JoinType="I">
<GIOn LineNbr="2" ParentField="adjgRefNbr" Condition="E " ChildField="refNbr" Operation="A" />
<GIOn LineNbr="1" ParentField="adjgDocDate" Condition="E " ChildField="docType" Operation="A" />
</GIRelation>
<GIRelation LineNbr="1" ChildTable="ARInvoice" IsActive="1" JoinType="I">
<GIOn LineNbr="4" ParentField="adjdRefNbr" Condition="E " ChildField="refNbr" Operation="A" />
<GIOn LineNbr="3" ParentField="adjdDocType" Condition="E " ChildField="docType" Operation="A" />
</GIRelation>
<GIResult LineNbr="15" SortOrder="5" IsActive="1" Field="adjgRefNbr" IsVisible="1" DefaultNav="0" QuickFilter="0" FastFilter="0" RowID="2d3616f8-2355-4c3b-93e4-d2a44ba774b5" />
<GIResult LineNbr="14" SortOrder="4" IsActive="1" Field="adjgDocType" Caption="Closing Date" IsVisible="1" DefaultNav="0" QuickFilter="0" FastFilter="0" RowID="7568bb15-a9f1-469d-932a-866671fa96d7" />
<GIResult LineNbr="2" SortOrder="2" IsActive="1" Field="adjdRefNbr" IsVisible="1" DefaultNav="0" QuickFilter="0" FastFilter="0" RowID="a0ea15b3-f981-4115-8a79-e843d995d62f" />
<GIResult LineNbr="1" SortOrder="1" IsActive="1" Field="adjdDocType" Caption="Document Type" Width="100" IsVisible="1" DefaultNav="0" QuickFilter="0" FastFilter="0" RowID="3d92eb0f-8e40-41aa-9ad9-8c504014efe6" />
</GITable>
<GIWhere LineNbr="1" IsActive="1" DataFieldName="ARAdjust.adjgDocType" Condition="E " IsExpression="0" Value1="='PMT'" Operation="A" />
<SiteMap linkname="toDesignById">
<row Title="Average Days to Pay" Url="~/genericinquiry/genericinquiry.aspx?id=00e23895-8349-4389-a5e5-3d57b7662299" ScreenID="AR0012DB" NodeID="a4012760-eefc-4910-9247-fe31102a5b93" ParentID="00000000-0000-0000-0000-000000000000">
<MUIScreen IsPortal="0" WorkspaceID="bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb" Order="640242" SubcategoryID="98e86774-69e3-41ea-b94f-eb2c7a8426d4">
<MUIPinnedScreen IsPortal="0" Username="" IsPinned="1" />
</MUIScreen>
</row>
</SiteMap>
</row>
</GIDesign>
<MUIWorkspace>
<row IsPortal="1" WorkspaceID="bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb" Order="5" Title="Data Views" Icon="poll" IsSystem="1">
<MUIFavoriteWorkspace IsPortal="0" Username="" IsFavorite="1" />
</row>
<row IsPortal="0" WorkspaceID="bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb" Order="32" Title="Data Views" Icon="poll" IsSystem="1">
<MUIFavoriteWorkspace IsPortal="0" Username="" IsFavorite="1" />
</row>
</MUIWorkspace>
<MUISubcategory>
<row IsPortal="1" SubcategoryID="98e86774-69e3-41ea-b94f-eb2c7a8426d4" Order="896" Name="Inquiries" Icon="" IsSystem="1" />
<row IsPortal="0" SubcategoryID="98e86774-69e3-41ea-b94f-eb2c7a8426d4" Order="896" Name="Inquiries" Icon="" IsSystem="1" />
</MUISubcategory>
</data>
</data-set>

 

 

Userlevel 2
Badge

As i suspected, and Naveen B confirmed, “ ARInvoice.DueDate and ARPayment.DocDate” field types are stored date format and NOT required any conversion to these fields.

The next focus is the calculation on the last line of the results grid:

‘=DateDiff( 'd', [ARInvoice.DueDate],[ARPayment.DocDate])’

Do I need to add anything so the results of that calculation is expressed in days? Could that be the trigger for the Conversion Error?

 

Userlevel 7
Badge +17

@mpeck12   There is some issue with the above provided XML code and NOT able to import as Generic Inquiry.

However, Formula field is correct, that will calculate the difference between [ARInvoice.DueDate] and [ARPayment.DocDate].

 

I have an example on handy, Please find the screenshot for reference. Hope this helps!!

 

Userlevel 5
Badge +1

Yes, these is some issue with the XML that you have shared. 

I tried a sample and it does not give any issues. Please import the attached  XML and verify. 

 

Happy coding!

Userlevel 2
Badge

I updated the GI to match your suggestions. The good news is I don’t get the error. Bad news is no records were found. I am using the U100 test data, so that might actually be the correct result, but I was expecting some rows to be returned.

Userlevel 7
Badge +17

Hi @mpeck12  Great that you are NOT getting any errors now.

Since GI is NOT returning any results, can you please check below highlighted areas

 

Please check this join conditions

 

 

I know PMT is correct condition, but still just remove and check once 

 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved