Add UD child table (nothing populating)

KirsJone · June 2, 2020, 3:50pm

Hi

I have added a UD Child Table via Wizards on the Part Table.
I have linked by Part.PartNum=UD07.Key3.

I have added a new sheet and added an ultragrid and binded by UD07.

When uploading a part that does exist in UD07 nothing displays. I have a feeling from other posts that something is wrong in the coding but i am quite new to this and not sure what i am doing.

I am able to add a new record to the UD table, but am not able to delete.

In the code set initial UD Key Values it does not appear to be right as shows partnum against Key 2 not Key 3. I have amended this but still does not display.

Any help would be much appreciated.

KirsJone · June 2, 2020, 3:56pm

’ **************************************************
’ Custom code for PartForm
’ Created: 24/08/2017 11:05:45
’ **************************************************

Imports System
Imports System.ComponentModel
Imports System.Data
Imports System.Diagnostics
Imports System.Windows.Forms
Imports Microsoft.VisualBasic
Imports Erp.Adapters
Imports Erp.UI
Imports Ice.Lib
Imports Ice.Adapters
Imports Ice.Lib.Customization
Imports Ice.Lib.ExtendedProps
Imports Ice.Lib.Framework
Imports Ice.Lib.Searches
Imports Ice.UI.FormFunctions

Module Script

	' ** Wizard Insert Location - Do Not Remove 'Begin/End Wizard Added Module Level Variables' Comments! **
	' Begin Wizard Added Module Level Variables **

	Private WithEvents _ud07Adapter As UD07Adapter
	Private _edvPart As EpiDataView
	Private UD07_Column As DataTable
	Private _edvUD07 As EpiDataView
	Private WithEvents _Key1UD07 As String
	Private WithEvents _Key2UD07 As String
	Private WithEvents _Key3UD07 As String
	Private WithEvents _Key4UD07 As String
	Private WithEvents _Key5UD07 As String
	Private WithEvents Part_DataView As DataView
	' End Wizard Added Module Level Variables **

	' Add Custom Module Level Variables Here **

	Public Sub InitializeCustomCode()
		' ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Variable Initialization' lines **
		' Begin Wizard Added Variable Initialization

		InitializeUD07Adapter()
		Script._Key1UD07 = String.Empty
		Script._Key2UD07 = String.Empty
		Script._Key3UD07 = String.Empty
		Script._Key4UD07 = String.Empty
		Script._Key5UD07 = String.Empty
		AddHandler Script.baseToolbarsManager.ToolClick, AddressOf Script.baseToolbarsManager_ToolClickForUD07
		AddHandler Script.PartForm.BeforeToolClick, AddressOf Script.PartForm_BeforeToolClickForUD07
		AddHandler Script.PartForm.AfterToolClick, AddressOf Script.PartForm_AfterToolClickForUD07
		AddHandler Script.Part_Row.EpiRowChanged, AddressOf Script.Part_AfterRowChangeForUD07
		Script.Part_DataView = Script.Part_Row.dataView
		AddHandler Script.Part_DataView.ListChanged, AddressOf Script.Part_DataView_ListChangedForUD07
		' End Wizard Added Variable Initialization

		' Begin Wizard Added Custom Method Calls

		' End Wizard Added Custom Method Calls
	End Sub

	Public Sub DestroyCustomCode()
		' ** Wizard Insert Location - Do not delete 'Begin/End Wizard Added Object Disposal' lines **
		' Begin Wizard Added Object Disposal

		If (Not (Script._ud07Adapter) Is Nothing) Then
			Script._ud07Adapter.Dispose
			Script._ud07Adapter = Nothing
		End If
		Script._edvUD07 = Nothing
		Script._edvPart = Nothing
		Script.UD07_Column = Nothing
		Script._Key1UD07 = Nothing
		Script._Key2UD07 = Nothing
		Script._Key3UD07 = Nothing
		Script._Key4UD07 = Nothing
		Script._Key5UD07 = Nothing
		RemoveHandler Script.baseToolbarsManager.ToolClick, AddressOf Script.baseToolbarsManager_ToolClickForUD07
		RemoveHandler Script.PartForm.BeforeToolClick, AddressOf Script.PartForm_BeforeToolClickForUD07
		RemoveHandler Script.PartForm.AfterToolClick, AddressOf Script.PartForm_AfterToolClickForUD07
		RemoveHandler Script.Part_Row.EpiRowChanged, AddressOf Script.Part_AfterRowChangeForUD07
		RemoveHandler Script.Part_DataView.ListChanged, AddressOf Script.Part_DataView_ListChangedForUD07
		Script.Part_DataView = Nothing
		' End Wizard Added Object Disposal

		' Begin Custom Code Disposal

		' End Custom Code Disposal
	End Sub


	Private Sub InitializeUD07Adapter()
		' Create an instance of the Adapter.
		Script._ud07Adapter = New UD07Adapter(Script.oTrans)
		Script._ud07Adapter.BOConnect

		' Add Adapter Table to List of Views
		' This allows you to bind controls to the custom UD Table
		Script._edvUD07 = New EpiDataView()
		Script._edvUD07.dataView = New DataView(Script._ud07Adapter.UD07Data.UD07)
		Script._edvUD07.AddEnabled = true
		Script._edvUD07.AddText = "New UD07"
		If (Script.oTrans.EpiDataViews.ContainsKey("UD07View") = false) Then
			Script.oTrans.Add("UD07View", Script._edvUD07)
		End If

		' Initialize DataTable variable
		Script.UD07_Column = Script._ud07Adapter.UD07Data.UD07

		' Initialize EpiDataView field.
		Script._edvPart = CType(Script.oTrans.EpiDataViews("Part"),EpiDataView)

		' Set the parent view / keys for UD child view
		Dim parentKeyFields(0) As String
		Dim childKeyFields(0) As String
		parentKeyFields(0) = "PartNum"
		childKeyFields(0) = "Key3"
		Script._edvUD07.SetParentView(Script._edvPart, parentKeyFields, childKeyFields)

		If (Not (Script.oTrans.PrimaryAdapter) Is Nothing) Then
			' Script.oTrans.PrimaryAdapter.GetCurrentDataSet(Ice.Lib.Searches.DataSetMode.RowsDataSet).Tables.Add(Script._edvUD07.dataView.Table.Clone)
		End If

	End Sub

	Private Sub GetUD07Data(ByVal key1 As String, ByVal key2 As String, ByVal key3 As String, ByVal key4 As String, ByVal key5 As String)
		If (Script._Key1UD07 <> key1) Or (Script._Key2UD07 <> key2) Or (Script._Key3UD07 <> key3) Or (Script._Key4UD07 <> key4) Or (Script._Key5UD07 <> key5) Then
			' Build where clause for search.
			Dim whereClause As String = "Key1 = '" & key1 & "' And Key2 = '" & key2 & "' And Key3 = '" & key3 & "' And Key4 = '" & key4 & "'"
			Dim whereClauses As System.Collections.Hashtable = New System.Collections.Hashtable(1)
			whereClauses.Add("UD07", whereClause)

			' Call the adapter search.
			Dim searchOptions As SearchOptions = SearchOptions.CreateRuntimeSearch(whereClauses, DataSetMode.RowsDataSet)
			Script._ud07Adapter.InvokeSearch(searchOptions)

			If (Script._ud07Adapter.UD07Data.UD07.Rows.Count > 0) Then
				Script._edvUD07.Row = 0
			Else
				Script._edvUD07.Row = -1
			End If

			' Notify that data was updated.
			Script._edvUD07.Notify(New EpiNotifyArgs(Script.oTrans, Script._edvUD07.Row, Script._edvUD07.Column))

			' Set key fields to their new values.
			Script._Key1UD07 = key1
			Script._Key2UD07 = key2
			Script._Key3UD07 = key3
			Script._Key4UD07 = key4
			Script._Key5UD07 = key5
		End If
	End Sub

	Private Sub GetNewUD07Record()
		Dim parentViewRow As DataRow = Script._edvPart.CurrentDataRow
		'Check for existence of Parent Row.
		If (parentViewRow Is Nothing) Then
			Exit Sub
		End If
		If Script._ud07Adapter.GetaNewUD07 Then
			Dim partnum As String = parentViewRow("PartNum").ToString

			' Get unique row count id for Key5
			Dim rowCount As Integer = Script._ud07Adapter.UD07Data.UD07.Rows.Count
			Dim lineNum As Integer = rowCount
			Dim goodIndex As Boolean = false
			While (goodIndex = false)
				' Check to see if index exists
				Dim matchingRows() As DataRow = Script._ud07Adapter.UD07Data.UD07.Select("Key5 = '" & lineNum.ToString & "'")
				If (matchingRows.Length > 0) Then
					lineNum = (lineNum + 1)
				Else
					goodIndex = true
				End If
			End While

			' Set initial UD Key values
			Dim editRow As DataRow = Script._ud07Adapter.UD07Data.UD07.Rows((rowCount - 1))
			editRow.BeginEdit
			editRow("Key1") = String.Empty
			editRow("Key2") = String.Empty
			editRow("Key3") = partnum
			editRow("Key4") = String.Empty
			editRow("Key5") = String.Empty
			editRow.EndEdit

			' Notify that data was updated.
			Script._edvUD07.Notify(New EpiNotifyArgs(Script.oTrans, (rowCount - 1), Script._edvUD07.Column))
		End If
	End Sub

	Private Sub SaveUD07Record()
		' Save adapter data
		Script._ud07Adapter.Update
	End Sub

	Private Sub DeleteUD07Record()
		' Check to see if deleted view is ancestor view
		Dim isAncestorView As Boolean = false
		Dim parView As Ice.Lib.Framework.EpiDataView = Script._edvUD07.ParentView
		While (Not (parView) Is Nothing)
			If (Script.oTrans.LastView Is parView) Then
				isAncestorView = true
				Exit While
			Else
				parView = parView.ParentView
			End If
		End While

		' If Ancestor View then delete all child rows
		If isAncestorView Then
			Dim drsDeleted() As DataRow = Script._ud07Adapter.UD07Data.UD07.Select("Key1 = '" & Script._Key1UD07 & "' AND Key2 = '" & Script._Key2UD07 & "' AND Key3 = '" & Script._Key3UD07 & "' AND Key4 = '" & Script._Key4UD07 & "'")
			Dim i As Integer = 0
			Do While (i < drsDeleted.Length)
				Script._ud07Adapter.Delete(drsDeleted(i))
				i = (i + 1)
			Loop
		Else
			If (Script.oTrans.LastView Is Script._edvUD07) Then
				If (Script._edvUD07.Row >= 0) Then
					Dim drDeleted As DataRow = CType(Script._ud07Adapter.UD07Data.UD07.Rows(Script._edvUD07.Row),DataRow)
					If (Not (drDeleted) Is Nothing) Then
						If Script._ud07Adapter.Delete(drDeleted) Then
							If (_edvUD07.Row > 0) Then
								_edvUD07.Row = (_edvUD07.Row - 1)
							End If

							' Notify that data was updated.
							Script._edvUD07.Notify(New EpiNotifyArgs(Script.oTrans, Script._edvUD07.Row, Script._edvUD07.Column))
						End If
					End If
				End If
			End If
		End If
	End Sub

	Private Sub UndoUD07Changes()
		Script._ud07Adapter.UD07Data.RejectChanges

		' Notify that data was updated.
		Script._edvUD07.Notify(New EpiNotifyArgs(Script.oTrans, Script._edvUD07.Row, Script._edvUD07.Column))
	End Sub

	Private Sub ClearUD07Data()
		Script._Key1UD07 = String.Empty
		Script._Key2UD07 = String.Empty
		Script._Key3UD07 = String.Empty
		Script._Key4UD07 = String.Empty
		Script._Key5UD07 = String.Empty

		Script._ud07Adapter.UD07Data.Clear

		' Notify that data was updated.
		Script._edvUD07.Notify(New EpiNotifyArgs(Script.oTrans, Script._edvUD07.Row, Script._edvUD07.Column))
	End Sub

	Private Sub baseToolbarsManager_ToolClickForUD07(ByVal sender As Object, ByVal args As Infragistics.Win.UltraWinToolbars.ToolClickEventArgs)
		' EpiMessageBox.Show(args.Tool.Key)
		Select Case args.Tool.Key

			Case "EpiAddNewNew UD07"
				GetNewUD07Record

			Case "ClearTool"
				ClearUD07Data

			Case "UndoTool"
				UndoUD07Changes

		End Select
	End Sub

	Private Sub PartForm_BeforeToolClickForUD07(ByVal sender As Object, ByVal args As Ice.Lib.Framework.BeforeToolClickEventArgs)
		' EpiMessageBox.Show(args.Tool.Key)
		Select Case args.Tool.Key

			Case "SaveTool"
				SaveUD07Record

		End Select
	End Sub

	Private Sub PartForm_AfterToolClickForUD07(ByVal sender As Object, ByVal args As Ice.Lib.Framework.AfterToolClickEventArgs)
		' EpiMessageBox.Show(args.Tool.Key)
		Select Case args.Tool.Key

			Case "DeleteTool"
				If (args.Cancelled = false) Then
					DeleteUD07Record
				End If

		End Select
	End Sub

	Private Sub Part_AfterRowChangeForUD07(ByVal args As EpiRowChangedArgs)
		' ** add AfterRowChange event handler
		Dim partnum As String = args.CurrentView.dataView(args.CurrentRow)("PartNum").ToString
		GetUD07Data(String.Empty, String.Empty,partnum, String.Empty, String.Empty)
	End Sub

	Private Sub Part_DataView_ListChangedForUD07(ByVal sender As Object, ByVal args As ListChangedEventArgs)
		' ** add ListChanged event handler
		Dim partnum As String = Part_DataView(0)("PartNum").ToString
		GetUD07Data(String.Empty, String.Empty,partnum, String.Empty, String.Empty)
	End Sub
End Module

ckrusen · June 3, 2020, 12:47pm

You mean when a new Part table entry is created?

Is this a one-to-one (exactly one UD07 record for each part record) or one-to-many(one or more UD07 records for each Part)?

Does there always have to be a UD07 record, or can there be a part without related UD07 records?

edit

Also, please use the formatting codes to format you post with the code.
Place three graves (“tick”, or “backward apostrophe”) on a line by themselves, before your first line of code, and three more after your last line.

KirsJone · June 3, 2020, 1:06pm

An existing part. This is one-to-many and there can be a part without related UD07 records.

Apologies first time posting so will get this corrected.

ckrusen · June 3, 2020, 1:36pm

I’ve not worked with UD Child tables much, so I just tried making one. And I get an odd behavior.

When I click New -> New UD07, nothing appears to happen. I click it a couple more times, and still nothing. Then I make a new record of the main table (I’m testing with ABCCodes), and the form shows the new blank record, but the ultragrid shows all the UD07 records for the yet to be named ABC code.

I think this is because the child table is using Key3 to relate UD07 to the main table (Part for you ABCCode for me). And the new record - whose ABC code is currently blank, matches all those UD fields where Key3 is blank.

So I think your Child table needs to use Key1 = PartNum.

KirsJone · June 3, 2020, 1:47pm

In our UD07 table we populate key 3 with the part number. When I click new in the Part table, key 3 is populated with the part number, I then enter information in key 1 and key 2, this saves and creates a new record in the UD07 table.

However when I clear the part table and re-enter either this part number or a different one that I know exists in UD07 this does not display anything.

I can add a new record to the uD07 table but cannot seem to edit existing entries.

ckrusen · June 3, 2020, 2:13pm

Did you edit this section of code?

' Set initial UD Key values 
Dim editRow As DataRow = Script._ud07Adapter.UD07Data.UD07.Rows((rowCount - 1)) 
editRow.BeginEdit editRow("Key1") = String.Empty 
editRow("Key2") = String.Empty 
editRow("Key3") = partnum 
editRow("Key4") = String.Empty 
editRow("Key5") = String.Empty 
editRow.EndEdit

Because when you use the wizard, and even selecting Key3 as the filed to relate to the parent table, the code generated looks like:

	// Set initial UD Key values
	DataRow editRow = this._ud08Adapter.UD08Data.UD08.Rows[(rowCount - 1)];
	editRow.BeginEdit();
	editRow["Key1"] = abccode;
	editRow["Key2"] = string.Empty;
	editRow["Key3"] = string.Empty;
	editRow["Key4"] = string.Empty;
	editRow["Key5"] = lineNum.ToString();
	editRow.EndEdit();

Granted mine is C# and yours is VB, but the fact that the code always sets Key1 to the primary firled of the parent, remains the same.

KirsJone · June 3, 2020, 2:35pm

I did yeah . I was unable to add new (didnt display a line or anything) so changed key1 to String.Empty and Key3 to partnum. This allowed me to add a new record to that table, populating key3 with the part number.

I then changed key5 to string.Empty as I want this field to be blank and to not display a number.

I changed the last bit of code from “GetUD07Data(partnum, String.Empty, String.Empty, String.Empty)” to what it now shows as thought this might be the problem but it is not.

Private Sub Part_AfterRowChangeForUD07(ByVal args As EpiRowChangedArgs)
		' ** add AfterRowChange event handler
		Dim partnum As String = args.CurrentView.dataView(args.CurrentRow)("PartNum").ToString
		GetUD07Data(String.Empty, String.Empty,partnum, String.Empty, String.Empty)
	End Sub

	Private Sub Part_DataView_ListChangedForUD07(ByVal sender As Object, ByVal args As ListChangedEventArgs)
		' ** add ListChanged event handler
		Dim partnum As String = Part_DataView(0)("PartNum").ToString
		GetUD07Data(String.Empty, String.Empty,partnum, String.Empty, String.Empty)

ckrusen · June 3, 2020, 2:40pm

You need key 5 as it is the index of which of the many child records related to the parent.

Is there a reason why you can’t relate Key1 to PartNum?

KirsJone · June 3, 2020, 2:49pm

Ahh right okay.

We have a UDtable UD06 where key 1 and key 2 are the same as Key1 and Key2 in UD07. So we have UD07.Key3 for partnum. Hope this makes sense.

The tables were being updated via DMT but now they want this information displaying and editing on the Part table. I did think of an embedded dashboard but have read this cannot be updatable.

BKen · June 3, 2020, 2:52pm

I remember reading the the First Key field is the ID of the Parent Record. I am trying to locate where I read that. Key 5 is a sequencer, so that each record is unique. Always keep Key 5.

For Key 1 I use the most general (if you are multi-company, I would use Company), then get more specific for each needed key.

For any UD child table, you can hide any column. In many cases I hide all the key fields (because users populate the fields from the form).

I agree with Calvin (if you are single company) then use partnum for Key 1 and keep Key 5 as the sequencer (but hide it on your form if you don’t want users to see it). You hide columns in the Customization. Properties, Layout, Columns.

KirsJone · June 3, 2020, 3:10pm

I have created a new customization following this rule with using key1 even though I can add a new record I still cannot display existing ones. So once I saved the new record I cleared the part table and reloaded the part num but still nothing displays.

ckrusen · June 3, 2020, 3:39pm

The new customization references the same child table UD07?

With no changes to the auto generated code?

KirsJone · June 3, 2020, 3:43pm

Yeah references same child table UD07 and have made no changes to the auto generated code. I can add new but once this saves I cannot display the added row if I exit out and back in.

BKen · June 3, 2020, 3:48pm

Kirsty,
Can you provide a snip of what some of the data lines look like in your UD07 table? Please show Keys 1-5 (at a minimum). This may help us diagnose the issue.

KirsJone · June 3, 2020, 4:03pm

We populate UD07.key1 and UD07.key2 with either of the following entries from ud06 (This is not hard coded or anything just uploaded via DMT). UD07.Key3 we enter the partnumber, then put information into the number fields.

Below is the new one I have added when using key1 as suggested.

Hope all this makes sense

ckrusen · June 3, 2020, 4:28pm

The UD07 record with the PN in Key1 and Keys3 won’t show, because your new customization is ~~expecting Key3 to be blank.~~ is only going to show UD07 records that match the settings in the wizard.

Or did you relate Part.PartNum-> Key1 and Part.PartNum->Key3 in the wizard?

Now that I think about it. I wonder if the values in Key2 (for the relationship to UD06) are preventing any UD07 records from being retrieved. You might need to tweak code to remove the constraints for the other keys.

If this is so, then maybe you original code is super close to working, and just need to remove the key 1 and key2 constaraints.

KirsJone · June 3, 2020, 4:44pm

I related Part.PartNum>Key1. I can seem to add a new record when I have something in Key3 in this recent example the partnum. I have tried adding a new record with nothing in Key3 and an entry in number02 but says duplicate entry.

In order to save using key1 I did have to disable a BPM. The UD tables/BPMs were not put in by myself so im not sure if there is something I am missing. I think you are right and wonder the same thing if UD06 and key1 and key2 is playing a part in retrieving records.

I shall give that a go tomorrow and have another play around with the code.

Frustrating as i feel i am so close, I can add a new record, I cannot delete but not concerned on that as reckon i can solve that issue when I can display the existing data.

ckrusen · June 3, 2020, 4:52pm

As a helper tool, add a menu item for Ice.UI.UD07Entry.dll. Then you’ll have a form that should let you view and edit the UD07 records without having to use the SQL tools.

ckrusen · June 3, 2020, 5:40pm

And some more hints…

A trace show the following:
(focus on the whereClauseUD07)

<tracePacket>
  <businessObject>Ice.Proxy.BO.UD07Impl</businessObject>
  <methodName>GetRows</methodName>
  <appServerUri>net.tcp://usdcaaps00371/UAT_102300/</appServerUri>
  <returnType>Ice.Tablesets.UD07Tableset</returnType>
  <localTime>6/3/2020 13:35:21:9839365 PM</localTime>
  <threadID>1</threadID>
  <executionTime total="43" roundTrip="42" channel="0" bpm="0" other="1" />
  <retries>0</retries>
  <parameters>
    <parameter name="whereClauseUD07" type="System.String"><![CDATA[Key1 = 'T' And Key2 = '' And Key3 = '' And Key4 = '' BY Key1]]></parameter>
    <parameter name="whereClauseUD07Attch" type="System.String"><![CDATA[]]></parameter>
    <parameter name="pageSize" type="System.Int32"><![CDATA[0]]></parameter>
    <parameter name="absolutePage" type="System.Int32"><![CDATA[0]]></parameter>
    <parameter name="morePages" type="System.Boolean"><![CDATA[False]]></parameter>
  </parameters>
</tracePacket>

Even though the Wizard was created with ABCCode = Key3, the whereClause is still using Key1. Also, the whereClause is looking for UD07 records with Key2 thru Key4 being null. That’s why even records with Key1 = primary_field, or key3 = the primary_field, aren’t returned.

Edit

I got it working like you originally had it (PartNum = Key 3), by tweaking the code in GetUD07Data()

I changed mine from

string whereClause = "Key1 = \'" + key1 + "\' And Key2 = \'" + key2 + "\' And Key3 = \'" + key3 + "\' And Key4 = \'" + key4 + "\'";

to

string whereClause = "Key3 = \'" + key1 + "\'";

Your line would go from

Dim whereClause As String = "Key1 = '" & key1 & "' And Key2 = '" & key2 & "' And Key3 = '" & key3 & "' And Key4 = '" & key4 & "'"

to

Dim whereClause As String = "Key3 = '" & key1 & "'"

No guarantees you won’t have to tweak other code.